常用SQL语句

1、根据一个表的数据修改另一个表里同样字段的数据

UPDATE policyno p 
INNER JOIN riskcon r on p.spolicyno = r.spolicyno 
set p.csrno = r.csrno
where p.comid = 'QD'

2、一次删除表中一条重复数据保留剩余数据

DELETE 
FROM person 
WHERE sid IN (
SELECT a.sid 
FROM
(SELECT sid FROM person GROUP BY spolicyno, pertype HAVING count(*)>1) a )

3、删除表中多条重复数据只留一条数据

DELETE 
FROM person 
WHERE sid not IN (
SELECT a.sid 
FROM
(SELECT sid FROM person GROUP BY spolicyno, pertype HAVING count(*)>0) a )

4、表添加字段

ALTER TABLE users ADD errnum int(11) NULL DEFAULT NULL COMMENT '登录错误次数，5次锁定';

5、表修改字段名称

ALTER TABLE hx_file CHANGE fgsbasename fgsbasename varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '分公司供应商名称';

6、表修改字段类型

ALTER TABLE hx_file MODIFY fgsbasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '分公司供应商名称'

7、表删除字段

ALTER TABLE hx_file DROP COLUMN fgsbasename;

8、查询同一保单号对应多个物理单号的单子

select appno, policyno,count(distinct spolicyno) as policynosss
from riskcon
group by policyno
HAVING count(distinct spolicyno) > 1