表 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
(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语句怎么优化了,表结构不能修改。谢谢!
这句只有一个值,要不你的五个字查询能组合起来?
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
FROM (SELECT [scheme] AS hm_scheme FROM D UNION ALL 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
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