删除多字段的重复行保留最大最小行

有张表格之前没有设计关键字段的唯一约束，导致有时候执行插入操作时不小心执行了多次就出现了重复记录，后面重新加入唯一约束，由于已经有了重复记录，无法添加，需要先删除重复记录。

看了网上的一些删除重复记录的方法（好像都是转载于同一篇文章，至少看了十几篇都是同样的内容），其中一个链接：http://blog.csdn.net/anya/article/details/6407280

自己现在是需要建立多字段唯一约束(非主键)，用文中的方法是不行的，反正在SQL Server 2012中是无法通过的。改写了下，如下：

1、查询重复记录：

[sql] view plain copy

SELECT * FROM dbo.Table T
WHERE EXISTS (SELECT 字段1, 字段2, 字段3 FROM dbo.TableSign WHERE 字段1= T.字段1 AND 字段2= T.字段2
AND 字段3 = T.字段3 GROUP BY 字段1, 字段2, 字段3 HAVING COUNT(*) > 1)
AND T.SelfID NOT IN (SELECT MIN(SelfID) FROM dbo.Table GROUP BY 字段1, 字段2, 字段3 HAVING COUNT(*) > 1)
其中：字段1, 字段2, 字段3指需要建立唯一约束的三个字段，SelfID指表Table中的一个自增字段。

2、删除重复记录，只保留SelfID最小的记录，也就是第一次插入的记录：

[sql] view plain copy

DELETE FROM dbo.TableSign
WHERE SelfID IN
(SELECT SelfID FROM dbo.Table T
WHERE EXISTS (SELECT 字段1, 字段2, 字段3 FROM dbo.TableSign WHERE 字段1= T.字段1 AND 字段2= T.字段2
AND 字段3 = T.字段3 GROUP BY 字段1, 字段2, 字段3 HAVING COUNT(*) > 1)
AND T.SelfID NOT IN (SELECT MIN(SelfID) FROM dbo.Table GROUP BY 字段1, 字段2, 字段3 HAVING COUNT(*) > 1))

SQL中的联接类型可以分为如下几种：

1）内联接 仅显示两个联接表中的匹配行的联接。

2）外联接 甚至包括在联接表中没有相关行的行的联接。可以创建外联接的三个变化形式来指定所包括的不匹配行：

2-1）左向外联接
2-2）右向外联接
2-3）完整外部联接
3）交叉联接
其中值得补充的是自联接
4）自联接

表可以通过自联接与自身联接。例如，可以使用自联接查找一张表使用相同Email的用户。

现在students表数据如下：

Stuid       Sname      semail
----------- ---------- --------------------------------------------------
1           张飞         zhangfei@sanguo.com
2           刘备         liubei@sanguo.com
3           关羽         guanyu@sanguo.com
4           赵云         zhaoyun@sanguo.com
5           赵小云        zhaoyun@sanguo.com

如何找出具有相同email的用户姓名呢？

由于此查询涉及 students表与其自身的联接，因此 students表以两种角色显示。若要区分这两个角色，必须在 FROM 子句中为 students表提供两个不同的别名（st1 和 st2）。这些别名用来限定其余查询中的列名。

select st1.sname,st2.sname,st1.semail
from students st1 inner join students st2
on st1.stuid=st2.stuid
where st1.semail=st2.semail

以下是结果集：

sname      sname      semail
---------- ---------- --------------------------------------------------
张飞         张飞         zhangfei@sanguo.com
刘备         刘备         liubei@sanguo.com
关羽         关羽         guanyu@sanguo.com
赵云         赵云         zhaoyun@sanguo.com
赵小云        赵小云        zhaoyun@sanguo.com

(5 行受影响)

若要消除完全相同的行，那么必须要这样

select st1.sname,st2.sname,st1.semail
from students st1 inner join students st2
on st1.stuid<>st2.stuid --注意这里的不等号
where st1.semail=st2.semail

以下是结果集：

sname      sname      semail
---------- ---------- --------------------------------------------------
赵小云        赵云         zhaoyun@sanguo.com
赵云         赵小云        zhaoyun@sanguo.com

(2 行受影响)

若要消除结果中用户姓名完全顺序颠倒相同的行（顺序颠倒）
select st1.sname,st2.sname,st1.semail
from students st1 inner join students st2
on st1.stuid>st2.stuid
where st1.semail=st2.semail

