常用SQL语句优化

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';