mysql 复杂sql子查询的实例

在我们的实际项目中，有时候需要写一下数据汇总和查询，哪么子查询就必不可少。

比如我现在有三张表：

lch_resource_attribute，idx_lch_resource_category，lch_resource_games

那么我现在要查询某个应用的子分类的话，用 left join on左连接

select l.game_id as 应用id,l.title as 主分类,m.title as 次分类 from (select * from (SELECT a.game_id, GROUP_CONCAT(b.title) as title FROM `lch_resource_attribute` AS b 
,`idx_lch_resource_category` AS a WHERE ((b.id = a.category_id or b.id =a.parent_id) and level = 1) AND b.status =1 and a.game_status = 1 group by a.game_id order by 
a.game_id asc) as g) as l left join (select * from (SELECT e.game_id, GROUP_CONCAT(d.title) as title FROM `lch_resource_attribute` AS d ,`idx_lch_resource_category` AS 
e WHERE ((d.id = e.category_id or d.id =e.parent_id) and level = 2) AND d.status =1 and e.game_status = 1 group by e.game_id order by e.game_id asc) as k) as m on 
l.game_id = m.game_id order by l.game_id;

结果如下:
+----------+--------------------------------------------------------+------------------------------+
|应用id   | 主分类                                                 | 次分类                       |
+----------+--------------------------------------------------------+------------------------------+
|        1 | 消消乐                                        | NULL                         |
|        3 | 卡牌,扑克也,智,休                      | 休智,闲                |
|        4 | 跑酷                                                   | NULL                         |
|        5 | 卡牌,消息好                                    | NULL                         |
|        8 | 2.1,仙剑                                           | NULL                         |
|        9 | 2.1,公共                                           | NULL                         |
|       10 | 冒险,篮球                                              | NULL                         |
|       12 | 网游卡牌,也不错                  | 卡戏,扑错          |
|       14 | 冒幻                                          | NULL                         |
+----------+--------------------------------------------------------+------------------------------+

查询应用基本信息(包含大小和上线时间),主分类和次分类,这个时候就要先查询主分类和次分类，然后在查应用属性。
select k.id, k.name, k.package, k.company, k.developer,k.link,k.size,k.online_time, g.c_title as 主分类,g.s_title as 次分类 from (select * from (SELECT c.id, c.name, 
c.package, c.company, 
c.developer, p.link,p.size, FROM_UNIXTIME(p.update_time) as online_time FROM 
`lch_resource_games` AS c, `idx_lch_resource_version` AS p WHERE c.id = p.game_id AND p.status =1 order by c.id asc) as f) as k, (select * from (select 
l.game_id,l.title as c_title,m.title as s_title from (select * from (SELECT a.game_id, GROUP_CONCAT(b.title) as title FROM `lch_resource_attribute` AS b 
,`idx_lch_resource_category` AS a WHERE (b.id = a.category_id or b.id =a.parent_id) AND b.status =1 and a.game_status = 1 group by a.game_id order by a.game_id asc) as 
g) as l left join (select * from (SELECT e.game_id, GROUP_CONCAT(d.title) as title FROM `lch_resource_attribute` AS d ,`idx_lch_resource_category` AS e WHERE ((d.id = 
e.category_id or d.id =e.parent_id) and level = 2) AND d.status =1 and e.game_status = 1 group by e.game_id order by e.game_id asc) as k) as m on l.game_id = m.game_id 
order by l.game_id) as h) as g where k.id= g.game_id order by k.id desc;
执行的结果如下:
+-----+-----------------+----------------------+------------------+-----------+--------+---------------------+------------------------------+---------------------+
| id  | name            | package              | company          | developer | size   | online_time         | 主分类                       | 次分类              |
+-----+-----------------+----------------------+------------------+-----------+--------+---------------------+------------------------------+---------------------+
| 374 | doufuyy3        | com.doufuyuyue5.am   | 1111@qq.com | aaaaa      |   0.30 | 2016-03-10 15:20:37 |策,坦克   | 击,坦克       |
| 373 | 约3       | com.doufuyuyue3.am   | 22222@qq.com | bbbb     |   3.67 | 2016-03-09 18:18:07 |动作,RPG                  | RPG             |
| 372 | 影之刃          | com.yzr.am           | 33333@gionee.com | cccc    | 157.88 | 2016-01-19 10:25:37 |略                      | NULL                |
| 371 | 2       | com.doufu.yuyuecc.am | 4444444@qq.com | ddddd      |   0.30 | 2016-03-09 10:23:26 |略,RPG            | RPG             |
| 370 | 带付      | com.doufusdk.am      | 55555@qq.com | eeee      |   4.59 | 2016-03-02 16:16:32 |动,游戏                  | RPG            |
+-----+-----------------+----------------------+------------------+-----------+--------+---------------------+------------------------------+---------------------+

这样就查出结果了，整个思路也就出来了。