时间戳

首页 > 解决方案 > MySQL：选择、分组和将行转换为单独的列:)

mysql - MySQL：选择、分组和将行转换为单独的列:)

问题描述

我需要向您寻求有关 MySQL 选择查询的帮助。
具体例子：有配偶和孩子的员工。
我已经将 2 个表合并为一个，现在我需要：
1，选择按“emp”字段分组的数据
2，使用以下规则转换结果：

该表（实际上是两个连接表）：

+---------+-----------+-----------+------------+
| emp     | relation  | relative  | birthdate  |
+---------+-----------+-----------+------------+
| emp-A   | spouse    | spouse-A  | 1970-xx-xx |
| emp-A   | kid       | kid-A1    | 1971-xx-xx |
| emp-A   | kid       | kid-A2    | 1972-xx-xx |
| emp-A   | kid       | kid-A3    | 1973-xx-xx |
| emp-B   | spouse    | spouse-B  | 1980-xx-xx |
| emp-B   | kid       | kid-B1    | 1981-xx-xx |
| emp-B   | kid       | kid-B2    | 1982-xx-xx |
| emp-C   | kid       | kid-C1    | 1991-xx-xx |
| emp-C   | kid       | kid-C2    | 1992-xx-xx |
+---------+-----------+-----------+------------+

期望的结果：

+---------+-----------+-------------+-----------+-------------+-----------+-------------+-----------+-------------+
| emp     | spouse    | birthdate   | kid1      | birthdate1  | kid2      | birthdate2  | kid3      | birthdate3  |
+---------+-----------+-------------+-----------+-------------+-----------+-------------+-----------+-------------+
| emp-A   | spouse-A  | 1970-xx-xx  | kid-A1    | 1971-xx-xx  | kid-A2    | 1972-xx-xx  | kid-A3    | 1973-xx-xx  |
| emp-B   | spouse-B  | 1980-xx-xx  | kid-B1    | 1981-xx-xx  | kid-B2    | 1982-xx-xx  |           |             |
| emp-C   |           |             | kid-C1    | 1991-xx-xx  | kid-C2    | 1992-xx-xx  |           |             |
+---------+-----------+-------------+-----------+-------------+-----------+-------------+-----------+-------------+


经过几个小时不成功的搜索，我放弃了。
我将非常感谢一些线索是否有可能实现这样的目标（一个选择查询对我来说是最好的选择）。
先感谢您。

回答 emsoff，源表：
员工：

+----+---------+
| id | emp     |
+----|---------+
|  1 | emp-A   |
|  2 | emp-B   |
|  3 | emp-C   |
+----|---------+

亲戚们：

+----+---------+-----------+-----------+------------+
| id | emp_id  | relation  | relative  | birthdate  |
+----+---------+-----------+-----------+------------+
|  1 |       1 | spouse    | spouse-A  | 1970-xx-xx |
|  2 |       1 | kid       | kid-A1    | 1971-xx-xx |
|  3 |       1 | kid       | kid-A2    | 1972-xx-xx |
|  4 |       1 | kid       | kid-A3    | 1973-xx-xx |
|  5 |       2 | spouse    | spouse-B  | 1980-xx-xx |
|  6 |       2 | kid       | kid-B1    | 1981-xx-xx |
|  7 |       2 | kid       | kid-B2    | 1982-xx-xx |
|  8 |       3 | kid       | kid-C1    | 1991-xx-xx |
|  9 |       3 | kid       | kid-C2    | 1992-xx-xx |
+----|---------+-----------+-----------+------------+

与 employees.id=relatives.emp_id 连接的表

标签： mysqlselectgroup-bypivottransform

解决方案

您的数据有些难以处理。

要制作数据透视表，列必须是唯一的，甚至更多，也可能出现五个 kis。

它非常难看，它适用于 myslq 5.7

架构（MySQL v5.7）

CREATE TABLE employees (
  `id` INTEGER,
  `emp` VARCHAR(5)
);

INSERT INTO employees
  (`id`, `emp`)
VALUES
  ('1', 'emp-A'),
  ('2', 'emp-B'),
  ('3', 'emp-C');

