本帖最后由 guojun17 于 2012-10-25 14:45:59 编辑 表T
id AA BB(int)
1 a 10
2 a 8
3 a 6
4 b 11
5 b 3
6 c 540
7 c 55
.......
我想一个查询 字段AA中所有不重复值的最大值
查询结果想要
1 a 10
4 b 11
6 c 540
这样的SQL请问怎么写
------最佳解决方案--------------------
if object_id('[T]') is not null drop table [T]
go
create table [T]([id] int,[AA] varchar(1),[BB] int)
insert [T]
select 1,'a',10 union all
select 2,'a',8 union all
select 3,'a',6 union all
select 4,'b',11 union all
select 5,'b',3 union all
select 6,'c',540 union all
select 7,'c',55
go
select * from t a
where not exists(select 1 from t where aa=a.aa and bb>a.bb)
/**
id AA BB
----------- ---- -----------
1 a 10
4 b 11
6 c 540
(3 行受影响)
**/------其他解决方案--------------------
select AA,max(BB) as BB
from T
group by AA
------其他解决方案--------------------
带上ID的话 用：
select id,AA,BB
from T as A
where exists(select 1 from (select AA,max(BB) as BB
from T
group by AA) as B where A.AA=B.AA and A.BB=B.BB)
------其他解决方案--------------------
select * from t a
where not exists(select 1 from t where aa=a.aa and bb>a.bb)
------其他解决方案--------------------
LS的 我刚试过啊 3楼的
结果集里面很多重复的a b c
------其他解决方案--------------------
是否存在相同的aa，最大值bb也有重复？
try
select * from t a
where not exists(select 1 from t where aa=a.aa and (bb>a.bb or (bb=a.bb and id<t.id)))
------其他解决方案--------------------
--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([id] INT,[AA] VARCHAR(1),[BB] INT)
INSERT #tb
SELECT 1,'a',10 UNION ALL
SELECT 2,'a',8 UNION ALL
SELECT 3,'a',6 UNION ALL
SELECT 4,'b',11 UNION ALL
SELECT 5,'b',3 UNION ALL
SELECT 6,'c',540 UNION ALL
SELECT 7,'c',55 UNION ALL
SELECT 8,'c',540