帮忙优化一下SQL语句解决办法

帮忙优化一下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.

SQL code

SELECT a1.[name] AS p1, a2.[name] AS p2, a3.[name] AS p3, a4.[name] As P4, c.bs AS bsFROM (SELECT [scheme] AS hm_scheme FROM D UNION SELECT znum FROM E) BJOIN (SELECT [name], ID from A Where CID = 1) a1 ON a1.ID = B.hm_schemeJOIN (SELECT [name], ID from A Where CID = 2) a2 ON a2.ID = B.hm_schemeJOIN (SELECT [name], ID from A Where CID = 3) a3 ON a3.ID = B.hm_schemeJOIN (SELECT [name], ID from A Where CID = 4) a4 ON a4.ID = B.hm_schemeJOIN C ON C.ID = B.hm_scheme
[解决办法]
SQL code
SELECT p1,p2,p3,p4,bsFROM (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即可。