iT邦幫忙

0

SQL查詢疑問

Ray 2020-09-21 09:42:251667 瀏覽

最近在學習SQL查詢語法,遇到個問題想不到如何透過SQL語句得到想要的結果,請前輩幫忙
如圖:
https://ithelp.ithome.com.tw/upload/images/20200921/2012965240QmSUPhb7.png

想請問如何透過表一產生表二的結果

MONTH(日期) AS Month ; Sum(銷售額) ; Group by Month
3
skyksl066
iT邦新手 5 級 ‧ 2020-09-21 09:58:32
最佳解答
SELECT
    `月份`,
    SUM(`銷售額`) as `總銷售額`
FROM
    (
    SELECT
        *,
        DATE_FORMAT(`日期`, '%Y-%m') as `月份`
    FROM
        `表一`
) as `表一`
GROUP BY `表一`.`月份`
SELECT
    `A`.`月份`,
    COALESCE(SUM(`表一`.`銷售額`),
    0) AS `總銷售額`
FROM
    (
    SELECT
        `月份`.`i` AS `月份`
    FROM
        (
        SELECT
            1 AS i
        UNION
    SELECT
        2
    UNION
SELECT
    3
UNION
SELECT
    4
UNION
SELECT
    5
UNION
SELECT
    6
UNION
SELECT
    7
UNION
SELECT
    8
UNION
SELECT
    9
UNION
SELECT
    10
UNION
SELECT
    11
UNION
SELECT
    12
    ) AS `月份`
) AS `A`
LEFT JOIN `表一` ON `A`.`月份` = MONTH(`表一`.`日期`)
GROUP BY
    `A`.`月份`
ORDER BY
    `A`.`月份`

思考流路
先產生一張table 1~12月
再把表一join回來
加總銷售額 null轉0
在把日期轉成月份
然後把月份群組
最後排序月份

看更多先前的回應...收起先前的回應...
Ray iT邦新手 5 級 ‧ 2020-09-21 10:46:36 檢舉

感謝S大的回答 剛稍作修改後可以得到下圖結果https://ithelp.ithome.com.tw/upload/images/20200921/201296525ldLGiu2Kk.png

請問若該月份沒有資料,要如何生成值為0的欄位?

遞迴查詢

CREATE TABLE t1(
    d date
  , m int
);

INSERT INTO
  t1(d, m)
VALUES
    ('2020-09-01', 100)
  , ('2020-09-02', 100)
  , ('2020-10-01', 500)
  , ('2020-12-01', 1000)

DECLARE  @t TABLE(
    StartDate DATETIME
  , EndDate DATETIME
);

INSERT INTO @t
  (StartDate, EndDate)
VALUES
  ('2020/01/01', '2021/01/01');

;WITH CTE (Dates,EndDate) AS
(
  SELECT
      StartDate Dates
    , EndDate EndDate
  FROM 
    @t
  UNION ALL
  SELECT
      DATEADD(MONTH, 1, Dates)
    , EndDate
  FROM
    CTE
  WHERE
    DATEADD(MONTH, 1, Dates) < EndDate
)

SELECT
    Month(CTE.Dates) Month
  , CASE WHEN SUM(t1.m) IS NULL
      THEN 0 
      ELSE SUM(t1.m) 
    END Total
FROM CTE
LEFT JOIN
  t1
  ON
    MONTH(CTE.Dates) = MONTH(t1.d)
GROUP BY
  CTE.Dates

dbfiddle

Zed_Yang iT邦新手 3 級 ‧ 2020-09-21 11:10:49 檢舉
SELECT A.mouth, ISNULL(B.總銷售額,0) as 總銷售額
FROM 所有月份 A
LEFT JOIN (SELECT
    `月份` as mouth,
    SUM(`銷售額`) as `總銷售額`
FROM
    (
    SELECT
        *,
        DATE_FORMAT(`日期`, '%Y-%m') as `月份`
    FROM
        `表一`
) as `表一`
GROUP BY `表一`.`月份`) B ON B.mouth = A.mouth

第二行的FROM 想辦法取得01-12

skyksl066 iT邦新手 5 級 ‧ 2020-09-21 11:43:47 檢舉

已補上

0
allenlwh
iT邦研究生 1 級 ‧ 2020-09-21 10:43:23
select month(日期) as '月份',sum(銷售額) as '總銷售額'
group by month(日期)
order by month(日期)
Ray iT邦新手 5 級 ‧ 2020-09-21 14:27:27 檢舉

感謝

0
samid
iT邦新手 5 級 ‧ 2020-09-22 16:27:32

SELECT CASE WHEN MONTH(日期)=1 then sum(銷售額) else 0 end as '1月'

,CASE WHEN MONTH(日期)=9 then sum(銷售額) else 0 end as '9月'
,CASE WHEN MONTH(日期)=10 then sum(銷售額) else 0 end as '10月'
,CASE WHEN MONTH(日期)=11 then sum(銷售額) else 0 end as '11月'
,CASE WHEN MONTH(日期)=12 then sum(銷售額) else 0 end as '12月'
FROM 表一
group by MONTH(日期)

我要發表回答

立即登入回答