求最大值（分组）

[foxe]

N D Q
---------------------
A CS 1
A CS 3
A CS 8
B QK 9
B QK 5
C MS 2
C MS 2
C MS 2
C MS 8
D NF 4
D NF 1
D NF 1
D NF 1
D NF 4
结果:
N D Q
---------------------
A CS 8
B QK 9
C MS 8
D NF 4
取得没一个N的Ｑ为最大值时的记录

[foxe]

更正：取得每一个Ｎ的记录当Ｑ为最大值时

[jinjazz]

--建立测试环境
Create Table 表(N varchar(10),D varchar(10),Q varchar(10))
--插入数据
insert into 表
select 'A','CS','1' union
select 'A','CS','3' union
select 'A','CS','8' union
select 'B','QK','9' union
select 'B','QK','5' union
select 'C','MS','2' union
select 'C','MS','2' union
select 'C','MS','2' union
select 'C','MS','8' union
select 'D','NF','4' union
select 'D','NF','1' union
select 'D','NF','1' union
select 'D','NF','1' union
select 'D','NF','4'
select * from 表
--测试语句
select
a.*
from
表 a,(select N,max(Q)Q from 表 group by N )b
where a.N=b.N and A.Q=b.Q


--删除测试环境
Drop Table 表

[jinjazz]

--建立测试环境
Create Table 表(N varchar(10),D varchar(10),Q varchar(10))
--插入数据
insert into 表
select 'A','CS','1' union
select 'A','CS','3' union
select 'A','CS','8' union
select 'B','QK','9' union
select 'B','QK','5' union
select 'C','MS','2' union
select 'C','MS','2' union
select 'C','MS','2' union
select 'C','MS','8' union
select 'D','NF','4' union
select 'D','NF','1' union
select 'D','NF','1' union
select 'D','NF','1' union
select 'D','NF','4'
select * from 表
--测试语句
select
a.*
from
表 a where not exists(select 1 from 表 where N=a.N and Q>a.Q)


--删除测试环境
Drop Table 表
/*
N D Q
---------- ---------- ----------
A CS 8
B QK 9
C MS 8
D NF 4
*/

[zjcxc]

select * from tb a
where not exists(select * from tb where n=a.n and q>a.q)

[zjcxc]

select * from tb a
where q=(select max(q) from tb where n=a.n)

[talantlee]

select N,max(Q)as maxq into #temp from 表 group by N
select 表.* from 表 where 表.N=#temp.N and 表.Q=#temp.Q
drop table #temp