帮忙优化一下SQL语句解决办法
日期：2014-05-18 浏览次数：20446 次
帮忙优化一下SQL语句
表 A,C,D,E,B
SELECT
(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 1) AS p1,
(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 2) AS p2,
(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 3) AS p3,
(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 4) AS p4,
(SELECT bs FROM C WHERE ID = B.hm_scheme) AS bs,
FROM (SELECT scheme, Stime FROM D
UNION ALL
SELECT znum, Ztime FROM E) B
表A,C,D,E 中各有几万到十几条记录,发现用这条SQL语句读取记录很慢，请问这个SQL语句怎么优化了，表结构不能修改。
谢谢!
------解决方案--------------------
四个表,几万到十几万,可能产生笛卡尔积,也许会很慢.
另:(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 1) AS p1
这句只有一个值,要不你的五个字查询能组合起来?
------解决方案--------------------
你这个from后边的有啥用啊，前边的select里面根本就没有用到。
------解决方案--------------------
使用case语句，连接来代替子查询，提高性能。
------解决方案--------------------
This should be quicker.
表 A,C,D,E,B
SELECT
(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 1) AS p1,
(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 2) AS p2,
(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 3) AS p3,
(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 4) AS p4,
(SELECT bs FROM C WHERE ID = B.hm_scheme) AS bs,
FROM (SELECT scheme, Stime FROM D
UNION ALL
SELECT znum, Ztime FROM E) B
表A,C,D,E 中各有几万到十几条记录,发现用这条SQL语句读取记录很慢，请问这个SQL语句怎么优化了，表结构不能修改。
谢谢!
------解决方案--------------------
四个表,几万到十几万,可能产生笛卡尔积,也许会很慢.
另:(SELECT name FROM A WHERE ID = B.hm_scheme AND CID = 1) AS p1
这句只有一个值,要不你的五个字查询能组合起来?
------解决方案--------------------
你这个from后边的有啥用啊，前边的select里面根本就没有用到。
------解决方案--------------------
使用case语句，连接来代替子查询，提高性能。
------解决方案--------------------
This should be quicker.
- SQL code
SELECT a1.[name] AS p1, a2.[name] AS p2, a3.[name] AS p3, a4.[name] As P4, c.bs AS bs FROM (SELECT [scheme] AS hm_scheme FROM D UNION SELECT znum FROM E) B JOIN (SELECT [name], ID from A Where CID = 1) a1 ON a1.ID = B.hm_scheme JOIN (SELECT [name], ID from A Where CID = 2) a2 ON a2.ID = B.hm_scheme JOIN (SELECT [name], ID from A Where CID = 3) a3 ON a3.ID = B.hm_scheme JOIN (SELECT [name], ID from A Where CID = 4) a4 ON a4.ID = B.hm_scheme JOIN C ON C.ID = B.hm_scheme
------解决方案--------------------
- SQL code
SELECT p1,p2,p3,p4,bs FROM (SELECT scheme, Stime FROM D UNION ALL SELECT znum, Ztime FROM E) B join (SELECT p1=name FROM A WHERE CID = 1) AS T1 on B.scheme=T1.ID join (SELECT p2=name FROM A WHERE CID = 2) AS T2 on B.scheme=T2.ID join (SELECT p3=name FROM A WHERE CID = 3) AS T3 on B.scheme=T3.ID join (SELECT p4=name FROM A WHERE CID = 4) AS T4 on B.scheme=T4.ID join (SELECT bs FROM C ) AS T5 on B.scheme=T5.ID
------解决方案--------------------
Generics
的方法可以参考
------解决方案--------------------
把join改为left join即可。
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
