MySQL:选择，分组和将行转换为单独的列:) [英] MySQL: select, group by and transform rows to separate columns :)
本文介绍了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，源表:
员工:
After several hours of unsuccessful searches, I gave up.
I'll be very grateful for some clues is it possible to achieve something like this (one select query would be the best option for me).
Thank you in advance.
Answering to emsoff, the source tables:
employees:
+----+---------+
| 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
推荐答案
您的数据有些难以处理.
Your data are somewhat difficult to handle.
要创建一个枢轴表，列必须是唯一的，甚至更多，这样五个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 | | |
这篇关于MySQL:选择，分组和将行转换为单独的列:)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
