SQL巢狀查詢總結

IT也有一段時間了,剛開始的時候``````
的困難主要是在程式設計語言上,數組,邏輯,演算法,...
這些都過來了之後,困難就上升到資料庫設計上了.
以及資料邏輯.
一個優秀的系統,會整合優秀的程式和優秀的資料庫設計.
要做到這點得有足夠的經驗.
這是我寫的一個結合UINON的巢狀查詢.
將五個方面的報表放到一個暫存資料表裡,再從暫存資料表裡,將所要的資料查詢出來. 
複製代碼 代碼如下:
$sql="SELECT type , sum( yjsl ) as yjsl , sum( yysl ) as yysl, sum( jyrs ) as jyrs, sum( jycs ) as jycs
FROM (
SELECT c.mc AS
TYPE , count( d.lsh ) AS yjsl, 0 AS yysl, 0 AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_dzyjb AS d
WHERE b.bm = c.lsh
AND d.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, count( e.lsh ) AS yysl, 0 AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_dzyy AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, 0 AS yysl, count( DISTINCT e.dzlsh ) AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_ltxxb AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, 0 AS yysl, 0 AS jyrs, count( DISTINCT e.lsh ) AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_ltxxb AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
) AS temptable
GROUP BY TYPE ";

分享給大家.