表1
学校 年级 班级 姓名 性别 年龄
1中 1 2班 张 男 20 --这个曲调
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15
2中 3 6班 李 女 18 -- 这个取掉当姓名和性别相同的时候,取年龄最小的 一条记录查询的结果是学校 年级 班级 姓名 性别 年龄
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15
学校 年级 班级 姓名 性别 年龄
1中 1 2班 张 男 20 --这个曲调
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15
2中 3 6班 李 女 18 -- 这个取掉当姓名和性别相同的时候,取年龄最小的 一条记录查询的结果是学校 年级 班级 姓名 性别 年龄
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--方法1：
select a.* from tb a where 年龄 = (select min(年龄) from tb where 姓名 = a.姓名 and 性别 = b.性别)
--方法2：
select a.* from tb a where not exists(select 1 from tb where 姓名 = a.姓名 and 性别 = b.性别 and 年龄 < a.年龄)
create table tb(学校 varchar(10),年级 int,班级 varchar(10),姓名 varchar(10),性别 varchar(10),年龄 int)
insert into tb values('1中', 1, '2班', '张', '男', 20)
insert into tb values('2中', 3, '1班', '马', '男', 17)
insert into tb values('1中', 2, '2班', '李', '女', 16)
insert into tb values('2中', 3, '5班', '张', '男', 15)
insert into tb values('2中', 3, '6班', '李', '女', 18)
go--方法1：
select a.* from tb a where 年龄 = (select min(年龄) from tb where 姓名 = a.姓名 and 性别 = a.性别) order by 学校
--方法2：
select a.* from tb a where not exists(select 1 from tb where 姓名 = a.姓名 and 性别 = a.性别 and 年龄 < a.年龄) order by 学校drop table tb/*
学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
1中 2 2班 李 女 16
2中 3 1班 马 男 17
2中 3 5班 张 男 15（所影响的行数为 3 行）学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
1中 2 2班 李 女 16
2中 3 1班 马 男 17
2中 3 5班 张 男 15（所影响的行数为 3 行）
*/
insert into tb values('1中', 1, '2班', '张', '男', 20)
insert into tb values('2中', 3, '1班', '马', '男', 17)
insert into tb values('1中', 2, '2班', '李', '女', 16)
insert into tb values('2中', 3, '5班', '张', '男', 15)
insert into tb values('2中', 3, '6班', '李', '女', 18)
go--方法1：
select a.* from tb a where 年龄 = (select min(年龄) from tb where 姓名 = a.姓名 and 性别 = a.性别) order by a.学校
--方法2：
select a.* from tb a where not exists(select 1 from tb where 姓名 = a.姓名 and 性别 = a.性别 and 年龄 < a.年龄) order by a.学校
--方法3：
select a.* from tb a,(select 姓名,性别,min(年龄) 年龄 from tb group by 姓名,性别) b where a.姓名 = b.姓名 and a.性别 = b.性别 and a.年龄 = b.年龄 order by a.学校
--方法4：
select a.* from tb a inner join (select 姓名,性别 , min(年龄) 年龄 from tb group by 姓名,性别) b on a.姓名 = b.姓名 and a.性别 = b.性别 and a.年龄 = b.年龄 order by a.学校
--方法5
select a.* from tb a where 1 > (select count(*) from tb where 姓名 = a.姓名 and 性别 = a.性别 and 年龄 < a.年龄) order by a.学校drop table tb/*
学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
1中 2 2班 李 女 16
2中 3 1班 马 男 17
2中 3 5班 张 男 15（所影响的行数为 3 行）学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
1中 2 2班 李 女 16
2中 3 1班 马 男 17
2中 3 5班 张 男 15（所影响的行数为 3 行）学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
1中 2 2班 李 女 16
2中 3 1班 马 男 17
2中 3 5班 张 男 15（所影响的行数为 3 行）学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
1中 2 2班 李 女 16
2中 3 1班 马 男 17
2中 3 5班 张 男 15（所影响的行数为 3 行）学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
1中 2 2班 李 女 16
2中 3 1班 马 男 17
2中 3 5班 张 男 15（所影响的行数为 3 行）*/
以下网址是官方的解决方案 http://support.microsoft.com/kb/139444/zh-cn以下是一个实例说明--创建一个表,假设col1和col2两列的组合应该惟一，但是现在有重复键，我们的目的就是去除重复行
create table t1(
col1 int,
col2 int,
col3 char(50)
)insert into t1
select 1, 1, 'data value 1' union all
select 1, 1, 'data value 1' union all
select 1, 1, 'data value 1' union all
select 1, 2, 'data value 2' union all
select 1, 3, 'data value 3-1' union all
select 1, 3, 'data value 3-2' union all
select 1, 3, 'data value 3-3' union all
select 1, 4, 'data value 4' union all
select 1, 5, 'data value 5' union all
select 1, 6, 'data value 6-1' union all
select 1, 6, 'data value 6-2' union all
select 1, 7, 'data value 7' union all
select 1, 8, 'data value 8'--看一下表可以发现,(1,1),(1,3),(1,6)有重复
select * from t1--选择重复的键值放入临时表中
SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1--查看表holdkey
select * from holdkey--结果如下，可知(1,1)重复行为3行，同理(1,3)为3行，(1,6)为2行
col1 col2 col3
----------- ----------- -----------
1 1 3
1 3 3
1 6 2（所影响的行数为 3 行）--选择重复的行放入临时表中，以清除进程中的重复值。例如：
SELECT DISTINCT a.*
INTO holddups
FROM t1 a inner join holdkey b
on (a.col1 = b.col1 AND a.col2 = b.col2)--查看holddups表
select * from holddupscol1 col2 col3
----------- ----------- --------------------------------------------------
1 1 data value 1
1 3 data value 3-1
1 3 data value 3-2
1 3 data value 3-3
1 6 data value 6-1
1 6 data value 6-2 （所影响的行数为 6 行）--查询是否存在键重复而行唯一的情况
SELECT col1, col2, count(*) as 行数
FROM holddups
GROUP BY col1, col2--结果如下，可见（1,3）,(1,6)都在键重复但是行惟一的情况
--因为行数列全为1才是正常的
col1 col2 行数
----------- ----------- -----------
1 1 1
1 3 3
1 6 2（所影响的行数为 3 行） --下面要修改holddubs表，将同一键值，但是第三列不同的情况删除。保证不同键值时行一定惟一
delete holddups
where col3 in
('data value 3-2','data value 3-3','data value 6-2')--再查询holddups，看是否正常
select * from holddupsSELECT col1, col2, count(*) as 行数
FROM holddups
GROUP BY col1, col2--结果为下，行数全为1,说明是正常的了
col1 col2 行数
----------- ----------- -----------
1 1 1
1 3 1
1 6 1（所影响的行数为 3 行）--从原始表中删除重复的行
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2--将惟一行放回原始表
INSERT t1 SELECT * FROM holddups--查看原始表
select * from t1--结果如下，可见重复键的列已没有了
col1 col2 col3
----------- ----------- --------------------------------------------------
1 1 data value 1
1 3 data value 3-1
1 6 data value 6-1
1 2 data value 2
1 4 data value 4
1 5 data value 5
1 7 data value 7
1 8 data value 8 （所影响的行数为 8 行）
on a.姓名=b.姓名 and a.性别=b.性别 and a.年龄=b.年龄
学校 年级 班级 姓名 性别 年龄
1中 1 2班 张 男 20 --这个曲调
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15
2中 3 6班 李 女 18 -- 这个取掉 当姓名和性别相同的时候,取年龄最小的 一条记录 查询的结果是 学校 年级 班级 姓名 性别 年龄
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15
select *
from table a
where not exists(select 1 from table where a.姓名= 姓名 and a.性别= 性别 and a.年龄>年龄)
insert into @T values('1中', 1, '2班', '张', '男', 20)
insert into @T values('2中', 3, '1班', '马', '男', 17)
insert into @T values('1中', 2, '2班', '李', '女', 16)
insert into @T values('2中', 3, '5班', '张', '男', 15)
insert into @T values('2中', 3, '6班', '李', '女', 18)
select
*
from
@T t
where
年龄!>all (select min(年龄) from @T where 姓名 = t.姓名 and 性别 = t.性别)
order by 班级 asc学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15（所影响的行数为 3 行）
--这发两种写法all/any
declare @T table (学校 varchar(10),年级 int,班级 varchar(10),姓名 varchar(10),性别 varchar(10),年龄 int)
insert into @T values('1中', 1, '2班', '张', '男', 20)
insert into @T values('2中', 3, '1班', '马', '男', 17)
insert into @T values('1中', 2, '2班', '李', '女', 16)
insert into @T values('2中', 3, '5班', '张', '男', 15)
insert into @T values('2中', 3, '6班', '李', '女', 18)
select
*
from
@T t
where
年龄!>ANY (select min(年龄) from @T where 姓名 = t.姓名 and 性别 = t.性别)
order by 班级 asc学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15（所影响的行数为 3 行）
declare @T table (学校 varchar(10),年级 int,班级 varchar(10),姓名 varchar(10),性别 varchar(10),年龄 int)
insert into @T values('1中', 1, '2班', '张', '男', 20)
insert into @T values('2中', 3, '1班', '马', '男', 17)
insert into @T values('1中', 2, '2班', '李', '女', 16)
insert into @T values('2中', 3, '5班', '张', '男', 15)
insert into @T values('2中', 3, '6班', '李', '女', 18)
select
*
from
@T t
group by 学校,年级,班级,姓名,性别,年龄
having
年龄=(select min(年龄) from @T where 姓名 = t.姓名 and 性别 = t.性别)
order by 班级
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）学校 年级 班级 姓名 性别 年龄
---------- ----------- ---------- ---------- ---------- -----------
2中 3 1班 马 男 17
1中 2 2班 李 女 16
2中 3 5班 张 男 15（所影响的行数为 3 行）