MySQL多层查询

最新推荐文章于 2025-01-05 17:07:07 发布

原创最新推荐文章于 2025-01-05 17:07:07 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

本文通过一个具体的SQL查询案例，展示了如何从订单数据中筛选并汇总特定用户的订单状态信息。该查询涉及复杂的子查询和多表关联，对于理解SQL聚合函数、条件筛选及结果排序具有很好的参考价值。

select user_id,status from mmall_order;
+--------+--------+
| user_id | status |
+--------+--------+
| 1 | 10 |
| 1 | 10 |
| 1 | 40 |
| 1 | 20 |
| 1 | 20 |
| 1 | 10 |
| 1 | 10 |
| 1 | 10 |
| 1 | 10 |
| 1 | 10 |
| 1 | 10 |
| 1 | 10 |
| 1 | 10 |
| 1 | 40 |
| 1 | 40 |
| 29 | 40 |
| 29 | 40 |
| 30 | 10 |
| 30 | 10 |
| 29 | 10 |
| 29 | 10 |
| 29 | 10 |
| 30 | 10 |
| 29 | 10 |
| 29 | 10 |
| 29 | 10 |
| 31 | 10 |
| 31 | 10 |
| 31 | 10 |
| 31 | 20 |
| 35 | 10 |
| 35 | 10 |
| 35 | 20 |
| 31 | 40 |
| 37 | 20 |
| 31 | 10 |
| 31 | 10 |
| 31 | 20 |
| 39 | 10 |
| 40 | 10 |
| 40 | 10 |
| 40 | 10 |
| 31 | 20 |
| 29 | 10 |
| 26 | 10 |
| 41 | 10 |
| 29 | 10 |
| 42 | 10 |
| 42 | 10 |
| 42 | 10 |
| 29 | 10 |
| 44 | 10 |
| 45 | 20 |
| 44 | 20 |
| 44 | 20 |
| 31 | 20 |
+--------+--------+
56 rows in set

select * from

(select user_id,sum(status) as sus,count(user_id) as cons2 from mmall_order group by user_id)temps where temps.sus>0 and user_id in
(select user_id from
(select user_id,count(user_id) as sus2 from mmall_order group by user_id) temps2

where temps2.sus2>0) order by sus desc,cons2 desc limit 2,3;

+--------+-----+-------+
| user_id | sus | cons2 |
+--------+-----+-------+
| 31 | 170 | 10 |
| 44 | 50 | 3 |
| 35 | 40 | 3 |
+--------+-----+-------+