admin 普通会员 时间：2025-10-10 16:41:39 浏览：18

多表连接

连接三个或更多表

StartMVC支持链式调用多个join()方法来连接多个表。

// 查询订单详细信息（用户-订单-订单项-商品）
$result = Db::table('orders')
 ->select('
 sm_orders.order_no,
 sm_users.username,
 sm_products.name as product_name,
 sm_order_items.quantity,
 sm_order_items.price,
 (sm_order_items.quantity * sm_order_items.price) as subtotal
 ')
 ->join('users', 'sm_orders.user_id', '=', 'sm_users.id')
 ->join('order_items', 'sm_orders.id', '=', 'sm_order_items.order_id')
 ->join('products', 'sm_order_items.product_id', '=', 'sm_products.id')
 ->where('sm_orders.status', 1)
 ->order('subtotal', 'DESC')
 ->limit(5)
 ->get();

生成的SQL：

SELECT 
 sm_orders.order_no,
 sm_users.username,
 sm_products.name as product_name,
 sm_order_items.quantity,
 sm_order_items.price,
 (sm_order_items.quantity * sm_order_items.price) as subtotal
FROM sm_orders 
JOIN sm_users ON sm_orders.user_id = sm_users.id 
JOIN sm_order_items ON sm_orders.id = sm_order_items.order_id 
JOIN sm_products ON sm_order_items.product_id = sm_products.id 
WHERE sm_orders.status = 1 
ORDER BY subtotal DESC 
LIMIT 5

返回结果：

Array(
 [0] => Array(
 [order_no] => ORDER20231102
 [username] => 张三
 [product_name] => MacBook Pro
 [quantity] => 1
 [price] => 12999.00
 [subtotal] => 12999.00
 ),
 [1] => Array(
 [order_no] => ORDER20231101
 [username] => 张三
 [product_name] => iPhone 13
 [quantity] => 1
 [price] => 5999.00
 [subtotal] => 5999.00
 ),
 [2] => Array(
 [order_no] => ORDER20231104
 [username] => 王五
 [product_name] => iPad Air
 [quantity] => 1
 [price] => 4799.00
 [subtotal] => 4799.00
 )
)

JOIN与聚合函数

1. 统计分析示例

// 用户订单统计（使用多个聚合函数）
$result = Db::table('users')
 ->select('
 sm_users.username,
 COUNT(sm_orders.id) as order_count,
 SUM(sm_orders.amount) as total_amount,
 AVG(sm_orders.amount) as avg_amount,
 MAX(sm_orders.amount) as max_amount,
 MIN(sm_orders.amount) as min_amount
 ')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id', 'LEFT')
 ->group('sm_users.id')
 ->order('total_amount', 'DESC')
 ->get();

生成的SQL：

SELECT 
 sm_users.username,
 COUNT(sm_orders.id) as order_count,
 SUM(sm_orders.amount) as total_amount,
 AVG(sm_orders.amount) as avg_amount,
 MAX(sm_orders.amount) as max_amount,
 MIN(sm_orders.amount) as min_amount
FROM sm_users 
LEFT JOIN sm_orders ON sm_users.id = sm_orders.user_id 
GROUP BY sm_users.id 
ORDER BY total_amount DESC

返回结果：

Array(
 [0] => Array(
 [username] => 张三
 [order_count] => 2
 [total_amount] => 20997.00
 [avg_amount] => 10498.50
 [max_amount] => 12999.00
 [min_amount] => 7998.00
 ),
 [1] => Array(
 [username] => 李四
 [order_count] => 2
 [total_amount] => 7998.00
 [avg_amount] => 3999.00
 [max_amount] => 5999.00
 [min_amount] => 1999.00
 ),
 [2] => Array(
 [username] => 王五
 [order_count] => 1
 [total_amount] => 4799.00
 [avg_amount] => 4799.00
 [max_amount] => 4799.00
 [min_amount] => 4799.00
 ),
 [3] => Array(
 [username] => 赵六
 [order_count] => 0
 [total_amount] => NULL
 [avg_amount] => NULL
 [max_amount] => NULL
 [min_amount] => NULL
 )
)

2. 直接获取聚合值

// 获取用户的订单总数
$count = Db::table('users')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id', 'LEFT')
 ->where('sm_users.id', 1)
 ->count('sm_orders.id');
// 返回: 2

// 获取用户的消费总额
$total = Db::table('users')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->where('sm_users.id', 1)
 ->where('sm_orders.status', 1)
 ->sum('sm_orders.amount');
// 返回: 20997.00

// 获取用户的最大订单金额
$max = Db::table('users')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->where('sm_users.id', 1)
 ->max('sm_orders.amount');
// 返回: 12999.00

// 获取用户的平均订单金额
$avg = Db::table('users')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->where('sm_users.id', 1)
 ->avg('sm_orders.amount');
// 返回: 10498.50

高级应用场景

1. 分页查询

// 带分页的JOIN查询
$page = 1;
$perPage = 3;

// 先获取总数
$total = Db::table('users')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->where('sm_orders.status', '!=', 2)
 ->count();

// 执行分页查询
$result = Db::table('users')
 ->select('sm_users.username, sm_users.email, sm_orders.order_no, sm_orders.amount')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->where('sm_orders.status', '!=', 2)
 ->order('sm_orders.created_at', 'DESC')
 ->page($perPage, $page) // 每页3条，第1页
 ->get();

2. 复杂条件查询

// 多条件组合查询
$result = Db::table('users')
 ->select('sm_users.username, sm_orders.order_no, sm_orders.amount')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->where('sm_orders.status', 'in', [0, 1])
 ->where('sm_orders.amount', 'between', [1000, 10000])
 ->where('sm_users.username', 'like', '%三%')
 ->order('sm_orders.created_at', 'DESC')
 ->limit(5)
 ->get();

3. HAVING子句

// 使用HAVING过滤分组后的结果
$result = Db::table('users')
 ->select('
 sm_users.username,
 sm_products.name as product_name,
 COUNT(sm_order_items.id) as buy_times,
 SUM(sm_order_items.quantity) as total_quantity
 ')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->join('order_items', 'sm_orders.id', '=', 'sm_order_items.order_id')
 ->join('products', 'sm_order_items.product_id', '=', 'sm_products.id')
 ->where('sm_orders.status', 1)
 ->group('sm_users.id, sm_products.id')
 ->having('buy_times', '>', 0)
 ->having('total_quantity', '>=', 1)
 ->order('total_quantity', 'DESC')
 ->get();

4. 热销商品排行

// 查询热销商品TOP3
$result = Db::table('products')
 ->select('
 sm_products.name,
 COUNT(DISTINCT sm_orders.user_id) as buyer_count,
 SUM(sm_order_items.quantity) as total_sold,
 SUM(sm_order_items.quantity * sm_order_items.price) as total_revenue
 ')
 ->join('order_items', 'sm_products.id', '=', 'sm_order_items.product_id')
 ->join('orders', 'sm_order_items.order_id', '=', 'sm_orders.id')
 ->where('sm_orders.status', 1)
 ->group('sm_products.id')
 ->order('total_revenue', 'DESC')
 ->limit(3)
 ->get();

返回结果：

Array(
 [0] => Array(
 [name] => MacBook Pro
 [buyer_count] => 1
 [total_sold] => 1
 [total_revenue] => 12999.00
 ),
 [1] => Array(
 [name] => iPhone 13
 [buyer_count] => 1
 [total_sold] => 1
 [total_revenue] => 5999.00
 ),
 [2] => Array(
 [name] => iPad Air
 [buyer_count] => 1
 [total_sold] => 1
 [total_revenue] => 4799.00
 )
)

5. 用户价值分析（RFM模型）

// 使用原生SQL进行复杂的RFM分析
$sql = "SELECT 
 u.username,
 COUNT(DISTINCT o.id) as frequency,
 SUM(o.amount) as monetary,
 DATEDIFF(NOW(), MAX(o.created_at)) as recency_days,
 CASE 
 WHEN SUM(o.amount) > 10000 THEN 'VIP'
 WHEN SUM(o.amount) > 5000 THEN '高价值'
 WHEN SUM(o.amount) > 1000 THEN '普通'
 ELSE '低价值'
 END as customer_level
FROM sm_users u
LEFT JOIN sm_orders o ON u.id = o.user_id AND o.status = 1
GROUP BY u.id
HAVING frequency > 0
ORDER BY monetary DESC";

$db = Db::connect();
$stmt = $db->getPdo()->query($sql);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

返回结果：

Array(
 [0] => Array(
 [username] => 张三
 [frequency] => 2
 [monetary] => 20997.00
 [recency_days] => 0
 [customer_level] => VIP
 ),
 [1] => Array(
 [username] => 王五
 [frequency] => 1
 [monetary] => 4799.00
 [recency_days] => 0
 [customer_level] => 普通
 )
)

性能优化建议

1. 索引优化

确保JOIN字段都有索引：

-- 为外键字段添加索引
ALTER TABLE sm_orders ADD INDEX idx_user_id (user_id);
ALTER TABLE sm_order_items ADD INDEX idx_order_id (order_id);
ALTER TABLE sm_order_items ADD INDEX idx_product_id (product_id);

2. 查询优化

// ❌ 不推荐：SELECT * 会查询所有字段
$result = Db::table('users')
 ->select('*')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->get();

// ✅ 推荐：只查询需要的字段
$result = Db::table('users')
 ->select('sm_users.username, sm_orders.order_no, sm_orders.amount')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->get();

3. 使用查询缓存

// 对频繁查询的数据使用缓存
$result = Db::table('products')
 ->select('sm_products.*, COUNT(sm_order_items.id) as sold_count')
 ->join('order_items', 'sm_products.id', '=', 'sm_order_items.product_id', 'LEFT')
 ->group('sm_products.id')
 ->cache(3600) // 缓存1小时
 ->get();

4. 分析查询性能

// 获取查询SQL并使用EXPLAIN分析
$sql = Db::table('users')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id')
 ->getSql()
 ->get();

// 在数据库中执行EXPLAIN
// EXPLAIN SELECT ... FROM sm_users JOIN sm_orders ...

常见问题

Q1: 为什么字段名要使用完整表名？

答： 为了避免字段名冲突。当多个表有相同字段名时（如id、created_at），使用完整表名可以明确指定是哪个表的字段。

Q2: join()和innerJoin()有什么区别？

答： 功能上没有区别。join()是通用方法，通过第5个参数指定JOIN类型；innerJoin()、leftJoin()等是语义化的便捷方法。

// 以下两种写法效果相同
Db::table('users')->join('orders', 'sm_users.id', '=', 'sm_orders.user_id');
Db::table('users')->innerJoin('orders', 'sm_users.id', '=', 'sm_orders.user_id');

Q3: 如何处理NULL值？

答： LEFT JOIN可能产生NULL值，使用聚合函数时要注意：

// 使用COALESCE或IFNULL处理NULL值
$result = Db::table('users')
 ->select('sm_users.username, COALESCE(COUNT(sm_orders.id), 0) as order_count')
 ->join('orders', 'sm_users.id', '=', 'sm_orders.user_id', 'LEFT')
 ->group('sm_users.id')
 ->get();

总结

StartMVC框架的join()方法提供了强大而灵活的多表查询功能：

1. 简单易用：链式调用，语法清晰
2. 功能完整：支持所有JOIN类型
3. 性能优秀：生成优化的SQL语句
4. 扩展性强：可与其他查询方法组合使用

通过本教程的学习，你应该能够熟练使用StartMVC框架进行各种复杂的数据库关联查询。记住以下要点：

• 使用table()和join()时只需提供表名（不含前缀）
• 在字段引用时使用完整表名（包含前缀）
• 合理使用索引和缓存优化查询性能
• 复杂查询可以考虑使用原生SQL

希望这个教程对你有所帮助！如有问题，欢迎在StartMVC社区交流讨论。