我需要一种在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/

10-16 15:10
查看更多