表
ID int Cnt Int GrpID int
1 1 1
9 1 1
2 3 1
3 2 2
4 1 2
8 1 3 结果
ID Cnt GrpID
1 1 1
4 1 2
8 1 3按照GrpID分组查询,每组查询出Cnt最小的唯一一条记录。如何写语句?
ID int Cnt Int GrpID int
1 1 1
9 1 1
2 3 1
3 2 2
4 1 2
8 1 3 结果
ID Cnt GrpID
1 1 1
4 1 2
8 1 3按照GrpID分组查询,每组查询出Cnt最小的唯一一条记录。如何写语句?
select t.* from tb t where not exists (select 1 from tb where GrpID = t.GrpID and Cnt < t.Cnt)
declare @tb table (ID int,Cnt Int,GrpID int)
insert into @tb
select 1,1,1 union all
select 9,1,1 union all
select 2,3,1 union all
select 3,2,2 union all
select 4,1,2 union all
select 8,1,3
select MIN(ID),MIN(Cnt),GrpID from @tb group by GrpID
------------------------------------
GrpID
----------- ----------- -----------
1 1 1
3 1 2
8 1 3
(
id int,
cnt int,
grpid int
)
insert into tb values(1, 1, 1)
insert into tb values(9 ,1, 1)
insert into tb values(2 ,3, 1)
insert into tb values(3 ,2 ,2)
insert into tb values(4 ,1 ,2)
insert into tb values(8 ,1 ,3)select * from tb t where not exists (select 1 from tb where GrpId = t.GrpId and cnt<t.Cnt)
/*
id,cnt,grpid
1,1,1
9,1,1
4,1,2
8,1,3(4 行受影响)
insert into tb values(1 ,1 ,1)
insert into tb values(9 ,1 ,1)
insert into tb values(2 ,3 ,1)
insert into tb values(3 ,2 ,2 )
insert into tb values(4 ,1 ,2 )
insert into tb values(8 ,1 ,3 )
goselect t.* from tb t where Cnt = (select min(Cnt) from tb where GrpID = t.GrpID)
/*
ID Cnt GrpID
----------- ----------- -----------
1 1 1
9 1 1
4 1 2
8 1 3(所影响的行数为 4 行)
*/select t.* from tb t where not exists (select 1 from tb where GrpID = t.GrpID and Cnt < t.Cnt )
/*
ID Cnt GrpID
----------- ----------- -----------
1 1 1
9 1 1
4 1 2
8 1 3(所影响的行数为 4 行)
*/select t.* from tb t where not exists (select 1 from tb where GrpID = t.GrpID and (Cnt < t.Cnt or (Cnt = t.Cnt and ID < t.ID)) )
/*
ID Cnt GrpID
----------- ----------- -----------
1 1 1
4 1 2
8 1 3(所影响的行数为 3 行)
*/drop table tb
go
create table [TB] (ID int,Cnt int,GrpID int)
insert into [TB]
select 1,1,1 union all
select 9,1,1 union all
select 2,3,1 union all
select 3,2,2 union all
select 4,1,2 union all
select 8,1,3select * from [TB];WITH TT as(
SELECT id,cnt,grpid,ROW_NUMBER() OVER(PARTITION BY grpid ORDER BY cnt ASC) AS Num
FROM dbo.TB )SELECT id,cnt,grpid FROM TT WHERE num =1
/*
id cnt grpid
1 1 1
4 1 2
8 1 3*/
s_id,
GrpID
FROM st t
WHERE not exists (
select 1
from st
where GrpID = t.GrpID and Cnt < t.Cnt
)
GROUP BY GrpID, s_idORDER BY grpid
insert into tb values(1 ,1 ,1)
insert into tb values(9 ,1 ,1)
insert into tb values(2 ,3 ,1)
insert into tb values(3 ,2 ,2 )
insert into tb values(4 ,1 ,2 )
insert into tb values(8 ,1 ,3 )
goselect ID,Cnt,GrpID from (
select ID,Cnt,GrpID,row_number() over(partition by GrpID order by Cnt asc) as rownum from tb) as a
where a.rownum<2