SQL Server按月分組

我有一個(gè)具有此架構(gòu)的表

ItemID UserID Year IsPaid PaymentDate Amount

1 1 2009 0 2009-11-01 300

2 1 2009 0 2009-12-01 342

3 1 2010 0 2010-01-01 243

4 1 2010 0 2010-02-01 2543

5 1 2010 0 2010-03-01 475

我正在嘗試查詢顯示每個(gè)月的總數(shù)的查詢。 到目前為止，我已經(jīng)嘗試過(guò)DateDiff和嵌套選擇，但都沒(méi)有給我想要的東西。 我認(rèn)為這是最接近的：

DECLARE @start [datetime] = 2010/4/1;

SELECT ItemID, IsPaid,

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And DateDiff(m, PaymentDate, @start) = 0 AND UserID = 100) AS "Apr",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =1 AND UserID = 100) AS "May",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =2 AND UserID = 100) AS "Jun",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =3 AND UserID = 100) AS "Jul",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =4 AND UserID = 100) AS "Aug",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =5 AND UserID = 100) AS "Sep",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =6 AND UserID = 100) AS "Oct",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =7 AND UserID = 100) AS "Nov",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =8 AND UserID = 100) AS "Dec",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =9 AND UserID = 100) AS "Jan",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =10 AND UserID = 100) AS "Feb",

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =11 AND UserID = 100) AS "Mar"

FROM LIVE L INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY

WHERE UserID = 16178

但是當(dāng)我應(yīng)該獲取值時(shí)，我只會(huì)得到空值。 我想念什么嗎？

7個(gè)解決方案

101 votes

SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount]

FROM Payments

GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120)

ORDER BY [Month]

您也可以嘗試：

SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount]

FROM Payments

GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)

ORDER BY Year, Month

Dave Downs answered 2020-06-17T06:16:37Z

20 votes

將NVARCHAR的尺寸限制為7，提供給CONVERT以僅顯示“ YYYY-MM”

SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Month], SUM(Amount) [TotalAmount]

FROM Payments

GROUP BY CONVERT(NVARCHAR(7),PaymentDate,120)

ORDER BY [Month]

Martyn Davis answered 2020-06-17T06:16:58Z

5 votes

我更喜歡結(jié)合Month和DateTime函數(shù)，如下所示：

GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0)

這兩個(gè)函數(shù)共同將比指定日期部分(在此示例中為Month)小的日期分量歸零。

您可以將datepart位更改為Month、DateTime、DAY等，這非常方便。

這樣，您原始的SQL查詢將類似于以下內(nèi)容(由于我沒(méi)有設(shè)置數(shù)據(jù)，因此無(wú)法對(duì)其進(jìn)行測(cè)試，但它應(yīng)該使您處于正確的軌道)。

DECLARE @start [datetime] = '2010-04-01';

SELECT

ItemID,

UserID,

DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0) [Month],

IsPaid,

SUM(Amount)

FROM LIVE L

INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY

WHERE UserID = 16178

AND PaymentDate > @start

還有一件事：Month列鍵入為DateTime，如果您需要進(jìn)一步處理該數(shù)據(jù)或?qū)⑵溆成錇?NET對(duì)象，這也是一個(gè)不錯(cuò)的優(yōu)勢(shì)。

bounav answered 2020-06-17T06:17:36Z

3 votes

DECLARE @start [datetime] = 2010/4/1;

應(yīng)該...

DECLARE @start [datetime] = '2010-04-01';

您所擁有的是將2010除以4，然后除以1，然后轉(zhuǎn)換為日期。 從1900-01-01開(kāi)始的第57.5天。

初始化后，嘗試SELECT @start，以檢查是否正確。

MatBailie answered 2020-06-17T06:18:05Z

3 votes

如果您需要經(jīng)常執(zhí)行此操作，則可能要向表中添加一個(gè)計(jì)算列PaymentMonth：

ALTER TABLE dbo.Payments ADD PaymentMonth AS MONTH(PaymentDate) PERSISTED

它被持久化并存儲(chǔ)在表中-因此查詢它實(shí)際上沒(méi)有性能開(kāi)銷。 這是一個(gè)4字節(jié)的INT值-因此空間開(kāi)銷也很小。

一旦有了它，就可以簡(jiǎn)化查詢，使其類似于以下內(nèi)容：

SELECT ItemID, IsPaid,

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 1 AND UserID = 100) AS 'Jan',

(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 2 AND UserID = 100) AS 'Feb',

.... and so on .....

FROM LIVE L

INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY

WHERE UserID = 16178

marc_s answered 2020-06-17T06:18:34Z

1 votes

另一種不涉及在結(jié)果中添加列的方法是簡(jiǎn)單地將日期的CONVERT零置零，因此CONVERT和2016-07-16都將是2016-07-01-從而使它們按月相等。

如果您具有datetime(而不是CONVERT)值，則可以將其直接置零：

SELECT

DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] ),

COUNT(*)

FROM

[Table]

GROUP BY

DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] )

如果您有datetime值，則需要使用CONVERT刪除“時(shí)段”部分：

SELECT

DATEADD( day, 1 - DATEPART( day, [Date] ), CONVERT( date, [Date] ) ),

COUNT(*)

FROM

[Table]

GROUP BY

DATEADD( day, 1 - DATEPART( day, [Date] ), CONVERT( date, [Date] ) )

Dai answered 2020-06-17T06:19:03Z

0 votes

現(xiàn)在，您的查詢明確地只查看了年份= 2010的付款，但是，我想您的意思是讓Jan / Feb / Mar實(shí)際代表2009。如果是這樣，則需要針對(duì)這種情況進(jìn)行一些調(diào)整。 不要繼續(xù)查詢每一列的總和值，而只是查詢以月為單位的日期差的條件。 將其余的放在WHERE子句中。

SELECT

SUM( case when DateDiff(m, PaymentDate, @start) = 0

then Amount else 0 end ) AS "Apr",

SUM( case when DateDiff(m, PaymentDate, @start) = 1

then Amount else 0 end ) AS "May",

SUM( case when DateDiff(m, PaymentDate, @start) = 2

then Amount else 0 end ) AS "June",

SUM( case when DateDiff(m, PaymentDate, @start) = 3

then Amount else 0 end ) AS "July",

SUM( case when DateDiff(m, PaymentDate, @start) = 4

then Amount else 0 end ) AS "Aug",

SUM( case when DateDiff(m, PaymentDate, @start) = 5

then Amount else 0 end ) AS "Sep",

SUM( case when DateDiff(m, PaymentDate, @start) = 6

then Amount else 0 end ) AS "Oct",

SUM( case when DateDiff(m, PaymentDate, @start) = 7

then Amount else 0 end ) AS "Nov",

SUM( case when DateDiff(m, PaymentDate, @start) = 8

then Amount else 0 end ) AS "Dec",

SUM( case when DateDiff(m, PaymentDate, @start) = 9

then Amount else 0 end ) AS "Jan",

SUM( case when DateDiff(m, PaymentDate, @start) = 10

then Amount else 0 end ) AS "Feb",

SUM( case when DateDiff(m, PaymentDate, @start) = 11

then Amount else 0 end ) AS "Mar"

FROM

Payments I

JOIN Live L

on I.LiveID = L.Record_Key

WHERE

Year = 2010

AND UserID = 100

DRapp answered 2020-06-17T06:19:24Z

總結(jié)

以上是生活随笔為你收集整理的sql月度分组_SQL Server按月分组的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。