平时常用sql语句集锦

一、sql去重（有自定id的情况）

思路：

1.先按照需要去重的组合字段进行分组查询，按照userid和change_count_field_name字段组合去重

select count(*) from stat_custom_change_data_today a where a.t_day = '2015-11-12' and a.mtype = 2 group by a.userid,a.change_count_field_name

2.从1步骤中选出最小(或者最大)的id，这些id的数据作为保留数据

select min(id) as min_id from stat_custom_change_data_today a where a.t_day = '2015-11-12' and a.mtype = 2 group by a.userid,a.change_count_field_name

3.然后使用not exists或者not in(性能不如not exists)排除保留的数据，剩下的就是需要去掉的重复数据

select b.id from stat_custom_change_data_today b where b.t_day = '2015-11-12' and b.mtype = 2 and not exists
(select min_id from 
(select min(id) as min_id from stat_custom_change_data_today a where a.t_day = '2015-11-12' and a.mtype = 2 group by a.userid,a.change_count_field_name) c where b.id = c.min_id)

二、update中进行表关联

update table1 a, table2 b set a.name = 'andy' where a.id = b.id;

三、批量insert

insert into table1 (name,age) values (select  name age from table2);