请问这样的SQL怎么写
本帖最后由 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
--------------开始查询--------------------------
--1
SELECT * FROM #tb AS T WHERE NOT EXISTS(SELECT 1 FROM #tb WHERE [AA]=t.[AA] AND [BB]>t.[BB] )
----------------结果----------------------------
/*
idAABB
1a10
4b11
6c540
8c540
*/
--2
SELECT * FROM
(
SELECT *,row_id=ROW_NUMBER() OVER(PARTITION BY [AA] ORDER BY [BB] DESC ) FROM #tb
) t WHERE row_id=1
----------------结果----------------------------
/*
idAABBrow_id
1a101
4b111
6c5401
*/
[其他解释]
with yy
as
(select *,ROW=ROW_NUMBER()over (PARTITION by AA order by bb desc) from t)
select ID,aa,bb from yy where ROW=1
/*ID aa bb
----------- ---- -----------
1 a 10
4 b 11
6 c 540
(3 row(s) affected)
[其他解释]
有相同的AA 最大值BB也有重复 会有时间字段来排序
其实只想取AA中不重复字段的最大值
[其他解释]
看7楼第二个对吗？
不对请给出你想要的结果。
[其他解释]
select * from t where select didtinct AA and max(BB)
[其他解释]
这个也可以
[其他解释]
select max(id),DISTINCT AA,max(BB) from T group by AA
应该是 最简单的了
[其他解释]
同意楼上的,简单
[其他解释]
null