在线求一条SQL语句

[lsp69]

/*
ChID  aa    bb      cc      dd        ee
22	  A1	0.2		0	  10.000	8.000
22	  A1	0.3		0	  10.000	4.000
22	  A1	0.5		0.1	  5.000	    4.000
22	  A1	0.6		0.1	  15.000	2.000
33	  A2	0.2		0	  11.000	8.000
34	  A3	0.3		0	  9.000	    8.000
35	  A4	0.5		0.1	  12.000	10.000
35	  A4	0.8		0	  15.000	14.000
..
..
..
*/
要得出这样的结果,即按aa列分组，取出bb为最小值的记录:
/*
ChID  aa    bb      cc      dd        ee
22	  A1	0.2		0	  10.000	8.000
33	  A2	0.2		0	  11.000	8.000
34	  A3	0.3		0	  9.000	    8.000
35	  A4	0.5		0.1	  12.000	10.000
*/

用一条sql语句怎么写？

[浩方软件HFWMS]

没有人回复啊

[叶子]

--> 测试数据：@T
declare @T table([ChID] int,[aa] varchar(2),[bb] numeric(2,1),[cc] numeric(2,1),[dd] numeric(5,3),[ee] numeric(5,3))
insert @T
select 22,'A1',0.2,0,10.000,8.000 union all
select 22,'A1',0.3,0,10.000,4.000 union all
select 22,'A1',0.5,0.1,5.000,4.000 union all
select 22,'A1',0.6,0.1,15.000,2.000 union all
select 33,'A2',0.2,0,11.000,8.000 union all
select 34,'A3',0.3,0,9.000,8.000 union all
select 35,'A4',0.5,0.1,12.000,10.000 union all
select 35,'A4',0.8,0,15.000,14.000
 
select * from @T t
where [bb]=(select min([bb]) from @T where [aa]=t.[aa])
 
/*
ChID        aa   bb                                      cc                                      dd                                      ee
----------- ---- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
22          A1   0.2                                     0.0                                     10.000                                  8.000
33          A2   0.2                                     0.0                                     11.000                                  8.000
34          A3   0.3                                     0.0                                     9.000                                   8.000
35          A4   0.5                                     0.1                                     12.000                                  10.000
*/

[叶子]

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go
   
   
--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)
   
方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID
   
方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)
   
方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 
   
方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)
   
方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0
   
方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)
   
方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)
   
方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)
   
--SQL2005:
   
方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID
   
方法11:
   
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1
   
生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1
   
(2 行受影响)
*/
   
   
--II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)
   
方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID
   
方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID
   
方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 
   
方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)
   
方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0
   
方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)
   
方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)
   
方法9(注:ID为唯一时可用):
select * from #T a where ID in(select max(ID) from #T group by Name)
   
--SQL2005:
   
方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID
   
方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1
   
生成结果2:
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2
   
(2 行受影响)
*/

[jmx123456789]

.....版主大人 V5

[Mirror然]

N个方法 全列了