在 SQL 存储过程中选择和合并表中的行

问题描述

有一个带有架构的临时表:ID |序列号 |姓名

Have a temp table with schema: ID | SeqNo | Name

ID - 不是唯一的
SeqNo - Int(可以是 1,2 或 3).排序 ID+SeqNo 作为主键
名称 - 任何文本

ID - Not unique
SeqNo - Int (can be 1,2 or 3). Sort of ID+SeqNo as Primary key
Name - Any text

表中的样本数据如下

1 | 1 | RecordA  
2 | 1 | RecordB  
3 | 1 | RecordC  
1 | 2 | RecordD  
4 | 1 | RecordE  
5 | 1 | RecordF  
3 | 1 | RecordG  

需要从这个表中选择并输出像

Need to select from this table and output like

1 | RecordA/RecordD  
2 | RecordB  
3 | RecordC/RecordG  
4 | RecordE  
5 | RecordF  

需要在没有光标的情况下执行此操作.

Need to do this without cursor.

推荐答案

如果 SeqNo 限制为 1,2,3:

If SeqNo is limited to 1,2,3:

select id, a.name + coalesce('/'+b.name, '') + coalesce('/'+c.name, '')
from myTable a
left outer join myTable b on a.id=b.id and b.seqno = 2
left outer join myTable c on a.id=c.id and c.seqno = 3
where a.seqno = 1;

如果 SeqNo 是开放式的，您可以部署递归 cte:

If SeqNo is open ended you can deploy a recursive cte:

;with anchor as (
   select id, name, seqno
      from myTable
      where seqno=1)
, recursive as (
   select id, name, seqno
      from anchor
      union all
   select t.id, r.name + '/' + t.name, t.seqno
      from myTable t
      join recursive  r on t.id = r.id and r.seqno+1 = t.seqno)
select id, name from recursive;

这篇关于在 SQL 存储过程中选择和合并表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！