gpt4 book ai didi

mysql - 我怎样才能改进这个 4 计数自连接?

转载 作者:行者123 更新时间:2023-11-29 00:07:37 24 4
gpt4 key购买 nike

假设我有以下示例数据集:

emplid | Citizenship |
100001 | USA |
100001 | CAN |
100001 | CHN |
100002 | USA |
100002 | CHN |
100003 | USA |

我想安排它在一行中显示每个员工的公民身份。我们可以假设一名员工最多拥有四种公民身份。输出看起来像这样:

emplid | Citizeship_1 | Citizenship_2 | Citizenship_3
100001 | USA | CHN | CAN
100002 | USA | CHN |
100003 | USA | |

我能够实现此目的的唯一可行解决方案:

SELECT e.emplid, MAX(e.citizenship) AS citizenship1, 
MAX(e1.citizenship) AS citizenship2,
MAX(e2.citizenship) AS citizenship3,
MAX(e3.citizenship) AS citizenship4
FROM employee e
LEFT JOIN employee e1 ON e1.emplid = e.emplid AND e1.citizenship < e.citizenship
LEFT JOIN employee e2 ON e2.emplid = e1.emplid AND e2.citizenship < e1.citizenship
LEFT JOIN employee e3 ON e3.emplid = e2.emplid AND e3.citizenship < e2.citizenship
GROUP BY e.emplid

随着数据集的增长和增长,这变得越来越低效,但我找不到重写此查询的方法。

最佳答案

为什么不直接将公民身份连接成一个列表?

select e.emplid, group_concat(citizenship) as citizenships
from employee e
group by e.emplid;

如果你想有四个单独的列,你可以这样做:

select e.emplid,
substring_index(group_concat(citizenship), ',', 1) as c1,
(case when count(*) >= 2
then substring_index(substring_index(group_concat(citizenship), ',', 2), ',', -1)
end) as c2,
(case when count(*) >= 3
then substring_index(substring_index(group_concat(citizenship), ',', 3), ',', -1)
end) as c3,
(case when count(*) >= 4
then substring_index(substring_index(group_concat(citizenship), ',', 4), ',', -1)
end) as c4
from employee e
group by e.emplid;

关于mysql - 我怎样才能改进这个 4 计数自连接?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26786560/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com