SQL Server:分组时聚合错误 [英] SQL Server: aggregate error on grouping
问题描述
我有一个称为任务的表,该表列出了工作人员可以完成的不同任务.然后,我有一个关系表,该表将已完成的任务链接到工作人员.我正在尝试编写查询,该查询根据工作人员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.
这篇关于SQL Server:分组时聚合错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!