常用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';
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值