SQL中每条记录的组和计数值[重复]

This question already has an answer here:

这个问题在这里已有答案:

How to use GROUP BY to concatenate strings in SQL Server? 16 answers

如何使用GROUP BY连接SQL Server中的字符串? 16个答案

Im trying to run a select statement to group records having similar IDs but also tally the values from another column for each master ID. So for example below. The result for each line will be the first instance unique ID and the 2 names shown from each record separated by semi colon. Thanks in advance.

我试图运行一个select语句来分组具有相似ID的记录,但也计算每个主ID的另一列的值。所以例如下面。每行的结果将是第一个实例唯一ID,并且每个记录显示的2个名称由分号分隔。提前致谢。

Current set

ID                               Name              Cnt
-------------------------------- ----------------- ---
0001D72BA5F664BE129B6AB5744E2BE0 Talati, Shilpa    1
0001D72BA5F664BE129B6AB5744E2BE0 Weaver, Larry     1
0007EAB7CE9A3F2F95D2D63D0BBD08A9 St-Hilaire, Edith 1
0007EAB7CE9A3F2F95D2D63D0BBD08A9 Talati, Shilpa    1

Result:

0001D72BA5F664BE129B6AB5744E2BE0 Talati, Shilpa; Weaver, Larry

3 个解决方案

#1

The easiest way to solve this in SQL Server is:

在SQL Server中解决此问题的最简单方法是:

select masterId, min(name) + '; ' + max(name)
from table t
group by masterId;

#2

Here's one way using a recursive common table expression. Given a table like this:

这是使用递归公用表表达式的一种方法。给出这样的表格:

create table dbo.Fizzbuzz
(
  id       int         not null identity(1,1) primary key clustered ,
  group_id int         not null ,
  name     varchar(50) not null ,
  cnt      int         not null ,
)

containing this data

包含这些数据

id group_id name   cnt
-- -------- ------ ---
1  1        Bob      3
2  1        Carol    5
3  1        Ted      6
4  1        Alice   16
5  2        Harold  72
6  2        Maude   28

This query

with recursive_cte as
(
  select group_id = t.group_id ,
         row      = t.row      ,
         name     = convert(varchar(8000),t.name) ,
         cnt      = t.cnt
  from ( select * ,
                row = row_number() over (
                        partition by group_id
                        order by id
                        )
         from dbo.Fizzbuzz
       ) t
  where t.row = 1
  UNION ALL
  select group_id = prv.group_id ,
         row      = nxt.row      ,
         name     = convert(varchar(8000), prv.name + ' and ' + nxt.name ) ,
         cnt      = prv.cnt  + nxt.cnt
  from recursive_cte prv
  join ( select * ,
                row = row_number() over (
                        partition by group_id
                        order by id
                        )
         from dbo.Fizzbuzz
       ) nxt on nxt.group_id = prv.group_id
            and nxt.row      = prv.row + 1
)
select group_id = t.group_id ,
       total    = t.cnt      ,
       names    = t.name
from ( select * ,
              rank = rank() over (
                       partition by group_id
                       order by row desc
                     )
       from recursive_cte
     ) t
where rank = 1
order by group_id

produces the following output

产生以下输出

group_id cnt name
-------- --- -------------------------------
    1     30 Bob and Carol and Ted and Alice
    2    100 Harold and Maude

One should note however, that the depth of recursion is bounded in SQL Server.

但是应该注意,递归的深度在SQL Server中是有限的。

#3

SELECT  
      t1.ID, 
      (SELECT Name + '; ' 
       FROM yourtable t2 
       WHERE t1.ID = t2.ID 
       for xml path('')) as Name
FROM yourtable t1 
GROUP BY t1.ID

#1