0

## SQL查詢疑問

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

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

### 3 個回答

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`.`月份`
``````

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

``````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
, EndDate
FROM
CTE
WHERE
)

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

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