无法将字符串\u agg(列，“，”)添加到整个查询中

4xrmg8kj 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(466)

我运行的是sql server 2017，除了尝试添加以下内容外，我的查询运行得非常好：
，字符串\u agg（templateid，，'）作为结果1
这是一个最终支持ssrs报告的存储过程。
我需要templateid的结果，这将是一个逗号分隔的值列表，如“1，3，5，12，14，33，44100107”（作为一个例子），也喜欢只选择不同的，如果可能的。
当我将上述内容添加到整个存储过程中时，management studio中出现以下错误：
msg 8120，level 16，state 1，procedure sp\u changesbyserdetail，line 11[batch start line 7]column'changedetail.displayname'在选择列表中无效，因为它不包含在聚合函数或group by子句中。
这是我完整的select语句。

SELECT DisplayName, IsDeleted, TemplateID,TemplateName, Version, Name, UpdatedBy, DataTable, ID, IsVirtual,
DATEADD(minute, DATEDIFF(minute, getutcdate(), GETDATE()), AddedDate) As AddedDate,
CONVERT(varchar(8), DATEADD(minute, DATEDIFF(minute, getutcdate(), GETDATE()), AddedDate), 108)
AS AddedTime, AttachmentContent, UserName, UpdatedDate,
cast(datediff(second, '1970-01-01 00:00:00', AddedDate) as varchar ) + '000' as millisec
,STRING_AGG (TemplateID, ',') as Result1

FROM ChangeDetail  WITH (NOLOCK) 
where UpdatedBy in (select s from fn_SplitLarge(',',@In_ModifiedBy)) 
and templatename in (select s from fn_SplitLarge(',',@In_ItemType)) 

and DATEADD(minute, DATEDIFF(minute, getutcdate(), GETDATE()), AddedDate) between  @In_StartDate + @In_StartTime and @In_EndDate + @In_EndTime

思想？

sql sql-server

来源：https://stackoverflow.com/questions/62944160/cannot-add-string-aggcolumn-into-overall-query

1条答案

按热度按时间

smdncfj31#

原因是没有按字段分组。还要注意，您分组依据的字段不应该在字段列表中（和名称）
前任。

Create table #t (Id int, TemplateName varchar(100))
insert into #t values
(1,'aaa'), (1,'bbb'),(1,'ccc'),(2,'ppp'),(2,'qqq'),(3,'XXX'),(3,'YYY'),(4,'ZZZ  ')

SELECT ID, TemplateName  FROM #T

-- FOLLOWING DOESN'T WORK
-- SELECT ID, TemplateName, STRING_AGG (TemplateName, ',') as Result1   FROM #T

-- FOLLOWING WORKS
SELECT ID, STRING_AGG (TemplateName, ',') as Result1   FROM #T
GROUP BY ID

因此，您可以按以下方式进行查询：

Select DisplayName, IsDeleted, Version, Name, UpdatedBy, DataTable, ID, IsVirtual,
    AddedDate,AddedTime, AttachmentContent, UserName, UpdatedDate, millisec
    ,STRING_AGG (TemplateID, ',') as Result1 
from
(
    SELECT DisplayName, IsDeleted, TemplateID,TemplateName, Version, Name, UpdatedBy, DataTable, ID, IsVirtual,
            DATEADD(minute, DATEDIFF(minute, getutcdate(), GETDATE()), AddedDate) As AddedDate,
            CONVERT(varchar(8), DATEADD(minute, DATEDIFF(minute, getutcdate(), GETDATE()), AddedDate), 108) AS AddedTime, 
            AttachmentContent, UserName, UpdatedDate,
            cast(datediff(second, '1970-01-01 00:00:00', AddedDate) as varchar ) + '000' as millisec
    FROM ChangeDetail  WITH (NOLOCK) 
    where UpdatedBy in (select s from fn_SplitLarge(',',@In_ModifiedBy)) 
        and templatename in (select s from fn_SplitLarge(',',@In_ItemType)) 
        and DATEADD(minute, DATEDIFF(minute, getutcdate(), GETDATE()), AddedDate) 
            between  @In_StartDate + @In_StartTime and @In_EndDate + @In_EndTime
) Q
Group By DisplayName, IsDeleted, Version, Name, UpdatedBy, DataTable, ID, IsVirtual,
    AddedDate,AddedTime, AttachmentContent, UserName, UpdatedDate, millisec

赞(0）回复(0）举报 2021-07-26

我来回答

无法将字符串\u agg(列，“，”)添加到整个查询中

1条答案

相关问题

热门标签

最新问答