11、用 left join 代替 not in 求:a有b没有,提高执行效率。
SELECT a.car
FROM idc_temp.temp_car_ly1 a
LEFT JOIN idc.dw_zy_car_status b
ON a.car = b.car_code
WHERE b.car_code is NULL;
-- 代替:
SELECT b.car
FROM idc_temp.temp_car_ly1 b
WHERE b.car NOT IN (
SELECT a.car_code
FROM idc.dw_zy_car_status a
);
12、找出重复字段:
SELECT car, COUNT(*) as count
FROM tb_liny_car
GROUP BY car
HAVING count>1;
13、求每个分组中最大/最新的一条记录
先建立测试数据:
CREATE TABLE `tb_temp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`val` int(11) DEFAULT NULL,
`memo` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
);
insert into tb_temp values(null, 'a', 2, 'a2');
insert into tb_temp values(null, 'a', 1, 'a1');
insert into tb_temp values(null, 'a', 3, 'a3');
insert into tb_temp values(null, 'b', 1, 'b1');
insert into tb_temp values(null, 'b', 3, 'b3');
insert into tb_temp values(null, 'b', 2, 'b2');
insert into tb_temp values(null, 'b', 4, 'b4');
insert into tb_temp values(null, 'b', 5, 'b5');
insert into tb_temp values(null, 'c', 10, 'c10');
insert into tb_temp values(null, 'c', 11, 'c11');
insert into tb_temp values(null, 'c', 15, 'c15');
SELECT * FROM tb_temp;
按name分组取val最大的值所在行的数据:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name;
或
select a.* from tb a, (select name,max(val) val from tb group by name) b
where a.name = b.name and a.val = b.val order by a.name;
按name分组取val最小的前2个:
SELECT a.* FROM tb_temp a
WHERE 2 > (
SELECT COUNT(*) FROM tb_temp WHERE name=a.name AND val < a.val
)
14、分组取某个值最大的前2条记录
SELECT a.* FROM tb_temp a
WHERE 2 > (
SELECT COUNT(*) FROM tb_temp WHERE name=a.name AND val > a.val
) ORDER BY a.name,a.val
;
SELECT a.id,a.`name`,a.val,a.memo
FROM tb_temp a
LEFT JOIN tb_temp b
ON a.`name`=b.`name` AND a.val<=b.val
GROUP BY a.id,a.`name`,a.val,a.memo
HAVING COUNT(b.id) <= 2
ORDER BY a.`name`,a.val DESC
;
25、优化删除/更新子句
DELETE FROM tb_temp_car_zy_t1 WHERE car_code IN(SELECT car_code FROM gb_temp_todel);
xx IN(…) 无法使用索引,优化为:
DELETE tb_temp_car_zy_t1
FROM tb_temp_car_zy_t1,(SELECT car_code FROM gb_temp_todel) b
WHERE tb_temp_car_zy_t1.car_code=b.car_code;
参考:https://www.jianshu.com/p/87151989563f
UPDATE table1 set num = num + 1 where id in (SELECT id FROM table2 WHERE date>'2017-05-09);
in条件的更新效率可优化为使用join语法:
UPDATE table1 t1 INNER JOIN table2 t2 on t1.id = t2.id
set t1.num = t1.num + 1 where t2.date>'2019-05-09';