sql常用骚操作
查询某个字段重复的记录:
select * from TableA where b in (select b from TableA group by b having count(b) > 1)
查询某个字段重复的次数:
select sys_update_time,count(*) as count from gczb_packages_bidders group by sys_update_time
having count>100
insert into + select + join搭配使用：
insert into user_has_role(user_id, role_id) select * from(select a.id,2 from others_irp_user a left join user_has_role b on a.id=b.user_id where b.role_id is null) as tb
update + select + join搭配使用：
update output_list a
left join approve b on a.aid=b.id
left join others_irp_project c on a.pid = c.id
set b.is_loan_check = 1,b.is_filling_check = 1
where b.is_loan_check = 0 or b.is_filling_check = 0
查询updatetime字段值一样的数据，更新update，保证updatetime数据不重复，逐个累加：
set @r:=1495181783;
update gczb_agreements set last_update_time=FROM_UNIXTIME((@r:=@r+1)) where last_update_time in (select last_update_time from gczb_agreements group by last_update_time having count(last_update_time) > 300);
获取某个数据库表的所有索引生成创建sql：
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ','ADD ',IF(NON_UNIQUE=1,CASE
UPPER(INDEX_TYPE)
WHEN'FULLTEXT'THEN'FULLTEXT INDEX'
WHEN'SPATIAL'THEN'SPATIAL INDEX'ELSE CONCAT('INDEX ',INDEX_NAME,' USING ',INDEX_TYPE)
END,IF
(UPPER(INDEX_NAME)='PRIMARY',CONCAT('PRIMARY KEY USING ',INDEX_TYPE),CONCAT('UNIQUE INDEX ',INDEX_NAME,' USING ',INDEX_TYPE))),'(',GROUP_CONCAT(DISTINCT CONCAT('',COLUMN_NAME,'')ORDER BY SEQ_IN_INDEX ASC SEPARATOR', '),');')AS'Show_Add_Indexes'
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA='数据库名称'
AND TABLE_NAME='表名'
GROUP BY
TABLE_NAME,INDEX_NAME
ORDER BY
TABLE_NAME ASC,INDEX_NAME ASC;
