SQL 有条件地筛选重复数据

 --基础了解：
1) select distinct name from table --打开重复记录的单个字段
2) select * from table where fid in(Select min(fid) FROM table group by name)--打开重复记录的所有字段值
3) select * from table where name in(select name from table group by name having count(name)=1)--打开重复任意次数的所有记录

--筛选重复数据就用上面的第二条语句，但是如果我要进行有条件地查询，还需要一点点转换，下面是一个例子：

--testact表创建如下：
CREATE TABLE testact
(
 tid int IDENTITY(1,1) PRIMARY KEY NOT NULL,--自增ID
 TDate datetime DEFAULT getdate() NOT NULL,--发表时间
 TUrl varchar(255) DEFAULT '' NOT NULL,--新闻链接地址
 TTop smallint DEFAULT 0 NOT NULL --是否置顶
)
--基本数据
INSERT INTO testact(TDate,TUrl,TTop) VALUES(getdate(),'http://www.baidu.com',1)
INSERT INTO testact(TDate,TUrl,TTop) VALUES(dateadd(dd,1,getdate()), 'http://www.baidu.com',0)

INSERT INTO testact(TDate,TUrl,TTop) VALUES(getdate(),'http://www.google.com',0)
INSERT INTO testact(TDate,TUrl,TTop) VALUES(dateadd(dd,1,getdate()),'http://www.google.com',0)

INSERT INTO testact(TDate,TUrl,TTop) VALUES(getdate(),'http://www.baidu.com?a=1',1)
INSERT INTO testact(TDate,TUrl,TTop) VALUES(dateadd(dd,1,getdate()),'http://www.baidu.com?a=1',1)
INSERT INTO testact(TDate,TUrl,TTop) VALUES(dateadd(dd,2,getdate()),'http://www.baidu.com?a=1',0)

INSERT INTO testact(TDate,TUrl,TTop) VALUES(getdate(),'http://www.baidu.com?b=1',1)
INSERT INTO testact(TDate,TUrl,TTop) VALUES(dateadd(dd,2,getdate()),'http://www.baidu.com?b=1',1)

INSERT INTO testact(TDate,TUrl,TTop) VALUES(getdate(),'http://www.baidu.com?c=1',0)

/*
要求：比如说有一个新闻表testact，有字段：tid(自增ID)，TDate(发表时间)，TUrl(链接地址)，TTop(是否置顶)。
表里数据TUrl(链接地址)可能会重复。
1）如果 TUrl(链接地址)重复，有一个置顶，一个不置顶的，则取置顶的数据（不管时间先后）。
2）如果 TUrl(链接地址)重复，都置顶的或都不置顶的，则取 发表时间（TDate）最新的数据。
3）如果 TUrl(链接地址)没有重复的，则直接取出。
4）最后对置顶和时间进行倒序排序
*/
这个问题我一开始看的时间感觉还真有点复杂，但后来考虑到用一个表进行转换，倒也方便，容易不难理解

--分析下面两个语句
[1]：SELECT * FROM testact ORDER BY turl, ttop DESC, tdate DESC --得出的排序的结果，分析结果不就是每一个重复TUrl的第一条记录吗
[2] select * from table where fid in(Select min(fid) FROM table group by name)--打开重复记录的所有字段值

