mysql子查询加优化

第一次学习到子查询原来可以这么使用：

SELECT field1,field2,(SUB_QUERY LIMIT 1) AS Alise_Name FROM Table_Name;

于是乎在表结构只有简单的5列的情况下，组长不给新建查询数据的二维表的时候，参考之前的代码，下列的SQL语句诞生了:

SELECT t_date,source,game_id,platform,(SELECT dev_num FROM res_md_device_report AS tt1 WHERE bt.t_date=tt1.t_date AND bt.source=tt1.source AND bt.game_id=tt1.game_id AND bt.platform=tt1.platform AND data_type='open'  AND t_date>='2016-04-06 10:41:07' AND t_date<='2016-05-06 10:41:07' LIMIT 1) AS open_num,(SELECT dev_num  FROM res_md_device_report AS tt2 WHERE bt.t_date=tt2.t_date AND bt.source=tt2.source AND bt.game_id=tt2.game_id AND bt.platform=tt2.platform AND data_type='togame'  AND t_date>='2016-04-06 10:41:07' AND t_date<='2016-05-06 10:41:07' LIMIT 1) AS togame_num,(SELECT dev_num FROM res_md_device_report AS tt3 WHERE bt.t_date=tt3.t_date AND bt.source=tt3.source AND bt.game_id=tt3.game_id AND bt.platform=tt3.platform AND data_type='pay_num'  AND t_date>='2016-04-06 10:41:07' AND t_date<='2016-05-06 10:41:07' LIMIT 1) AS pay_num,(SELECT dev_num FROM res_md_device_report AS tt4 WHERE bt.t_date=tt4.t_date AND bt.source=tt4.source AND bt.game_id=tt4.game_id AND bt.platform=tt4.platform AND data_type='pay_money'  AND t_date>='2016-04-06 10:41:07' AND t_date<='2016-05-06 10:41:07' LIMIT 1) AS pay_money,(SELECT dev_num FROM res_md_device_report AS tt5 WHERE bt.t_date=tt5.t_date AND bt.source=tt5.source AND bt.game_id=tt5.game_id AND bt.platform=tt5.platform AND data_type='active_mkt'  AND t_date>='2016-04-06 10:41:07' AND t_date<='2016-05-06 10:41:07' LIMIT 1) AS active_num,(SELECT dev_num FROM res_md_device_report AS tt6 WHERE bt.t_date=tt6.t_date AND bt.source=tt6.source AND bt.game_id=tt6.game_id AND bt.platform=tt6.platform AND data_type='active_mkt' AND type=1  AND t_date>='2016-04-06 10:41:07' AND t_date<='2016-05-06 10:41:07' LIMIT 1) AS new_active_num,(SELECT dev_num FROM res_md_device_report AS tt7 WHERE bt.t_date=tt7.t_date AND bt.source=tt7.source AND bt.game_id=tt7.game_id AND bt.platform=tt7.platform AND data_type='active_opt'  AND t_date>='2016-04-06 10:41:07' AND t_date<='2016-05-06 10:41:07' LIMIT 1) AS active_opt_num FROM res_md_device_report AS bt WHERE 1=1 AND type='open'  AND t_date>='2016-04-06 10:41:07' AND t_date<='2016-05-06 10:41:07' GROUP BY t_date,source,game_id,platform ORDER BY t_date DESC LIMIT 0,10;

我次奥，这是我写过最长的一条SQL语句了，然后信誓旦旦的开启了我的查询之路，我等啊等，等啊等，10秒，15秒，30秒，好了，ctrl+c，反正我是没机会看到这条语句的执行结果出来，事实是我一直没ctrl+c，在尝试在改SQL了，然后酱紫的SQL出来了，查询速度也是能接受的，我是一个容易知足的人，在记录数有200多万行出这样的数据，1点几秒我是接受了.

SELECT t_date,source,game_id,platform,(SELECT dev_num FROM res_md_device_report AS tt1 WHERE bt.t_date=tt1.t_date AND bt.source=tt1.source AND bt.game_id=tt1.game_id AND bt.platform=tt1.platform AND data_type='open'  AND t_date>='2016-04-06 10:55:41' AND t_date<='2016-05-06 10:55:41' LIMIT 1) AS open_num,(SELECT dev_num  FROM res_md_device_report AS tt2 WHERE bt.t_date=tt2.t_date AND bt.source=tt2.source AND bt.game_id=tt2.game_id AND bt.platform=tt2.platform AND data_type='togame'  AND t_date>='2016-04-06 10:55:41' AND t_date<='2016-05-06 10:55:41' LIMIT 1) AS togame_num,(SELECT dev_num FROM res_md_device_report AS tt3 WHERE bt.t_date=tt3.t_date AND bt.source=tt3.source AND bt.game_id=tt3.game_id AND bt.platform=tt3.platform AND data_type='pay_num'  AND t_date>='2016-04-06 10:55:41' AND t_date<='2016-05-06 10:55:41' LIMIT 1) AS pay_num,(SELECT dev_num FROM res_md_device_report AS tt4 WHERE bt.t_date=tt4.t_date AND bt.source=tt4.source AND bt.game_id=tt4.game_id AND bt.platform=tt4.platform AND data_type='pay_money'  AND t_date>='2016-04-06 10:55:41' AND t_date<='2016-05-06 10:55:41' LIMIT 1) AS pay_money,(SELECT dev_num FROM res_md_device_report AS tt5 WHERE bt.t_date=tt5.t_date AND bt.source=tt5.source AND bt.game_id=tt5.game_id AND bt.platform=tt5.platform AND data_type='active_mkt'  AND t_date>='2016-04-06 10:55:41' AND t_date<='2016-05-06 10:55:41' LIMIT 1) AS active_num,(SELECT dev_num FROM res_md_device_report AS tt6 WHERE bt.t_date=tt6.t_date AND bt.source=tt6.source AND bt.game_id=tt6.game_id AND bt.platform=tt6.platform AND data_type='active_mkt' AND type=1  AND t_date>='2016-04-06 10:55:41' AND t_date<='2016-05-06 10:55:41' LIMIT 1) AS new_active_num,(SELECT dev_num FROM res_md_device_report AS tt7 WHERE bt.t_date=tt7.t_date AND bt.source=tt7.source AND bt.game_id=tt7.game_id AND bt.platform=tt7.platform AND data_type='active_opt'  AND t_date>='2016-04-06 10:55:41' AND t_date<='2016-05-06 10:55:41' LIMIT 1) AS active_opt_num FROM (SELECT t_date,source,game_id,platform FROM res_md_device_report AS bt WHERE 1=1 AND type='open'  AND t_date>='2016-04-06 10:55:41' AND t_date<='2016-05-06 10:55:41' GROUP BY t_date,source,game_id,platform ORDER BY t_date DESC LIMIT 0,10) AS bt;

 

怎么解释呢，就是先根据能定位到的记录的字段，查询出分页的结果，然后再作为临时表，外层加子查询，查询最终结果