CREATE TABLE relatives (
  `id` INTEGER,
  `emp_id` INTEGER,
  `relation` VARCHAR(6),
  `relative` VARCHAR(8),
  `birthdate` DATE
);

INSERT INTO relatives
  (`id`, `emp_id`, `relation`, `relative`, `birthdate`)
VALUES
  ('1', '1', 'spouse', 'spouse-A', '1970-01-01'),
  ('2', '1', 'kid', 'kid-A1', '1971-01-02'),
  ('3', '1', 'kid', 'kid-A2', '1972-01-01'),
  ('4', '1', 'kid', 'kid-A3', '1973-01-01'),
  ('5', '2', 'spouse', 'spouse-B', '1980-02-01'),
  ('6', '2', 'kid', 'kid-B1', '1981-02-01'),
  ('7', '2', 'kid', 'kid-B2', '1982-02-01'),
  ('8', '3', 'kid', 'kid-C1', '1991-03-01'),
  ('9', '3', 'kid', 'kid-C2', '1992-03-01');

查询 #1

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when `relation` = "',
     `relation`,
      '" then `relation` end) AS `',
      relation, '`',
      ',MAX(case when `relation` = "',
     `relation`,
      '" then `birthdate` end) AS `',
      'birthdate_',relation, '`'      
    )
    ORDER BY rownumber
  ) INTO @sql
FROM
  (SELECT 
    `relation`
    ,rownumber
FROM 
    employees e
    INNER JOIN 
    (SELECT
    IF(`relation` = 'kid',IF (@empid = `emp_id`,@rn:= @rn+1,@rn:= 1),IF (@empid = `emp_id`,@rn:= @rn,@rn:= 0)) rownumber
     ,IF(`relation` = 'kid',CONCAT(`relation`,@rn),`relation`) relation
    , `relative`
    , `birthdate`
    ,@empid := `emp_id` emp_id
FROM
    (SELECT 
        * 
    FROM 
        relatives
    ORDER BY `emp_id`,FIELD(`relation`,"spouse","kid"),`birthdate`) rel
    ,(SELECT @empid := 0) a1
    ,(SELECT @rn := 0) a2) r ON e.id = r.emp_id) t1
    ;

SET @sql = CONCAT("SELECT MIN(`emp`), ", @sql, " 
                  FROM   (SELECT 
                            `emp_id`,
                            `emp` ,
                            `relation`
                            ,rownumber
                            , `relative`
                            , `birthdate`
                        FROM 
                            employees e
                            INNER JOIN 
                            (SELECT
                            IF(`relation` = 'kid',IF (@empid = `emp_id`,@rn:= @rn+1,@rn:= 1),IF (@empid = `emp_id`,@rn:= @rn,@rn:= 0)) rownumber
                             ,IF(`relation` = 'kid',CONCAT(`relation`,@rn),`relation`) 'relation'
                            , `relative`
                            , `birthdate`
                            ,@empid := `emp_id` 'emp_id'
                        FROM
                            (SELECT 
                                * 
                            FROM 
                                relatives
                            ORDER BY `emp_id`,FIELD(`relation`,'spouse','kid'),`birthdate`) rel
                            ,(SELECT @empid := 0) a1
                            ,(SELECT @rn := 0) a2) r ON e.id = r.emp_id) t1 
                   GROUP BY `emp_id`");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

| MIN(`emp`) | spouse | birthdate_spouse | kid1 | birthdate_kid1 | kid2 | birthdate_kid2 | kid3 | birthdate_kid3 |
| ---------- | ------ | ---------------- | ---- | -------------- | ---- | -------------- | ---- | -------------- |
| emp-A      | spouse | 1970-01-01       | kid1 | 1971-01-02     | kid2 | 1972-01-01     | kid3 | 1973-01-01     |
| emp-B      | spouse | 1980-02-01       | kid1 | 1981-02-01     | kid2 | 1982-02-01     |      |                |
| emp-C      |        |                  | kid1 | 1991-03-01     | kid2 | 1992-03-01     |      |                |

在 DB Fiddle 上查看

推荐阅读