Tips: 搜集整理具有参考价值SQL,未测试
目录
- 删除表中多余的重复记录,根据单个字段(peopleId)来判断,只留有rowid最小的记录
- 查找表中多余的重复记录(多个字段)
- 删除表中多余的重复记录(多个字段),只留有rowid最小的记录
- 查找表中多余的重复记录(多个字段),不包含rowid最小的记录
- 多表关联查询
删除表中多余的重复记录,根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1)
and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)
查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
多表关联查询
define query ad_query(@stdname string,@unitname string,@starttime date ,@endtime date)
begin
select org.stdname AS "单位名称",jbxxb.PG_JB_ZCPGXMMC AS "项目名称", data.GROUPID AS "基于文件类型的全部下载" , index.ATTACHMENTID AS "附件ID", index.ATTACHMENTNAME AS "附件名称" , zb.title AS "附件类型" , '全部下载' AS "全部下载", '下载' AS "下载",index.uploaddate AS "附件上传时间", index.uploadusername AS "附件上传人"
from THR_ATTACHMENT_INDEX as index
join RP_BLOBZBDATA as data
on data.recid = index.ATTACHMENTID
join pg_fjb as fjb
ON data.GROUPID = fjb.PG_FJ_ZCPGBGS
OR data.GROUPID = fjb.PG_FJ_ZXHNDSJBG
OR data.GROUPID = fjb.PG_FJ_JJXWPFWJ
//......
join b0101_zb as zb
on zb.recid = data.zb_id
join md_org as org
on fjb.unitid=org.recid and org.stdname CONTAINS @stdname
join pg_jbxxb as jbxxb
on jbxxb.unitid=fjb.unitid and jbxxb.datatime=fjb.datatime and jbxxb.PG_JB_ZCPGXMMC CONTAINS @unitname
where data.INS_TIME between @starttime and @endtime
end