MS SQL 級距筆數查詢

sql語法

johnstudy 2021-06-23 13:11:58 ‧ 2896 瀏覽

分享至

各位前輩先進你們好，遇到一個問題想請教
現有一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)
;

以上謝謝大家

窮嘶發發發 iT邦高手 1 級 ‧ 2021-06-23 13:27:53 檢舉

問題敘述寫 13個月
但第五題寫 12個月
前面四題沒寫期間，當作要找 13個月的資料，因此會有 13筆資料
第五題是算12個月總筆數，因此只有一筆
http://sqlfiddle.com/#!18/beacd/112 => DEMO
SQL :
DECLARE @M13 varchar(6)
SET @M13 = CONVERT(varchar(6),DATEADD(MONTH,-12,GETDATE()),112) - 191100

SELECT PMM,
COUNT(CASE WHEN MKQTY BETWEEN 0 AND 500 THEN 1 END) '500 pc以下',
COUNT(CASE WHEN MKQTY BETWEEN 501 AND 1000 THEN 1 END) '501~1000pc(含)',
COUNT(CASE WHEN MKQTY > 1000 THEN 1 END) '1000 pc以上',
COUNT(*) '每月訂單筆數'
FROM a1111 where PMM >= @M13
GROUP BY PMM;

DECLARE @M12 varchar(6)
SET @M12 = CONVERT(varchar(6),DATEADD(MONTH,-11,GETDATE()),112) - 191100

SELECT count(MKQTY) AS '筆數'
FROM a1111
WHERE PMM >= @M12;

rogeryao iT邦超人 8 級 ‧ 2021-06-23 14:29:44 檢舉

A .以查詢日前13個月 =>13個月 ?
B .5.前12個月 =>前12個月 ?
不一樣嗎 ?
請附上預期的結果

johnstudy iT邦新手 5 級 ‧ 2021-06-28 09:37:54 檢舉

感謝大家的幫忙，每位先進的方式我都測試過了，雖然石頭大師的方式我很喜歡，但是還是給了rogeryao大師最佳解，感謝

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

5 個回答

rogeryao

iT邦超人 8 級 ‧ 2021-06-24 03:42:21

最佳解答

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

Demo

回應
分享
檢舉

登入發表回應

superslotauto

iT邦見習生 ‧ 2021-06-23 13:15:59

Thanks for sharing this information.สล็อตเครดิตฟรีแค่สมัคร

回應
分享
檢舉

登入發表回應

japhenchen

iT邦超人 1 級 ‧ 2021-06-23 13:16:03

請用 http://sqlfiddle.com/ 把你的測試資料表跟查詢句都做一份線上測試版，SAVE後把網址貼上來，大家比較方便解答，記得選SQL版本

回應
分享
檢舉

登入發表回應

通靈亡

iT邦高手 1 級 ‧ 2021-06-23 15:21:05

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

回應
分享
檢舉

登入發表回應

石頭

iT邦高手 1 級 ‧ 2021-06-23 21:23:24

你在尋找 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

dbfiddle

Note 我建議你前12個月資料判斷使用另一個Date的欄位