SQL技巧->合并单选到多选 , 并用逗号分隔 , 除了stuff函数之外的方法
分享一个工作中发现的小技巧 , PS : 把一个简单的SQL问题讲明白也不容易啊, 要自己造数据
需求 : 每只债券有多个特征值type , 每个特征值可能在不同的表中查询并实现 , 写出一个包含所有特征值的字段 , 特征之间用逗号分隔
最麻烦的是这个逗号的处理
常用的SQL写法
1 drop table if exists #a -- 临时表只是为了生成测试数据 说明问题 2 3 select * into #a from( 4 select symbol='001',exchange='sh',type='a' 5 union all 6 select symbol='002',exchange='sz',type='b' 7 union all 8 select symbol='001',exchange='sh',type='c' 9 ) a 10 11 select symbol,exchange, 12 remark=stuff((select ',' + type from #a where symbol=a.symbol and a.exchange=exchange for xml path('')),1,1,'') --这里是关键 13 from #a a 14 group by symbol,exchange -- 为了去重 15 16 drop table #a
涉及十分复杂的type时 , 用逗号隔开一般是用stuff函数处理 ,比较容易
如果提取type的逻辑不是过于复杂 , 可以用下面的这段比较巧妙的逻辑解决
1 select symbol,exchange, 2 remark =case when left(remark,1)=',' then right(remark,len(remark)-1) else remark end -- 去掉第一个可能存在的逗号 3 from 4 ( 5 select a.symbol,a.exchange, 6 remark=case when a.exchange='sh' then 'a' else '' end 7 +case when a.exchange='sz' then ',b' else '' end -- 这里一定要加逗号 8 +case when b.symbol is not null then ',c' else '' end -- 这里一定要加逗号 9 from 10 ( 11 select symbol='001',exchange='sh' 12 union all 13 select symbol='002',exchange='sz' 14 ) a 15 left join -- 这个left join 不是必要的 这里只是为了配合symbol is not null解决复杂一点的问题 16 ( 17 select symbol='001',exchange='sh' where 1=1 -- 这里一般会有个复杂的where条件 18 ) b on a.symbol=b.symbol and a.exchange=b.exchange 19 ) a
这里最重要的地方是第6行开始的remark写法 , 用case when的方式解决了逗号的问题
注意除了第一行开始 , 剩余的每一个特征值都要在前面加上逗号 ,比如 ",b"",c"而不是"b""c"
第一行也可以加逗号 因为外面那一层的remark重写的时候会去掉第一个可能出现的逗号
如果你确定必然一个case when的结果不为null , 那么可以省略外面那一层嵌套的select语句 , 因为不会出现第一个是逗号的情况了
最最重要的一点 : 每一段case when的结尾都要是 else '' 不能缺少
因为不加的话 默认是null null值与任何字符用+连接结果都是null
结果如下图
最后一句 : 上面两种方法并不是对立的 , 可以把两种方法联合在一起用 , 用于拼接remark
谢谢!