以下是结果集：

sname sname semail
---------- ---------- --------------------------------------------------
赵小云赵云 zhaoyun@sanguo.com

(1 行受影响)

这样就达到目标了

-----------------------------------------------------------------------------------------------

一个表与自身进行连接，称为自连接

问题的提出:一个网友提出这样一个SQL题目，说自己想了很久没解决，我一看，这不是很简单吗

可是自己在查询分析器调试了半天原来问题并不是那不简单

有一个学生表,里面有学号功课编号

学生成绩三个字段.用一个SQL查询语句得出每门功课成绩最好的前两名

学号功课编号学生成绩

1 1 99

2 1 98

3 1 100

4 2 88

5 2 87

6 2 88

7 3 99

8 3 88

9 3 100

解决方法

SELECT DISTINCT 学生表1.*

FROM 学生表学生表1 INNER JOIN

学生表学生表2 ON 学生表1.学号 IN

(SELECT TOP 2 学生表.学号

FROM 学生表

WHERE 学生表.功课编号 = 学生表1.功课编号

ORDER BY 学生成绩 DESC)

查询结果

学号功课编号学生成绩

1 1 99

2 1 98

4 2 88

6 2 88

7 3 99

9 3 100

------------------------------

select top 100 * from MES_RepairDevice where RepareDate='2015-07-15 00:00:00.000' and DeviceNo='JUK8.081.10024037'

select MIN(ID), RepareDate,DeviceType,DeviceNo,MouldNo,RepairType,BeginDate,EndDate
from MES_RepairDevice
group by RepareDate,DeviceType,DeviceNo,MouldNo,RepairType,BeginDate,EndDate
having COUNT(1)>1

select * from MES_RepairDevice
where ID in(
select max(ID)
from MES_RepairDevice
group by RepareDate,DeviceType,DeviceNo,MouldNo,RepairType,BeginDate,EndDate
having COUNT(1)>1
)
-------------------------------------------------------------------------------------------------------------
select AA.RepareDate,AA.DeviceType,AA.DeviceNo,AA.MouldNo,AA.RepairType,AA.BeginDate,AA.EndDate
into #SS from MES_RepairDevice AA
group by AA.RepareDate,AA.DeviceType,AA.DeviceNo,AA.MouldNo,AA.RepairType,AA.BeginDate,AA.EndDate
having COUNT(1)>1

select top 100 * from MES_RepairDevice where RepareDate='2015-07-15 00:00:00.000' and DeviceNo='JUK8.081.10024037'

select * from #SS
select TT.ID,AA.* from #SS AA join MES_RepairDevice TT on
AA.RepareDate=TT.RepareDate
and AA.DeviceType=TT.DeviceType
and AA.DeviceNo=TT.DeviceNo
--and AA.MouldNo=TT.MouldNo
and AA.RepairType=TT.RepairType
--and AA.BeginDate=TT.BeginDate
--and AA.EndDate=TT.EndDate

-------------------------------------------------------------------------------------------------------------
select TT.ID,TT.* from MES_RepairDevice TT where
EXISTS
(select RepareDate,DeviceType,DeviceNo,
MouldNo,RepairType,BeginDate,EndDate
from MES_RepairDevice
where RepareDate=TT.RepareDate
and DeviceType=TT.DeviceType
and DeviceNo=TT.DeviceNo
and RepairType=TT.RepairType
group by RepareDate,DeviceType,DeviceNo,MouldNo,RepairType,BeginDate,EndDate
having COUNT(1)>1
)
and
TT.ID not in
(
select MIN(ID)
from MES_RepairDevice
group by RepareDate,DeviceType,DeviceNo,MouldNo,RepairType,BeginDate,EndDate
having COUNT(1)>1
)
------------------------------------------------------------------------------------------------------------

create table #Test
(
ID int identity(1,1),
Sname nvarchar(100),
Semail nvarchar(100)
)

insert into #Test
select '刘备','liubei@sanguo.com'
union
select '关羽','guanyu@sanguo.com'
union
select '赵云','zhaoyun@sanguo.com'
union
select '赵小云','zhaoyun@sanguo.com'

select * from #Test

select AA.*,BB.* from #Test AA inner join #Test BB on AA.ID<>BB.ID
where AA.Semail=BB.Semail