由DATEPART（）组错误/错误

Question

我遇到了由DATEPART（）子句组出现的问题。我的代码是

SELECT con.Name, con.contractID, Sum(cplanit.Cost) as costs, DATENAME(month,month(cplanit.PaymentDate)) as month, year(cplanit.PaymentDate) as year 

FROM ContractTable con 

INNER JOIN [a lot of tables] ON [joins proofed and working fine] 

Group By con.Name, con.contractID, DATEPART(month,cplanit.PaymentDate), DATEPART(year,cplanit.PaymentDate) 

我得到这个：

Adobe CLP Agreement - Initial purchase C00012121 18.7500 January 2011 
Adobe CLP Agreement - Initial purchase C00012121 18.7500 January 2010 
Adobe CLP Agreement - Initial purchase C00012121 18.7500 January 2011 
Adobe CLP Agreement - Initial purchase C00012121 18.7500 January 2010 
Adobe CLP Agreement - Initial purchase C00012121 18.7500 January 2011 

相同的条目。费用按确切的付款日期进行分组，而不是每月和每年 - 这是我需要做的。如果我在SELECT子句中删除日期名称和年份，或者仅按月或年分组，则会出现此问题。对DATEPART（），month（）或year（）进行分组工作正常。也许这是我错过的简单东西？有时我只是盲目。

在SQL Server Management Studio中/ SQL Server 2012的

Answer 1

您在GROUP不privide通过在SELECT子句提供子句领域遇到。请尝试以下操作：

SELECT con.Name, con.contractID, Sum(cplanit.Cost) as costs, DATENAME(month,month(cplanit.PaymentDate)) as month, year(cplanit.PaymentDate) as year 

FROM ContractTable con 

INNER JOIN [a lot of tables] ON [joins proofed and working fine] 

GROUP BY con.Name, con.contractID, DATENAME(month,month(cplanit.PaymentDate)), year(cplanit.PaymentDate) 

---

反正它工作在所有情况下：

CREATE TABLE #Tempas 
(
    Name NVARCHAR(80), 
    contractID NVARCHAR(20), 
    Data DATETIME2 
) 
INSERT INTO #Tempas VALUES('Adobe CLP Agreement - Initial purchase' ,'1','20010101 10:10:10') 
INSERT INTO #Tempas VALUES('Adobe CLP Agreement - Initial purchase' ,'1','20010101 10:10:10') 
INSERT INTO #Tempas VALUES('Adobe CLP Agreement - Initial purchase' ,'1','20010101 10:10:10') 
INSERT INTO #Tempas VALUES('Adobe CLP Agreement - Initial purchase' ,'1','20010101 10:10:10') 
INSERT INTO #Tempas VALUES('Adobe CLP Agreement - Initial purchase' ,'1','20020101 10:10:10') 
INSERT INTO #Tempas VALUES('Adobe CLP Agreement - Initial purchase' ,'1','20020101 10:10:10') 
INSERT INTO #Tempas VALUES('Adobe CLP Agreement - Initial purchase' ,'1','20020101 10:10:10') 
INSERT INTO #Tempas VALUES('Adobe CLP Agreement - Initial purchase' ,'1','20020101 10:10:10') 

SELECT Name, contractID, DATENAME(month,month(data)) AS Month, year(data) AS year 
FROM #Tempas 
GROUP BY Name, contractID, DATEPART(month,data), DATEPART(year,data) 

DROP TABLE #tempas 

OUTPUT：

   Name    contractID Month year 
Adobe CLP Agreement - Initial purchase 1 January 2001 
Adobe CLP Agreement - Initial purchase 1 January 2002 

---

在相同的价值观和下面的查询：

SELECT Name, contractID, DATENAME(month,month(data)) AS Month, year(data) AS year 
FROM #Tempas 
GROUP BY Name, contractID, DATENAME(month,month(data)), year(data) 

输出相同：

   Name    contractID Month year 
Adobe CLP Agreement - Initial purchase 1 January 2001 
Adobe CLP Agreement - Initial purchase 1 January 2002 

Answer 2

的代码似乎很好地工作，因为它是，即使by子句当前组。问题是否可以在查询的另一部分？

declare @ContractTable table (
Name nvarchar(20), 
ContractId int, 
Cost Decimal, 
PaymentDate DateTime) 

insert into @ContractTable values ('Name', 1, 100, N'20150101') 
insert into @ContractTable values ('Name', 2, 100, N'20150101') 
insert into @ContractTable values ('Name', 1, 100, N'20150102') 
insert into @ContractTable values ('Name', 1, 100, N'20150102') 
insert into @ContractTable values ('Name', 2, 100, N'20150104') 
insert into @ContractTable values ('Name', 2, 100, N'20150104') 
insert into @ContractTable values ('Name', 2, 100, N'20150105') 
insert into @ContractTable values ('Name', 2, 100, N'20150105') 
insert into @ContractTable values ('Name', 2, 100, N'20150105') 
insert into @ContractTable values ('Name', 2, 100, N'20150105') 

SELECT 
    Name 
    ,contractID 
    ,Sum(Cost) as costs 
    ,DATENAME(month,month(PaymentDate)) as [month] 
    ,year(PaymentDate) as [year] 
FROM 
    @ContractTable 
Group By 
    Name 
    ,contractID 
    ,DATEPART(month,PaymentDate) 
    ,DATEPART(year, PaymentDate)