請問怎麼簡化這個SQL? |
|
尚未結案
|
|
kartan_01
一般會員   發表：42 回覆：60 積分：20 註冊：2004-12-08 發送簡訊給我 |
SELECT
a.pmc_code1,
ISNULL(SUM(a.pmc_pqty),0) AS pqty,
ISNULL(T4.pepo,0) AS pepo,
ISNULL(SUM(a.pmc_hour_mc-a.pmc_exhour),0)/60 AS mach,
ISNULL(T1.mc40,0) AS mc40,
ISNULL(T2.mc70,0) AS mc70,
ISNULL(T3.mc100,0) AS mc100
FROM isfc_repmc a LEFT JOIN
(
SELECT
b.pmc_code1,ISNULL(SUM(b.pmc_hour_mc-b.pmc_exhour),0)/60 AS mc40
FROM isfc_repmc b,isfc_machine c
WHERE
b.pmc_date BETWEEN '2005/09/01' AND '2005/09/30'
AND
b.pmc_mac_no=c.fcmc_mac_no
AND
c.fcmc_grade='40'
GROUP BY b.pmc_code1
HAVING ISNULL(SUM(b.pmc_hour_mc-b.pmc_exhour),0) > 0
)T1 ON a.pmc_code1=T1.pmc_code1 LEFT JOIN
(
SELECT
b.pmc_code1,ISNULL(SUM(b.pmc_hour_mc-b.pmc_exhour),0)/60 AS mc70
FROM isfc_repmc b,isfc_machine c
WHERE
b.pmc_date BETWEEN '2005/09/01' AND '2005/09/30'
AND
b.pmc_mac_no=c.fcmc_mac_no
AND
c.fcmc_grade='70'
GROUP BY b.pmc_code1
HAVING ISNULL(SUM(b.pmc_hour_mc-b.pmc_exhour),0) > 0
)T2 ON a.pmc_code1=T2.pmc_code1 LEFT JOIN
(
SELECT
b.pmc_code1,ISNULL(SUM(b.pmc_hour_mc-b.pmc_exhour),0)/60 AS mc100
FROM isfc_repmc b,isfc_machine c
WHERE
b.pmc_date BETWEEN '2005/09/01' AND '2005/09/30'
AND
b.pmc_mac_no=c.fcmc_mac_no
AND
c.fcmc_grade='100'
GROUP BY b.pmc_code1
HAVING ISNULL(SUM(b.pmc_hour_mc-b.pmc_exhour),0) > 0
)T3 ON a.pmc_code1=T3.pmc_code1 LEFT JOIN
(
SELECT b.pma_code1,ISNULL(SUM(b.pma_hour_ma),0) AS pepo
FROM isfc_repman b
WHERE b.pma_date BETWEEN '2005/09/01' AND '2005/09/30'
GROUP BY b.pma_code1
HAVING ISNULL(SUM(b.pma_hour_ma),0) > 0
) T4 ON a.pmc_code1=T4.pma_code1
WHERE
a.pmc_date BETWEEN '2005/09/01' AND '2005/09/30'
GROUP BY a.pmc_code1,T1.mc40,T2.mc70,T3.mc100,T4.pepo
HAVING ISNULL(SUM(a.pmc_hour_mc-a.pmc_exhour),0) > 0
ORDER BY a.pmc_code1
|
|
Arlung Miao
初階會員  發表：9 回覆：44 積分：25 註冊：2004-08-25 發送簡訊給我 |
幾點説明：
1. 我用了SQL Server語法，如果為Oracle,則請將Case語句改為Decode函數
2. 如果Union All前面的那個子句返回的記錄比較多(上萬行)，則可以在那裏By F1, F2再Group一次
SELECT f1 AS pmc_code1
, ISNULL(SUM(CASE WHEN f1 <> 'peop' THEN f4 ELSE 0 END), 0) AS pqty
, ISNULL(SUM(CASE WHEN f1 = 'pepo' THEN f3 ELSE 0 END), 0) AS pepo
, ISNULL(SUM(CASE WHEN f1 <> 'peop' THEN f3 ELSE 0 END), 0) AS mach
, ISNULL(SUM(CASE WHEN f1 = '40' THEN f3 ELSE 0 END), 0) AS mc40
, ISNULL(SUM(CASE WHEN f1 = '70' THEN f3 ELSE 0 END), 0) AS mc70
, ISNULL(SUM(CASE WHEN f1 = '100' THEN f3 ELSE 0 END), 0) AS mc100
FROM
(
SELECT b.pmc_code1 f1
, (CASE WHEN c.fcmc_grade in ('40', '70', '100') THEN c.fcmc_grade ELSE '999' END) f2
, SUM(b.pmc_hour_mc-b.pmc_exhour)/60 AS f3
, SUM(a.pmc_pqty) AS f4
FROM isfc_repmc b,isfc_machine c
WHERE b.pmc_date BETWEEN '2005/09/01' AND '2005/09/30'
AND b.pmc_mac_no = c.fcmc_mac_no
GROUP BY b.pmc_code1, c.fcmc_grade
HAVING ISNULL(SUM(b.pmc_hour_mc-b.pmc_exhour),0) > 0
UNION ALL
SELECT b.pmc_code1 AS f1
, 'pepo' AS f2
, SUM(b.pma_hour_ma) AS f3
, 0 AS f4
FROM isfc_repman b
WHERE b.pma_date BETWEEN '2005/09/01' AND '2005/09/30'
GROUP BY b.pma_code1
HAVING SUM(b.pma_hour_ma) > 0
) z
GROUP BY f1, f2
ORDER BY f1
|
發表時間：2005-10-14 09:13:58
IP:61.221.xxx.xxx 未訂閱
系統時間：2024-11-23 6:34:32
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |