按照dept_no进行汇总

dept_no	employees
d001	10001,10002
d002	10006
d003	10005
d004	10003,10004
d005	10007,10008,10010
d006	10009,10010

示例1

输入:

drop table if exists  `dept_emp` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');

输出:

d001|10001,10002
d002|10006
d003|10005
d004|10003,10004
d005|10007,10008,10010
d006|10009,10010

原站题解

Sqlite 解法, 执行用时: 9ms, 内存消耗: 3304KB, 提交时间: 2021-06-06

select dept_no,group_concat(emp_no) as employees
from dept_emp
group by dept_no;

Sqlite 解法, 执行用时: 9ms, 内存消耗: 3304KB, 提交时间: 2021-06-03

select 
dept_no
, group_concat(emp_no,',')
from dept_emp
group by dept_no

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3192KB, 提交时间: 2020-10-30

select a.dept_no, group_concat(a.emp_no) from dept_emp a
group by a.dept_no;

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3264KB, 提交时间: 2021-08-07

select dept_no, group_concat(emp_no) as employees from dept_emp group by dept_no;

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3296KB, 提交时间: 2021-04-04

select a.dept_no, group_concat(a.emp_no) 
from dept_emp as a
group by a.dept_no

详情