代码之家 › 专栏 › 技术社区 › BadHorsie

将每组的前3条记录合并为一行

excel sql mysql

BadHorsie · 技术社区 · 6 年前

一个 user qualifications Excel中的电子表格 从MySQL数据库。结果应包含每个用户的一行,并在同一行中列出他们的第一、第二和第三资格。

用户表

id  |  Name     
--------------
1   |  James
2   |  Bob
3   |  Adam
4   |  Michael

资格表

id  |  user_id  |  subject
-----------------------------------------
1   |  1        |  English
2   |  2        |  History
3   |  2        |  Mathematics
4   |  2        |  Biology
5   |  2        |  Sports Science
6   |  2        |  Art
7   |  3        |  Physics
8   |  3        |  Chemistry
10  |  4        |  Geography
11  |  4        |  Computer Science
12  |  4        |  Theology

期望结果

User ID  |  Name     |  Qualification 1  |  Qualification 2   |  Qualification 3
-----------------------------------------------------------------------
1        |  James    |  English          |  NULL              |  NULL
2        |  Bob      |  History          |  Mathematics       |  Biology
3        |  Adam     |  Physics          |  Chemistry         |  NULL
4        |  Michael  |  Geography        |  Computer Science  |  Theology

笔记

我只需要为每个用户的前3个资格。
用户可以具有任意数量的资格。他们可能没有,他们可能有10个。
在这个例子中,James只有一个资格。鲍勃有五个孩子。亚当只有两个。
这是一项一次性任务。我不需要经常这样做。

2 回复 | 直到 6 年前

BadHorsie 6 年前

最简单的方法(MySQL 8.0之前)使用变量和条件聚合:

select u.user_id, u.name,
       max(case when rn = 1 then q.subject end) as qualification_1,
       max(case when rn = 2 then q.subject end) as qualification_2,
       max(case when rn = 3 then q.subject end) as qualification_3
from users u left join
     (select q.*,
             (@rn := if(@u = q.user_id, @rn + 1,
                        if(@u := q.user_id, 1, 1)
                       )
             ) as rn
      from qualifications q,
           (select @u := -1, @rn := 0) params
      order by q.user_id, q.id
     ) q
     on u.id = q.user_id
where rn <= 3
group by u.id, u.name

Salman Arshad 6 年前

你可以用 GROUP_CONCAT SUBSTRING_INDEX 功能。这三个主题可以出现在一列中:

SELECT
    users.id,
    users.name,
    SUBSTRING_INDEX(GROUP_CONCAT(qualifications.subject SEPARATOR ','), ',', 3) AS subjects
FROM users
LEFT JOIN qualifications ON users.id = qualifications.user_id
GROUP BY users.id, users.name

或者你可以把它们分开:

SELECT
    id,
    name,
    subjects,
    SUBSTRING_INDEX(subjects, ',', 1) AS subject1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(subjects, ',', 2), ',', -1) AS subject2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(subjects, ',', 3), ',', -1) AS subject3
FROM (
    SELECT
        users.id,
        users.name,
        CONCAT(GROUP_CONCAT(qualifications.subject SEPARATOR ','), ',,') AS subjects
    FROM users
    LEFT JOIN qualifications ON users.id = qualifications.user_id
    GROUP BY users.id, users.name
) AS x