----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-10 12:38:30
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2(Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[People]
if object_id('[People]') is not null drop table [People]
go
create table [People]([name] varchar(4),[sex] varchar(1),[age] int,[love] varchar(6),[marry] varchar(2))
insert [People]
select '张三','N',22,'足球','否' union all
select '张三','F',50,'篮球','否' union all
select '张三','N',50,'篮球','否' union all
select '赵六','F',30,'上网','是' union all
select '赵六','N',30,'上网','是' union all
select '赵六','N',30,'听音乐','是'
--------------开始查询--------------------------
select * from [People] a
WHERE EXISTS (SELECT 1 FROM (SELECT MAX(love)love,name,sex FROM [People] GROUP BY name,sex)b
WHERE a.love=b.love AND a.NAME=b.NAME AND a.sex=b.sex )
----------------结果----------------------------
/*
name sex age love marry
---- ---- ----------- ------ -----
张三 N 22 足球 否
张三 F 50 篮球 否
赵六 F 30 上网 是
赵六 N 30 听音乐 是
*/
SELECT [name],[sex],[age],[love],[marry]
FROM
(
SELECT *,rn=ROW_NUMBER() OVER(PARTITION BY [name],[sex] ORDER BY GETDATE())
FROM [People]
) T
WHERE rn=1
create table People
(name varchar(10), sex varchar(10), age int, love varchar(10), marry varchar(10))
insert into People
select '张三', 'N', 22, '足球', '否' union all
select '张三', 'F', 50, '篮球', '否' union all
select '张三', 'N', 50, '篮球', '否' union all
select '赵六', 'F', 30, '上网', '是' union all
select '赵六', 'N', 30, '上网', '是' union all
select '赵六', 'N', 30, '听音乐', '是'
select distinct name,sex from People
/*
name sex
---------- ----------
张三 F
张三 N
赵六 F
赵六 N
(4 row(s) affected)
*/
select identity(int,1,1) 'rn',*
into #t
from People
select a.name,a.sex,a.age,a.love,a.marry
from #t a
where not exists(select 1 from #t b
where b.rn
/*
name sex age love marry
---------- ---------- ----------- ---------- ----------
张三 N 22 足球 否
张三 F 50 篮球 否
赵六 F 30 上网 是
赵六 N 30 上网 是
(4 row(s) affected)
*/
if object_id('[People]') is not null drop table [People]
go
create table [People]([name] varchar(4),[sex] varchar(1),[age] int,[love] varchar(6),[marry] varchar(2))
insert [People]
select '张三','N',22,'足球','否' union all
select '张三','F',50,'篮球','否' union all
select '张三','N',50,'篮球','否' union all
select '赵六','F',30,'上网','是' union all
select '赵六','N',30,'上网','是' union all
select '赵六','N',30,'听音乐','是'
--------------开始查询--------------------------
select distinct
p.*
from People p
inner join
(
select name,
sex,
MAX(cast(age as varchar) + love + marry) as st
from People
group by name,sex
)pp
on p.name = pp.name
and p.sex = pp.sex
and cast(p.age as varchar) + p.love + p.marry = pp.st
----------------结果----------------------------
/*
name sex age love marry
张三 F 50 篮球 否
张三 N 50 篮球 否
赵六 F 30 上网 是
赵六 N 30 听音乐 是
*/
delete from People
where not exists (select 1 from (select * from [People] a
WHERE EXISTS (SELECT 1 FROM (SELECT MAX(love)love,name,sex FROM [People] GROUP BY name,sex)b
WHERE a.love=b.love AND a.NAME=b.NAME AND a.sex=b.sex )) b where People.NAME=b.name AND People.sex=b.sex AND People.age=b.age AND People.love=b.love AND People.marry=b.marry)
SELECT * FROM dbo.People
/*
name sex age love marry
---- ---- ----------- ------ -----
张三 N 22 足球 否
张三 F 50 篮球 否
赵六 F 30 上网 是
赵六 N 30 听音乐 是
*/
while 1=1
begin
delete top (1) from People
from People a join ( select name,sex from People group by name,sex having COUNT(*)>1 ) b
on a.name=b.name and a.sex=b.sex
if @@ROWCOUNT=0 break;
end;
create table People
(name varchar(10), sex varchar(10), age int, love varchar(10), marry varchar(10))
insert into People
select '张三', 'N', 22, '足球', '否' union all
select '张三', 'F', 50, '篮球', '否' union all
select '张三', 'N', 50, '篮球', '否' union all
select '赵六', 'F', 30, '上网', '是' union all
select '赵六', 'N', 30, '上网', '是' union all
select '赵六', 'N', 30, '听音乐', '是'
select identity(int,1,1) 'rn',*
into #t
from People
truncate table People
insert into People
select a.name,a.sex,a.age,a.love,a.marry
from #t a where not exists(select 1 from #t b
where b.rn
select * from People
/*
name sex age love marry
---------- ---------- ----------- ---------- ----------
张三 N 22 足球 否
张三 F 50 篮球 否
赵六 F 30 上网 是
赵六 N 30 上网 是
(4 row(s) affected)
*/