declare @a table(nick varchar(20),data int, data1 int)
insert @a select 'a', 1000,10
union all select 'a', 1100,1
union all select 'a', 1000,5
union all select 'a', 1100,2
union all select 'b', 1200,10
union all select 'c', 1100,2
union all select 'c', 1000,20 select nick,data,sum(data1) data1 from
(select * from @a a where not exists(select 1 from @a where nick=a.nick and data>a.data)) s
group by nick,data
--result
/*nick data data1
-------------------- ----------- -----------
a 1100 3
c 1100 2
b 1200 10*/
CREATE TABLE testtable(nick varchar(10),data int,data1 int)insert into testtable values('a',1000,10)insert into testtable values('a',1100,1)insert into testtable values('a',1000,5)insert into testtable values('a',1100,2)insert into testtable values('b',1200,10)insert into testtable values('c',1100,2)insert into testtable values('c',1000,20)select a.nick,a.data,b.data1
from
(select nick,data=max(data) from testtable group by nick)a inner join
(
select nick,data,data1=max(data1) from testtable group by nick,data
) b on a.nick=b.nick and a.data=b.datanick data data1
---------- ----------- -----------
a 1100 2
b 1200 10
c 1100 2(3 行受影响)
data1要有重复呢?
取同组中data最大的,如果data最大值有重复值,把重复的data1相加。
nick data data1
a 1000 10
a 1100 1
a 1000 5
a 1100 2
b 1200 10
c 1100 2
c 1000 20说明一下,要统计这张表中data字段最高的和其所对应的data1的合集,
如果出现data值相同,还要按data1的大小返回我自己写了一下
select nick,max(data) as tmp,max(data1) as tmp1 from testtable group by nick order by data desc,data1 desc
得到的结果
nick tmp tmp1
b 1200 10
a 1100 10
c 1100 2希望得到的结果nick tmp tmp1
b 1200 10
a 1100 3
c 1100 2
select a.nick,a.data,b.data1
from
(select nick,data=max(data) from testtable group by nick)a inner join
(
select nick,data,data1=sum(data1) from testtable group by nick,data
) b on a.nick=b.nick and a.data=b.data order by a.data desc,b.data1 desc
nick data data1
---------- ----------- -----------
b 1200 10
a 1100 3
c 1100 2(3 行受影响)
insert @a select 'a', 1000,10
union all select 'a', 1100,1
union all select 'a', 1000,5
union all select 'a', 1100,2
union all select 'b', 1200,10
union all select 'c', 1100,2
union all select 'c', 1000,20
select a.nick,a.data,sum(a.data1) from @a a
inner join
(select max(data) data,nick from @a group by nick) b
on a.nick=b.nick and a.data=b.data
group by a.nick,a.data
写法有n种,均以 以下语句为原型1,select a.* from tb a where not exists(select 1 from tb where id2=a.id2 and a>a.a)
2,select a.* from tb a where checksum(id2,a) in(select checksum(id2,max(a)) group by id2)
3,select a.* from tb a where a=(select max(a) from tb where id2=a.id2)
4,select a.* from tb a where 1>(select count(*) from tb where id2=a.id2 and a>a.a)
5,select a.* from tb a
inner join
(select max(a) a ,id2 from tb group by id2) b
on a.id2=b.id2 and a.a=b.a
6,select a.* from tb a
inner join
(select checksum(id2,max(a)) group by id2) b
on checksum(a.id2,a.a)=checksum(b.id2,b.a)
7,select a.* from tb where a=(select top 1 a from tb where a.id2=id2 order by a desc)写法太多,不一一列举了
从性能考滤,推荐顺序为
5,6,2
1,3
7,4
insert into tb values('a', 1000, 10 )
insert into tb values('a', 1100, 1 )
insert into tb values('a', 1000, 5 )
insert into tb values('a', 1100, 2 )
insert into tb values('b', 1200, 10 )
insert into tb values('c', 1100, 2 )
insert into tb values('c', 1000, 20 )
goselect a.* from tb a where data = (select max(data) from tb where nick = a.nick) order by a.nick , a.data
/*
nick data data1
---------- ----------- -----------
a 1100 1
a 1100 2
b 1200 10
c 1100 2(所影响的行数为 4 行)
*/select m.* from (select a.* from tb a where data = (select max(data) from tb where nick = a.nick) ) m where data1 = (select max(data1) from (select a.* from tb a where data = (select max(data) from tb where nick = a.nick) ) n where n.nick = m.nick and n.data = m.data) order by m.nick , m.data
/*
nick data data1
---------- ----------- -----------
a 1100 2
b 1200 10
c 1100 2(所影响的行数为 3 行)
*/drop table tb
--按某一字段分组取最大(小)值所在行的数据(2007-10-23于浙江杭州)
/*
数据如下:
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
*/