MySQL:选择、分组和将行转换为单独的列:)-数据库问题

MySQL: select, group by and transform rows to separate columns :)(MySQL:选择、分组和将行转换为单独的列:))

本文介绍了MySQL:选择、分组和将行转换为单独的列:)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I need to ask you for help with MySQL select query.
Specific example: employees with the spouse and kids.
I have 2 tables already joined into one and now I need to:
1, select the data with grouping them by 'emp' field
2, transform the result with these rules:

只有一行具有特定的 emp(emp-A、emp-B、emp-C)
后续列中的每个亲属(配偶和孩子)(先是配偶，然后是孩子)

表格(实际上是两个连接的表格):

+---------+-----------+-----------+------------+
| 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 联接的表

Tables joined with employees.id=relatives.emp_id

推荐答案

你的数据有点难处理.

要制作数据透视表，列必须是唯一的，甚至还必须有五个 kis.

To maek a pivot table the columsn have to be uniquie, even more, that also five kis could apperar.

它很丑，它适用于 myslq 5.7

It is quite ugly and it works withmyslq 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

这篇关于MySQL:选择、分组和将行转换为单独的列:)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！