如何在一个查询中连接一列中多行的字符串+内部连接 [英] How to concatenate strings from multiple rows in one column + inner join in one query

问题描述

我有一个查询，结果如下:查询:

I have a query with the following result: query:

SELECT Tasks.TaskId, Comments.Comment, comments.timespent       
FROM   comments
INNER JOIN tasks ON comments.entityid = tasks.taskid                
WHERE  ( comments.entity = 1 ) 
GROUP  BY Tasks.TaskId, Comments.Comment, comments.timespent

结果:

TaskID  Comment  TimeSpent
__________________________
111754    C1        4
111754    C2        1
111754    C3       79

请告诉我应该如何编写查询以获得如下结果:

Please tell me how should I write my query to get the result as follows:

TaskID  Comment          TimeSpent
__________________________________
111754  ,C1,C2,C3           84

提前致谢.

推荐答案

这是工作的 SQL Fiddle:http://sqlfiddle.com/#!3/3597a/3

Here's the working SQL Fiddle: http://sqlfiddle.com/#!3/3597a/3

这是实际工作的 SQL.

Here's the actual working SQL.

SELECT Tasks.TaskId, SUBSTRING(
(SELECT ',' + Comments.Comment
FROM Comments
INNER JOIN tasks ON comments.entityid = tasks.taskid
FOR XML PATH('')),2,200000) AS Comments
, SUM(comments.timespent) AS TimeSpent      
FROM   comments
INNER JOIN tasks ON comments.entityid = tasks.taskid                
WHERE  ( comments.entity = 1 ) 
GROUP  BY Tasks.TaskId

创建表并填充数据

CREATE TABLE Tasks
(
  TaskID NVARCHAR(20) NOT NULL,
);

CREATE TABLE Comments
( 
  Entity  INT NOT NULL,
  EntityID NVARCHAR(20) NOT NULL,
  Comment NVARCHAR(50) NOT NULL,
  TimeSpent INT NOT NULL
);


INSERT INTO Tasks VALUES
( '111754' );

INSERT INTO Comments VALUES
(1,'111754', 'C1',4 ),
(1,'111754', 'C2',1 ),
(1,'111754', 'C3',79 );

执行 SQL

SELECT Tasks.TaskId, SUBSTRING(
(SELECT ',' + Comments.Comment
FROM Comments
INNER JOIN tasks ON comments.entityid = tasks.taskid
FOR XML PATH('')),2,200000) AS Comments
, SUM(comments.timespent) AS TimeSpent     
FROM   comments
INNER JOIN tasks ON comments.entityid = tasks.taskid                
WHERE  comments.entity = 1 
GROUP  BY Tasks.TaskId

查看结果.

TASKID  COMMENTS    TIMESPENT
111754  C1,C2,C3    84

这篇关于如何在一个查询中连接一列中多行的字符串+内部连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！