再问一个SQL语句

[tjandy]

表testtable
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 3
c 1100 2

希望得到的结果

nick tmp tmp1
b 1200 10
a 1100 3
c 1100 2

[fcuandy]

不明白你在说什么.

你自己得到的结果跟你希望的结果有什么不同吗?

[chuifengde]

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

*/

[kk19840210]

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.data



nick       data        data1
---------- ----------- -----------
a          1100        2
b          1200        10
c          1100        2

(3 行受影响)

[kk19840210]

楼主的结果应该写错了吧 按楼主意识

[wzy_love_sly]

看明白了；

[wzy_love_sly]

鸟儿写的

[fcuandy]

看半天算看明白了, 取同组中data最大的一条记录,如果最大值有重复值,再在data最大值的记录中按 data1最大的取.
data1要有重复呢?

[tjandy]

to fcuandy(人, 无完人;学, 无止境)

如果data1重复就没事了，直接返回就行了

[playwarcraft]

好像是a有重復的，把data1加起來？？

[wzy_love_sly]

楼上，应该是
取同组中data最大的，如果data最大值有重复值，把重复的data1相加。

[tjandy]

问题中是有个地方写错了，我改了下，再发一下

表testtable
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

[kk19840210]

要是 data 相同 返回 data1的和

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 行受影响)

[fcuandy]

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 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

[tjandy]

to kk19840210(飞天小虫)

谢谢你的回答，还有个问题，如果要是每条记录再有一个Data字段来规定范围，那SQL语句要怎么改呢？

[dawugui]

没看懂楼主的结果．我写了两个，不知道哪个合适？

create table tb(nick varchar(10), data int, data1 int)
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 )
go

select 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

[dawugui]

至于分组取值的问题，看下面的东西．
--按某一字段分组取最大(小)值所在行的数据(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
*/