删除多字段的重复行保留最大最小行
有张表格之前没有设计关键字段的唯一约束，导致有时候执行插入操作时不小心执行了多次就出现了重复记录，后面重新加入唯一约束，由于已经有了重复记录，无法添加，需要先删除重复记录。
看了网上的一些删除重复记录的方法（好像都是转载于同一篇文章，至少看了十几篇都是同样的内容），其中一个链接：http://blog.csdn.net/anya/article/details/6407280
自己现在是需要建立多字段唯一约束(非主键)，用文中的方法是不行的，反正在SQL Server 2012中是无法通过的。改写了下，如下：
1、查询重复记录：
- 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最小的记录，也就是第一次插入的记录：
- 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
posted on 2015-12-07 11:28 chengjunde 阅读(280) 评论(0) 编辑 收藏 举报
