MySQL DDL:
CREATE TABLE `employee` (
`c_id` int(11) DEFAULT NULL,
`c_name` varchar(20) DEFAULT NULL,
`t_money` int(11) DEFAULT NULL,
`d_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO test.employee
(c_id, c_name, t_money, d_id)
VALUES(1, 'Joe', 70000, 1);
INSERT INTO test.employee
(c_id, c_name, t_money, d_id)
VALUES(2, 'Jim', 90000, 1);
INSERT INTO test.employee
(c_id, c_name, t_money, d_id)
VALUES(3, 'Henry', 80000, 2);
INSERT INTO test.employee
(c_id, c_name, t_money, d_id)
VALUES(4, 'Sam', 60000, 2);
INSERT INTO test.employee
(c_id, c_name, t_money, d_id)
VALUES(5, 'Max', 90000, 1);
题1
是找出相同d_id下,最大的c_id:
SELECT SUBSTRING_INDEX(GROUP_CONCAT(a.c_id order by a.c_id desc ),',',1) c_id,a.d_id
FROM test.employee a group by a.d_id
解释:
1-GROUP_CONCAT 函数是分组后拼接函数 比如:
SELECT GROUP_CONCAT(a.c_id order by a.c_id desc ) c_id,a.d_id
FROM test.employee a group by a.d_id
得到数据为:
2-SUBSTRING_INDEX函数:字符串截取函数,语法为:
- substring_index(str,delim,count)
- str:要处理的字符串
- delim:分隔符
- count:计数
所以我们截取第一个就可以得到最终的答案: