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 ";

 

 

分享給大家.