我在 SQL-Server 中有一個這樣的表
| 一種 | 創建時間 |
|---|---|
| X | 2020-06-05 12:23:33.920 |
| X | 2021-09-15 12:23:33.920 |
| X | 2021-09-25 12:23:33.920 |
我需要創建一個獲取以下資料的視圖：
| 年 | 簡 | 二月 | 行進 | 四月 | 可能 | 六月 | 七月 | 八月 | 九月 | 十月 | 十一月 | 十二月 | 全部的 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2020 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2021 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 2 |
我知道如何按月計數，但我很難將計數分成兩行，每年一行。誰能給我一個提示？
非常感謝
uj5u.com熱心網友回復：
Group by year(CreatedAt)訣竅是，在 asum或count每個月和總數中使用 case 運算式：
select
year(CreatedAt) as Year,
sum(case when month(CreatedAt) = 1 then 1 else 0 end) as Jan,
sum(case when month(CreatedAt) = 2 then 1 else 0 end) as Feb,
sum(case when month(CreatedAt) = 3 then 1 else 0 end) as March,
sum(case when month(CreatedAt) = 4 then 1 else 0 end) as April,
sum(case when month(CreatedAt) = 5 then 1 else 0 end) as May,
sum(case when month(CreatedAt) = 6 then 1 else 0 end) as June,
sum(case when month(CreatedAt) = 7 then 1 else 0 end) as July,
sum(case when month(CreatedAt) = 8 then 1 else 0 end) as Aug,
sum(case when month(CreatedAt) = 9 then 1 else 0 end) as Sept,
sum(case when month(CreatedAt) = 10 then 1 else 0 end) as Oct,
sum(case when month(CreatedAt) = 11 then 1 else 0 end) as Nov,
sum(case when month(CreatedAt) = 12 then 1 else 0 end) as Dec,
count(*) as Total
from table_name
group by year(CreatedAt);
小提琴
uj5u.com熱心網友回復：
您也可以使用PIVOT。雖然我個人覺得Zakaria 的方法對于這個特定案例更緊湊。
db<>小提琴
SELECT
YearNum
, [Jan]
, [Feb]
, [Mar]
, [Apr]
, [May]
, [Jun]
, [Jul]
, [Aug]
, [Sep]
, [Oct]
, [Nov]
, [Dec]
, ( [Jan] [Feb] [Mar]
[Apr] [May] [Jun]
[Jul] [Aug] [Sep]
[Oct] [Nov] [Dec]
) AS Total
FROM
(
SELECT ColumnA
, DatePart(yyyy, CreatedAt) AS YearNum
, Left(DateName(mm, CreatedAt), 3) AS MonthName
FROM YourTable
) t
PIVOT (
COUNT(ColumnA)
FOR MonthName IN (
[Jan]
, [Feb]
, [Mar]
, [Apr]
, [May]
, [Jun]
, [Jul]
, [Aug]
, [Sep]
, [Oct]
, [Nov]
, [Dec]
)
) AS pvt
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/432430.html
上一篇：在高流量請求期間防止超額預訂