具有不同字符的 SQL Server 组连接

本文介绍了具有不同字符的 SQL Server 组连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我查看了许多在 SQL Server 中模拟Group concat"功能的解决方案.不过，我想制定一个更易读的解决方案，但我不知道该怎么做.

I have looked through a number of solutions to emulating "Group concat" functionality in SQL Server. I wanted to make a more human readable solution though and I can't work out how to do it.

我有一个观点:

ParentID | ChildName

其中包含记录，例如:

1 | Max
1 | Jessie
2 | Steven
2 | Lucy
2 | Jake
3 | Mark

我想对这些进行分组连接"以获得:

I want to "Group Concat" these to get:

1 | Max and Jessie
2 | Steven, Lucy and Jake
3 | Mark

因此，如果只有 1 个孩子，只需返回姓名，如果有多个，则将最后 2 个与 ' 和 ' 连接起来，其他所有用 ', ' 连接.

So If there is only 1 child, just return name, if there are more than one, concat the last 2 with an ' and ' and all others with a ', '.

我对如何在不求助于 CLR 的情况下做到这一点感到有些困惑，我不想这样做.我对一个功能很满意 - 但速度是个问题，我如何确定子编号以便我可以在和"、、"或"之间进行选择?

I am a bit stuck on how to do this without resorting to CLR, which I don't want to do. I am happy with a function - but speed is an issue and how do I determine the child number so I can choose between ' and ', ', ' or ''?

推荐答案

抱歉，这是我对您的要求所能做的最好的事情.

Sorry, this is the best I can do with your requirement.

SQL 小提琴

MS SQL Server 2008 架构设置:

create table YourTable
(
  ParentID int,
  ChildName varchar(10)
);

insert into YourTable values
(1, 'Max'),
(1, 'Jessie'),
(2, 'Steven'),
(2, 'Lucy'),
(2, 'Jake'),
(3, 'Mark');

查询 1:

with T as
(
  select ParentID,
         ChildName,
         row_number() over(partition by ParentID order by ChildName) as rn,
         count(*) over(partition by ParentID) as cc
  from YourTable
)
select T1.ParentID,
       (
         select case
                  when T2.rn = 1 and T2.cc > 1 then ' and '
                  else ', '
                end + T2.ChildName
         from T as T2
         where T1.ParentID = T2.ParentID
         order by T2.rn desc
         for xml path(''), type
       ).value('substring(text()[1], 3)', 'varchar(max)') as ChildNames
from T as T1
group by T1.ParentID

结果:

| PARENTID |            CHILDNAMES |
------------------------------------
|        1 |        Max and Jessie |
|        2 | Steven, Lucy and Jake |
|        3 |                  Mark |

这篇关于具有不同字符的 SQL Server 组连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

clr