我该如何改善这4个计数的自我加入? [英] How can I improve this 4 count self join?

问题描述

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

Suppose I have the following sample data set:

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

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

And I want to arrange it to show the citizenships for each employee in one row. We can assume that an employee has up to four citizenships. The output would look like this:

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

我唯一能够实现这一目标的解决方案:

The only working solution I have been able to achieve this this:

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

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

As the data set grows and grows this becomes more and more inefficient, but I can't find a way to rewrite this query.

推荐答案

为什么不将公民身份串联到列表中?

Why not just concatenate the citizenships into a list?

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

如果要有四个单独的列，则可以执行以下操作:

If you want to have four separate columns, you can do something like:

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;

这篇关于我该如何改善这4个计数的自我加入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！