sql - 条件检查,用逗号分隔的多列的单/多值
问题描述
我有以下数据:
create table mtest
(
id1 int,
id2 int,
id3 int,
id4 int,
name varchar(20)
);
insert into mtest values(1,11,2,33,'Test1');
insert into mtest values(2,12,4,3,'Test2');
insert into mtest values(4,13,6,44,'Test3');
insert into mtest values(7,15,17,4,'Test4');
insert into mtest values(10,65,9,5,'Test5');
insert into mtest values(7,65,4,5,'Test6');
insert into mtest values(37,11,4,15,'Test7');
我想找到具有以下值的 ID:7 和 4
预期输出:
id_column value names
-------------------------------------
id3 4 Test2,Test6,Test7
id1 7 Test4,Test6
id1 4 Test3
id4 4 Test4
尝试:
select distinct id_column,value,stuff((select ','+ name from mtest b where b.id1 = a.value or b.id2 = a.value or b.id3 = a.value or b.id4 =a.value for xml path('')), 1, 1,'') names
from
(
select case when id1 in ('7','4') then 'id1'
when id2 in ('7','4') then 'id2'
when id3 in ('7','4') then 'id3'
when id4 in ('7','4') then 'id4' else ''
end as id_column,
case when id1 in ('7','4') then id1
when id2 in ('7','4') then id2
when id3 in ('7','4') then id3
when id4 in ('7','4') then id4 else ''
end as value,
name
from mtest
) a
where a.id_column <> ''
但是得到错误的结果:
id_column value names
---------------------------------------------------
id1 4 Test2,Test3,Test4,Test6,Test7
id1 7 Test4,Test6
id3 4 Test2,Test3,Test4,Test6,Test7
而且我也担心stuff
多个 or 条件的子句,因为表有超过 1000 万条记录。
解决方案
首先,您需要规范化您的数据(我的意思是修复您的数据,而不是即时进行)。然后你根据ID
和IDCol
(在我的例子中)聚合你的字符串。
WITH CTE AS(
SELECT V.ID,
V.IDCol,
M.[Name]
FROM dbo.mtest M
CROSS APPLY (VALUES(id1,'id1'),(id2,'id2'),(id3,'id3'),(id4,'id4'))V(ID,IDCol))
SELECT C.IDCol,
C.ID,
STUFF((SELECT ',' + x.[Name]
FROM CTE x
WHERE C.IDCol = x.IDCol
AND C.ID = x.ID
FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,1,'') AS Names
FROM CTE C
WHERE C.ID IN (4,7)
GROUP BY C.IDCol,
C.IDl
旁注,SQL Server 2008 的(扩展)支持还剩 7 天。你真的需要尽快查看升级路径。
推荐阅读
- c - 静态内存可以延迟分配吗?
- r - 无法将变量转换为 ggplot 的因子
- apache-flink - flink 背压监控
- ignite - 处理选择器键失败后,点燃缓存失败...java.io.IOException: Broken pipe exception
- javascript - IONIC3 推送通知在 IOS DEVICE 中不起作用
- time-complexity - 循环调整的时间复杂度
- azure-devops - 如何将 apitoken 添加为自定义 VSTS 服务端点数据源的一部分?
- java - 将表示为货币的字符串值转换为普通浮点数或整数
- laravel-5.5 - 在 laravel 5.5 中注册一个新类型的用户
- angular - 如何提取 x.509 证书并以角度 2 显示其字段