各位前輩先進你們好,遇到一個問題想請教
現有一MS SQL資料表a111
MKDAT=生產日期 (中華民國曆)
ORDER_NO=訂單號碼
MKQTY=生產數量
我想求出以查詢日前13個月的訂單生產量每個級距共有多少筆的級距表
1.每月500 pc以下有多少訂單筆數 (COUNT)
2.每月501~1000pc(含)有多少訂單筆數 (COUNT)
3.每月1000 pc以上有多少訂單筆數 (COUNT)
4.每月共有多少訂單筆數 (COUNT)
5.前12個月共有多少訂單筆數 (COUNT)
我在sqlfiddle將資料表建立後,求上1~5點
http://sqlfiddle.com/#!18/beacd/1/0
CREATE TABLE a1111
([PMM] int, [ORDER_NO] varchar(6), [MKQTY] int)
;
INSERT INTO a1111
([PMM], [ORDER_NO], [MKQTY])
VALUES
(10905, '4N0241', 250),
(10905, '4N0301', 375),
(10905, '4N0292', 3700),
(10905, '4N0331', 1335),
(10905, '4N0341', 365),
(10906, '5N3234', 1425),
(10906, '5N3245', 7400),
(10906, '5N0271', 1075),
(10906, '5N0284', 1200),
(10906, '5N3251', 4000),
(10907, '6N3191', 2800),
(10907, '6N3103', 8800),
(10907, '6N0371', 1200),
(10907, '6N3194', 1540),
(10907, '6N3104', 2250),
(10908, '7N3229', 10160),
(10908, '7N0202', 1025),
(10908, '7N0251', 6600),
(10908, '7N0221', 1400),
(10908, '7N3283', 3400),
(10908, '8N3215', 1890),
(10908, '8N3222', 9525),
(10908, '8N0151', 7575),
(10909, '8N0121', 11550),
(10909, '9N3011', 7900),
(10909, '8N0121', 660),
(10909, '8N0201', 1515),
(10909, '9N3011', 2600),
(10909, '8N0211', 295),
(10909, '9N3012', 10500),
(10909, '8N3231', 8230),
(10909, '9N3013', 2250),
(10909, '9N3014', 2650),
(10910, '9N0131', 7000),
(10910, '9N0341', 5170),
(10910, '9N3244', 1535),
(10910, '9N3245', 1235),
(10911, 'AN3226', 715),
(10911, 'AN3223', 695),
(10911, 'AN3251', 6015),
(10911, 'AN3197', 4600),
(10911, 'AN3222', 685),
(10911, 'AN3231', 1535),
(10912, 'BN3223', 1825),
(10912, 'BN3227', 1535),
(10912, 'BN3226', 5075),
(10912, 'BN0291', 200),
(10912, 'BN3224', 325),
(10912, 'BN3221', 1115),
(10912, 'BN3225', 1550),
(10912, 'BN3244', 525),
(11001, 'CN0031', 4000),
(11001, 'CN0301', 600),
(11001, 'CN0291', 4200),
(11001, 'CN3154', 1550),
(11001, 'CN0291', 6000),
(11001, 'CN3134', 2125),
(11001, 'CN0031', 6000),
(11002, '1N0271', 10000),
(11002, '1N0264', 3700),
(11002, '1N3192', 10280),
(11002, '1N0267', 1925),
(11002, '1N0264', 1500),
(11002, '1N0265', 1910),
(11002, '1N0282', 1625),
(11002, '1N3244', 300),
(11002, '1N3248', 1075),
(11003, '2N3196', 2800),
(11003, '2N3201', 950),
(11003, '2N0271', 1690),
(11003, '2N3197', 9975),
(11003, '2N0243', 225),
(11004, '3N3123', 16800),
(11004, '3N3124', 3900),
(11004, '3N3121', 19800),
(11004, '3N3161', 7500),
(11004, '3N3161', 5425),
(11004, '3N3122', 4600),
(11004, '3N3124', 6300),
(11004, '3N3121', 1800),
(11004, '3N3161', 10375),
(11004, '3N3122', 12100),
(11005, '4N3197', 2908),
(11005, '4N3196', 250),
(11005, '4N3194', 10000),
(11005, '4N3195', 1950),
(11005, '5N3021', 1025),
(11005, '4N3192', 100),
(11005, '4N3191', 5000),
(11005, '4N3211', 1765),
(11005, '5N0011', 2045),
(11005, '4N3192', 4200),
(11005, '4N3193', 5100),
(11005, '4N3192', 700),
(11006, '5N3291', 4850),
(11006, '5N0181', 1325),
(11006, '6N3011', 10327),
(11006, '5N3243', 3900),
(11006, '5N3302', 8800),
(11006, '6N3021', 3850),
(11006, '5N3243', 6075),
(11006, '5N3302', 8895),
(11006, '5N0171', 5225),
(11006, '6N3031', 300),
(11006, '6N3021', 1300)
;
以上謝謝大家
-- Case 1 : 查詢日當月為第一個月
SELECT CASE WHEN CAST(14-ROW_NUMBER() OVER (ORDER BY PMM) AS CHAR(2)) ='0' THEN '<1~12>'
ELSE CAST(14-ROW_NUMBER() OVER (ORDER BY PMM) AS CHAR(2)) END AS 'Phase',
CASE WHEN PMM =99999 THEN NULL ELSE PMM END AS PMM,
CLASS1 AS 'MKQTY<=500',CLASS2 AS 'MKQTY>500 & MKQTY<=1000',CLASS3 AS 'MKQTY>1000',Total
FROM (
SELECT PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'CLASS1',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'CLASS2',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'CLASS3',
COUNT(MKQTY) AS 'Total'
FROM a1111
WHERE PMM+191100=CONVERT(CHAR(6),DATEADD(MONTH,-12,GETDATE()),112)
GROUP BY PMM
UNION ALL
SELECT CASE WHEN PMM IS NULL THEN 99999 ELSE PMM END AS PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'CLASS1',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'CLASS2',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'CLASS3',
COUNT(MKQTY) AS 'Total'
FROM a1111
WHERE PMM+191100>=CONVERT(CHAR(6),DATEADD(MONTH,-11,GETDATE()),112)
AND PMM+191100<=CONVERT(CHAR(6),DATEADD(MONTH,0,GETDATE()),112)
GROUP BY ROLLUP(PMM)
) AS M
ORDER BY CASE WHEN PMM IS NULL THEN 99999 ELSE PMM END
-- Case 2 : 查詢日的上一個月為第一個月
SELECT CASE WHEN CAST(14-ROW_NUMBER() OVER (ORDER BY PMM) AS CHAR(2)) ='0' THEN '<1~12>'
ELSE CAST(14-ROW_NUMBER() OVER (ORDER BY PMM) AS CHAR(2)) END AS 'Phase',
CASE WHEN PMM =99999 THEN NULL ELSE PMM END AS PMM,
CLASS1 AS 'MKQTY<=500',CLASS2 AS 'MKQTY>500 & MKQTY<=1000',CLASS3 AS 'MKQTY>1000',Total
FROM (
SELECT PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'CLASS1',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'CLASS2',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'CLASS3',
COUNT(MKQTY) AS 'Total'
FROM a1111
WHERE PMM+191100=CONVERT(CHAR(6),DATEADD(MONTH,-13,GETDATE()),112)
GROUP BY PMM
UNION ALL
SELECT CASE WHEN PMM IS NULL THEN 99999 ELSE PMM END AS PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'CLASS1',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'CLASS2',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'CLASS3',
COUNT(MKQTY) AS 'Total'
FROM a1111
WHERE PMM+191100>=CONVERT(CHAR(6),DATEADD(MONTH,-12,GETDATE()),112)
AND PMM+191100<=CONVERT(CHAR(6),DATEADD(MONTH,-1,GETDATE()),112)
GROUP BY ROLLUP(PMM)
) AS M
ORDER BY CASE WHEN PMM IS NULL THEN 99999 ELSE PMM END
-- Case 3 : 查詢日當月為第一個月
SELECT PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'MKQTY<=500',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'MKQTY>500 & MKQTY<=1000',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'MKQTY>1000',
COUNT(MKQTY) AS 'Total',
SUM(CASE WHEN PMM+191100=CONVERT(CHAR(6),DATEADD(MONTH,-12,GETDATE()),112) THEN 0
ELSE COUNT(MKQTY) END) OVER (ORDER BY PMM) AS 'Accumulation'
FROM a1111
WHERE PMM+191100>=CONVERT(CHAR(6),DATEADD(MONTH,-12,GETDATE()),112)
AND PMM+191100<=CONVERT(CHAR(6),DATEADD(MONTH,0,GETDATE()),112)
GROUP BY PMM
ORDER BY PMM
Thanks for sharing this information.สล็อตเครดิตฟรีแค่สมัคร
請用 http://sqlfiddle.com/ 把你的測試資料表跟查詢句都做一份線上測試版,SAVE後把網址貼上來,大家比較方便解答,記得選SQL版本
WITH EveryMonth AS
( SELECT PMM
, COUNT(*) AS EveryMonthCount
FROM a1111
GROUP BY PMM ),
Lower500 AS
( SELECT PMM
, COUNT(*) AS Lower500Count
FROM a1111
WHERE MKQTY <= 500
GROUP BY PMM ),
Between501To1000 AS
( SELECT PMM
, COUNT(*) AS Between501To1000Count
FROM a1111
WHERE MKQTY BETWEEN 501 AND 1000
GROUP BY PMM),
MoreThan1001 AS
( SELECT PMM
, COUNT(*) AS MoreThan1001
FROM a1111
WHERE MKQTY >= 1001
GROUP BY PMM ),
OneYearBefore AS
(
SELECT MONTH01.PMM
, SUM(MONTH02.EveryMonthCount) AS BeforeOneYearCount
FROM EveryMonth MONTH01
INNER JOIN EveryMonth MONTH02
ON MONTH02.PMM < MONTH01.PMM AND MONTH02.PMM >= MONTH01.PMM - 100
GROUP BY MONTH01.PMM
)
SELECT EveryMonth.PMM
,EveryMonthCount
,ISNULL(Lower500Count, 0) AS Lower500Count
,ISNULL(Between501To1000Count, 0) AS Between501To1000Count
,ISNULL(MoreThan1001, 0) AS MoreThan1001
,ISNULL(BeforeOneYearCount, 0) AS BeforeOneYearCount
FROM EveryMonth
LEFT JOIN Lower500
ON EveryMonth.PMM = Lower500.PMM
LEFT JOIN Between501To1000
ON EveryMonth.PMM = Between501To1000.PMM
LEFT JOIN MoreThan1001
ON EveryMonth.PMM = MoreThan1001.PMM
LEFT JOIN OneYearBefore
ON EveryMonth.PMM = OneYearBefore.PMM
http://sqlfiddle.com/#!18/beacd/68
PMM | EveryMonthCount | Lower500Count | Between501To1000Count | MoreThan1001 | BeforeOneYearCount |
---|---|---|---|---|---|
10905 | 5 | 3 | 0 | 2 | 0 |
10906 | 5 | 0 | 0 | 5 | 5 |
10907 | 5 | 0 | 0 | 5 | 10 |
10908 | 8 | 0 | 0 | 8 | 15 |
10909 | 10 | 1 | 1 | 8 | 23 |
10910 | 4 | 0 | 0 | 4 | 33 |
10911 | 6 | 0 | 3 | 3 | 37 |
10912 | 8 | 2 | 1 | 5 | 43 |
11001 | 7 | 0 | 1 | 6 | 51 |
11002 | 9 | 1 | 0 | 8 | 58 |
11003 | 5 | 1 | 1 | 3 | 67 |
11004 | 10 | 0 | 0 | 10 | 72 |
11005 | 12 | 2 | 1 | 9 | 82 |
11006 | 11 | 1 | 0 | 10 | 89 |
你在尋找 condition aggregate function.可以使用CASE WHEN
搭配你的條件.
SELECT PMM,
COUNT(*) '總訂單筆數',
COUNT(CASE WHEN MKQTY <= 500 THEN 1 END) '500 pc以下',
COUNT(CASE WHEN MKQTY BETWEEN 501 AND 999 THEN 1 END) '501~1000pc(含)',
COUNT(CASE WHEN MKQTY >= 1000 THEN 1 END) '1000 pc以上',
COUNT(CASE WHEN [PMM] >= 10907 THEN 1 END) '前12個月'
FROM a1111
GROUP BY PMM
Note 我建議你前12個月資料判斷使用另一個Date的欄位