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
相关文章