查询以逗号分隔的ID为逗号分隔的值 [英] Query for comma-separated ids to comma-separated values
本文介绍了查询以逗号分隔的ID为逗号分隔的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2张桌子
部门
ID Dept
---------
1 HR
2 Accts
3 IT
员工
ID Name Depts
-------------------
1 Kevin 2,1
2 Michelle 1
3 Troy 1,3
4 Rheesa 2,3,1
我正在寻找带有以下SQL查询的输出.
I am looking for an output like the following with a SQL query.
员工部门
ID Name Depts
-------------------------
1 Kevin Accts,HR
2 Michelle HR
3 Troy HR,IT
4 Rheesa Accts,IT,HR
我尝试了以下将s与dept连接起来的方法,但是仅对每个dept产生一行.如何使用查询获得以上结果?
I have tried the following that join s with depts but results in one row for each dept only. How do i get the above results using a query?
select
name, depts, dept
from
employee
CROSS APPLY
dbo.split_list(employee.depts ,',') split
inner join
dbo.department on depts= split.value
order by
name
推荐答案
DECLARE @Departments TABLE
(
ID INT PRIMARY KEY,
Dept VARCHAR(32) NOT NULL UNIQUE
);
DECLARE @Employees TABLE
(
ID INT PRIMARY KEY,
Name NVARCHAR(64) NOT NULL,
Depts VARCHAR(255) NOT NULL
);
INSERT @Departments VALUES
(1,'HR'), (2,'Accts'), (3,'IT');
INSERT @Employees VALUES
(1,'Kevin','2,1'), (2,'Michelle','1'),
(3,'Troy','1,3'), (4,'Rheesa','2,3,1');
SELECT ID, Name, Depts = STUFF((SELECT ',' + d.Dept
FROM @Departments AS d
INNER JOIN @Employees AS ei
ON ',' + ei.Depts + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.id) + ',%'
WHERE ei.ID = e.ID
ORDER BY Dept
FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM @Employees AS e
ORDER BY ID;
结果与您要求的结果不太匹配,因为排序是确定性的(按部门名称排序):
The results don't quite match your required results, as the ordering is deterministic (ordered by department name):
ID Name Depts
---- -------- ----
1 Kevin Accts,HR
2 Michelle HR
3 Troy HR,IT
4 Rheesa Accts,HR,IT
如果您希望它们按照逗号分隔列表中的出现顺序进行排序,只需更改:
If you want them ordered by the appearance in the comma-separated list, just change:
ORDER BY Dept
收件人:
ORDER BY CHARINDEX( ',' + CONVERT(VARCHAR(12), d.id) + ',', ',' + ei.Depts + ',')
结果:
ID Name Depts
---- -------- ----
1 Kevin Accts,HR
2 Michelle HR
3 Troy HR,IT
4 Rheesa Accts,IT,HR -- this is the only one affected as it turns out
但是,实际上,您应该 规范化数据库 .这绝对是一场噩梦.
However, in reality, you should normalize your database. This is an absolute nightmare.
这篇关于查询以逗号分隔的ID为逗号分隔的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文