SQL Server:分组时聚合错误

本文介绍了SQL Server:分组时聚合错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个称为任务的表，该表列出了工作人员可以完成的不同任务.然后，我有一个关系表，该表将已完成的任务链接到工作人员.我正在尝试编写查询，该查询根据工作人员ID将任务分组到一个列表中，但是查询给了我以下错误(请参阅下文).

I have a table called tasks, which lists different tasks a worker can complete. Then i have a relationship table that links a completed task to a worker. I'm trying to write query that groups the tasks into a list based on the worker id, but the query gives me the following error (see below).

Column 'mater.dbo.worker_task_completion.FK_task_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

表格

CREATE TABLE [dbo].[tasks] ( [task_id] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) NOT NULL, [icon] [nvarchar](max) NULL, [isActive] [int] NOT NULL, [time] [int] NOT NULL, CONSTRAINT [PK_tasks] PRIMARY KEY CLUSTERED ( [task_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] CREATE TABLE [dbo].[worker_task_completion] ( [FK_worker_id] [int] NOT NULL, [FK_task_id] [int] NOT NULL, [update_date] [datetime] NOT NULL, CONSTRAINT [PK_worker_task_completion] PRIMARY KEY CLUSTERED ( [FK_worker_id] ASC, [FK_task_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

查询

SELECT top 100 wtc.FK_worker_id, tasks = Stuff((SELECT ', ' + dbo.tasks.NAME FROM dbo.tasks WHERE dbo.tasks.task_id = wtc.FK_task_id FOR xml path ('')), 1, 1, '') FROM dbo.worker_task_completion AS wtc LEFT JOIN dbo.tasks AS tc ON tc.task_id = wtc.fk_task_id -- WHERE wtc.FK_worker_id IN () GROUP BY wtc.FK_worker_id

推荐答案

嗯.您不能将非聚合列用于相关性子句.解决方案是将 JOIN 移到子查询中:

Hmmm. You cannot use a non-aggregated column for the correlation clause. The solution is to move the JOIN into the subquery:

SELECT top 100 wtc.FK_worker_id, stuff((SELECT ', ' + t.NAME FROM dbo.worker_task_completion wtc2 JOIN dbo.tasks t ON t.task_id = wtc2.FK_task_id WHERE wtc2.FK_worker_id = wtc.FK_worker_id FOR xml path ('') ), 1, 2, '' ) as tasks FROM (SELECT DISTINCT wtc.FK_worker_id FROM dbo.worker_task_completion wtc ) wtc -- WHERE wtc.FK_worker_id IN ()

请注意，我更改了 STUFF()的第二个参数.大概是要删除空格和逗号.

Note that I changed the second argument for STUFF(). Presumably, you want to remove the space as well as the comma.