--建立一个转换表(其实就是为了给[1]的结果增加一个自增ID， 再用[2]取ID最小的数据
CREATE TABLE testtmp
(
 tmpid int IDENTITY(1,1) PRIMARY KEY NOT NULL,
 tid int DEFAULT 0  NOT NULL,
 TUrl varchar(255) DEFAULT '' NOT NULL
)

--向testtmp插入testact的两列数据
INSERT INTO testtmp(tid,turl)
SELECT tid,turl FROM testact ORDER BY turl, ttop DESC, tdate DESC

--最后结果
SELECT * FROM testact WHERE tid IN(
SELECT tid FROM testtmp t WHERE tmpid IN(SELECT min(t.tmpid) FROM testtmp t GROUP BY t.TUrl))
ORDER BY TTop DESC , TDate DESC

 

 

--换一下条件，
1）如果新闻表TUrl有重复的，但日期有已过期的，也有还没过期的，则取还没过期的且离当前日期最近的记录
2）如果新闻表TUrl有重复的，但日期都已过期的，则取离当前日期最近的已过期记录
3）如果新闻表TUrl有重复的，但日期都未过期的，则取离当前日期最近的未过期记录
4）如果 TUrl(链接地址)重复，有一个置顶，一个不置顶的，则取置顶的数据（不管时间先后）。
5）最后对置顶和时间进行倒序排序

总之，对于重复的数据，有置顶的就显示置顶的，有还没过期的先显示还没过期的再到已过期的（但要显示离现在最近的日期）
DROP TABLE testtmp
CREATE TABLE testtmp
(
 tmpid int IDENTITY(1,1) PRIMARY KEY NOT NULL,
 tid int DEFAULT 0  NOT NULL,
 TUrl varchar(255) DEFAULT '' NOT NULL,
 tdate datetime DEFAULT getdate() NOT NULL, --时间
 tdate1 datetime,
 tdate2 datetime
)
INSERT INTO testtmp(tid,turl,tdate,tdate1, tdate2)
SELECT tid,turl,tdate,
CASE WHEN datediff(dd, getdate(), tdate) >= 0 THEN tdate ELSE '2030-01-01'  END  AS date1 ,
CASE WHEN datediff(dd, getdate(), tdate) < 0 THEN tdate ELSE '2000-01-01'  END  AS date2
 FROM testact ORDER BY turl, ttop DESC, date1, date2 DESC
 
 SELECT * FROM testact WHERE tid IN(
SELECT tid FROM testtmp t WHERE tmpid IN(SELECT min(t.tmpid) FROM testtmp t GROUP BY t.TUrl))
ORDER BY ttop DESC, tdate DESC

 

 

另外，在网上看到的例子：

表格式如下：

ID S_ID COURSE_NAME STATE
1 1900560013 课程A 0
2 1900560013 课程A 1
3 1900930116 课程A 0
4 1900930116 课程A 1
5 1900930116 课程B 1
6 1900930116 课程C 0
7 1900930116 课程B 0
8 1900930116 课程C 1


现在 S_ID，COURSE_NAME 两个字段有重复，需要删除重复，但是两条重复的数据需检查STATE，需要删除的是STATE=1的，如果两天重复的数据STATE都等于1，则任意删除一条。

 

create table #t
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
S_ID VARCHAR(50) NOT NULL,
COURSE_NAME VARCHAR(20) NOT NULL,
STATE BIT NOT NULL
)

insert into #t(S_ID, COURSE_NAME, STATE)
SELECT  '1900560013','A',0 UNION
SELECT '1900560013','A',1 UNION
SELECT '1900930116','A',1 UNION
SELECT '1900930116','A',1 UNION
SELECT '1900930116','A',1 UNION
SELECT '1900930116','B',1 UNION
SELECT '1900930116','C',0 UNION
SELECT '1900930116','B',0 UNION
SELECT '1900930116','C',1 UNION
SELECT '1900930117','D',1 UNION
SELECT '1900930117','D',1

select * from #t

delete from #t
where ID not in
(
    select min(id)
    from #t
    group by S_ID,COURSE_NAME,[STATE]
)

select * from #t

--这个地方就按照你的要求删除S_ID,COURSE_NAME一样，但[STATE]=1的数据
delete from #t
where ID in
(
    select t.ID from #t t
    inner join
    (
        select S_ID,COURSE_NAME from #t
        group by S_ID,COURSE_NAME
        having COUNT(*)>1
    ) as tt on t.s_id=tt.s_id and t.course_name=tt.course_name
    where t.state=1
)
select * from #t
DROP TABLE #t

 

总结一下：就是先删除S_ID COURSE_NAME STATE 都有重复的数据，然后再删除 group by S_ID,COURSE_NAME
 超过两条记录， 且 state=1 的记录