SQL查詢疑問

sql access c#

Ray 2020-09-21 09:42:25 ‧ 3462 瀏覽

分享至

最近在學習SQL查詢語法，遇到個問題想不到如何透過SQL語句得到想要的結果，請前輩幫忙
如圖:

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

leonliu777 iT邦新手 5 級 ‧ 2020-09-21 10:20:21 檢舉

MONTH(日期) AS Month ; Sum(銷售額) ; Group by Month

登入發表討論

直播研討會

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

直播中

3 個回答

skyksl066

iT邦新手 4 級 ‧ 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
在把日期轉成月份
然後把月份群組
最後排序月份

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

Ray iT邦新手 5 級 ‧ 2020-09-21 10:46:36 檢舉

感謝S大的回答剛稍作修改後可以得到下圖結果

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

listennn08 iT邦高手 5 級 ‧ 2020-09-21 11:08:57 檢舉

遞迴查詢

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邦新手 4 級 ‧ 2020-09-21 11:43:47 檢舉

已補上

登入發表回應

allenlwh

iT邦高手 1 級 ‧ 2020-09-21 10:43:23

select month(日期) as '月份',sum(銷售額) as '總銷售額'
group by month(日期)
order by month(日期)

回應 1
分享
檢舉

Ray iT邦新手 5 級 ‧ 2020-09-21 14:27:27 檢舉

感謝

登入發表回應

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(日期)