当前位置：首页 > news >正文

MySql数据库left join中添加子查询

news 2024/10/21 15:39:41

user表查询出数据列表（多条，如id）左连接到order表中的order_agent_id字段，并通过 order_agent_id分组，求和user_order_partner，使用COALESCE()聚合函数对未获取到和值的进行默认赋值，防止查询不出数据不显示问题

SELECT COUNT(*)
FROM (SELECT t.id,t1.create_time AS createTime,t.user_nickname,t.user_image AS userImg,t.user_tel,COALESCE(t1.total_price, '0.00') AS priceFROM db_user tLEFT JOIN (SELECT order_agent_id, create_time, COALESCE(SUM(user_order_partner), '0.00') AS total_priceFROM db_user_orderWHERE order_status = 1 AND deleted = 0GROUP BY order_agent_id, create_time) AS t1 ON t.id = t1.order_agent_idWHERE t.deleted = 0 AND (t.user_partner_id = ? AND t.user_level <> ?)ORDER BY t1.total_price DESC
) AS TOTAL;

‌最外层查询‌：
```
SELECT COUNT(*) FROM (...) AS TOTAL;
```
这部分查询计算内部查询（即括号内的查询）返回的行数。TOTAL是一个别名，用于表示内部查询的结果集。
‌内部查询‌：
```
    SELECT t.id,t1.create_time AS createTime,t.user_nickname,t.user_image AS userImg,t.user_tel,COALESCE(t1.total_price, '0.00') AS priceFROM db_user tLEFT JOIN (SELECT order_agent_id, create_time, COALESCE(SUM(user_order_partner), '0.00') AS total_priceFROM db_user_orderWHERE order_status = 1 AND deleted = 0GROUP BY order_agent_id, create_time) AS t1 ON t.id = t1.order_agent_idWHERE t.deleted = 0 AND (t.user_partner_id = ? AND t.user_level <> ?)ORDER BY t1.total_price DESC
```
这部分查询执行以下操作：
- 从db_user表中选择用户信息。
- 通过左连接（LEFT JOIN）将db_user表与一个子查询结果连接。子查询从db_user_order表中计算每个order_agent_id和create_time组合的订单总额（user_order_partner的和）。
- 使用COALESCE函数确保如果total_price为NULL，则将其替换为'0.00'。上面的COALESCE(t1.total_price, '0.00') AS price必须保留，因为在下面子查询中的COALESCE(SUM(user_order_partner), '0.00') AS total_price仅作为提示使用，可以直接写成SUM(user_order_partner) AS total_price，子查询中的值不会因为有COALESCE函数而传递默认值出去，外面查询语句中获取的t1.total_price依然会是NULL。此处需注意。
- 应用WHERE子句来过滤出未删除的用户，并且其user_partner_id等于某个值且user_level不等于另一个值。
- 使用ORDER BY子句按total_price降序排序结果。
‌参数占位符‌：
查询中的?是参数占位符，用于在执行查询时提供具体的值。例如，t.user_partner_id = ?中的?应该被替换为一个具体的用户伙伴ID值。
‌结果‌：
最终，这个查询返回一个数字，表示满足条件的用户订单记录的数量。注意，尽管内部查询包含ORDER BY子句，但外部查询的COUNT(*)并不关心行的顺序，它只计算行数。