常用SQL语句总结

常用SQL语句总结

查询某个分组类别的其中某个字段是否有重复数据

select user_id,seq_num ,count(seq_num) from user_bill group by user_id,seq_num
having count(seq_num)>1

分组取每组前几条记录(排序)

select a.* from user_bill a where (
  select count(id) from user_bill where user_id=a.user_id and seq_num>a.seq_num
) < 2 order by user_id,seq_num desc;

分组取val最大的值所在行

select a.* from user_bill a where seq_num = 
(select max(seq_num) from user_bill where user_id=a.user_id) order by a.user_id,a.seq_num desc;

select max(seq_num) ,user_id from user_bill group by user_id order by user_id,seq_num desc;

mysql如果有数据就更新，没有数据就插入的方法

INSERT INTO tablename (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a=a+1;

INSERT INTO test_version (value,version) VALUES ('3',4) ON DUPLICATE KEY UPDATE version=4;

mysql日期类型判断

date_add(CURRENT_DATE, interval 1 month)>str_to_date(RTRIM(YXQZ), '%Y-%m-%d') 
CURRENT_DATE<=str_to_date(YXQZ, '%Y-%m-%d')