我需要一种在MySQL中创建视图的解决方案。
我有两个数据表。一个有客户名称列表。
表格:name_list
编号名称
------------------
12345史蒂夫
23456米克
罗伯特34567
45678约翰
56789泰勒
67890史蒂文
23234肯
56746哈里
另一个有客户群。
表:customer_group
集团客户
----------------------
A5213 12345,34567,56789
B5314 23234
D5486 23456,45678,67890,56746
现在,我需要通过从customer_group表中拆分逗号分隔的客户ID并从name_list表中拆分这些客户的名称来显示这些数据。
查看:customer_name_result
组Cust1名称1 Cust2名称2 Cust3名称3 Cust4名称4
-------------------------------------------------- --------------------
A5213 12345史蒂夫34567罗伯特56789泰勒空空
B5314 23234肯NULL NULL NULL NULL NULL NULL NULL
D5486 23456米克45678约翰67890史蒂文56746哈里
在此先感谢您的帮助。
最佳答案
您可以按以下方式使用数据透视:
;with cte as (
select [Group], [Value], nl.[name], RowN = Row_Number() over(partition by [Group] order by [Value])
,RowN1 = Row_Number() over(partition by [Group] order by [Value]) +1000
from customer_group cg
cross apply
(
Select * from string_split(customers,',')
) a
inner join name_list nl
on a.value = nl.id
)
select [Group], max([1001]) as [Name1], max([1]) as [Val1]
, max([1002]) as [Name2], max([2]) as [Val1]
, max([1003]) as [Name3], max([3]) as [Val1]
, max([1004]) as [Name4], max([4]) as [Val1]
from
(select * from cte ) b
pivot (max([value]) for RowN in([1],[2],[3],[4])) p
pivot (max([name]) for RowN1 in ([1001],[1002],[1003],[1004])) p1
group by [Group]
回答如下:
+-------+--------+-------+--------+-------+--------+-------+--------+-------+
| Group | Name1 | Val1 | Name2 | Val1 | Name3 | Val1 | Name4 | Val1 |
+-------+--------+-------+--------+-------+--------+-------+--------+-------+
| A5213 | Steve | 12345 | Robert | 34567 | Taylor | 56789 | NULL | NULL |
| B5314 | Ken | 23234 | NULL | NULL | NULL | NULL | NULL | NULL |
| D5486 | Mick | 23456 | John | 45678 | Harry | 56746 | Steven | 67890 |
+-------+--------+-------+--------+-------+--------+-------+--------+-------+
关于mysql - 如何将单列逗号分隔的值拆分为多列,并在MySQL的另一个表中将其左连接到每个单元格?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44397328/