已解決430363個問題，去搜搜看，總會有你想問的

如何將如下數據按年月日統計，獲取統計數據

首頁猿問如何將如下數據按年月日統計，獲取統計數據

如何將如下數據按年月日統計，獲取統計數據

SQL Server

白豬掌柜的 2018-12-06 21:58:33

SET NOCOUNT ON SELECT COUNT(1) AS Total, day(InsertTime) as [Day] FROM Count_DataUpload GROUP BY day(InsertTime) SELECT COUNT(1) AS Total, month(InsertTime) AS [Month] FROM Count_DataUpload GROUP BY month(InsertTime) SELECT COUNT(1) AS Total, year(InsertTime) AS [Year] FROM Count_DataUpload GROUP BY year(InsertTime) 顯示結果： Total ? ? ? ? Day----------- ----------- 45 ? ? ? ? ? ? 20 Total ? ? ? ? ?Month----------- ----------- 45 ? ? ? ? ? ? 11 Total ? ? ? ? Year----------- ----------- 45 ? ? ? ? ? ? 2013 ? ? 有沒有辦法把這3個查詢轉換成 Year ? ?YearTotal ? ?Month ? ?MonthTotal ? ?Day ? ?DayTotal 2013 ? ?45 ? ? ? ? ? ? 11 ? ? ? ? ?45 ? ? ? ? ? ? ? ?20 ? ? ?45

查看完整描述

2 回答

烙印99

TA貢獻1829條經驗獲得超13個贊

SELECT
distinct
year(InsertTime) AS [Year],COUNT(1) over(partition by year(inserttime)) AS YearTotal
,month(InsertTime) AS [Month],COUNT(1) over(partition by month(inserttime)) AS MonthTotal,
day(InsertTime)as [Day],COUNT(1) over(partition by day(inserttime)) AS DayTotal
FROM Count_DataUpload

或者你想要的不就是直接union起來不就好了嗎簡化的方法是使用group by grouping sets

SELECT 
year(InsertTime) AS [Year],COUNT(1) AS YearTotal,
month(InsertTime) AS [Month],COUNT(1) AS MonthTotal,
day(InsertTime)as [Day],COUNT(1) AS DayTotal 
FROM Count_DataUpload GROUP BY 
grouping sets
(
    year(InsertTime),
    MONTH(inserttime),
    DAY(inserttime)
)

反對回復 2019-01-07

2 回答
0 關注
485 瀏覽

關注

添加回答

舉報

0/150

提交

取消

亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

熱搜

最近搜索清空

如何將如下數據按年月日統計，獲取統計數據

如何將如下數據按年月日統計，獲取統計數據

2 回答

添加回答

如何將如下數據按年月日統計，獲取統計數據