ID AID TYPE
1 1 1
2 1 2
3 1 3
4 1 4
5 2 2
6 2 4
7 3 2
8 4 4
以AID 以相同的分组
TYPE 以 1,2,3 为一组 1,2,3随便显示哪个
4 为 一组来分
最后结果
ID AID TYPE
1 1 1(2,3)随便哪个数字都可以
4 1 4
5 2 2
6 2 4
7 3 2
8 4 4
1 1 1
2 1 2
3 1 3
4 1 4
5 2 2
6 2 4
7 3 2
8 4 4
以AID 以相同的分组
TYPE 以 1,2,3 为一组 1,2,3随便显示哪个
4 为 一组来分
最后结果
ID AID TYPE
1 1 1(2,3)随便哪个数字都可以
4 1 4
5 2 2
6 2 4
7 3 2
8 4 4
if not object_id('Tempdb..#1') is null
drop table #1
Go
Create table #1([ID] int,[AID] int,[TYPE] int)
Insert #1
select 1,1,1 union all
select 2,1,2 union all
select 3,1,3 union all
select 4,1,4 union all
select 5,2,2 union all
select 6,2,4 union all
select 7,3,2 union all
select 8,4,4
Go
Select *
from #1 as a
where ID=(select top 1 ID from #1 where [AID]=a.[AID] and (([TYPE]<4 and a.TYPE<4) or [TYPE]=a.[TYPE]))
/*
ID AID TYPE
1 1 1
4 1 4
5 2 2
6 2 4
7 3 2
8 4 4
*/
create table #t(id int identity(1,1),aid int,type int)
insert into #t
select 1 as aid,1 as type union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,2 union all
select 2,4 union all
select 3,2 union all
select 4,4 ;with t as
(
select *, [组别] = (case when type in(1,2,3) then cast(aid as varchar) + '-1组' else cast(aid as varchar) + '-2组' end)
from #t
)select id,aid,type from t a
where not exists
(select 1 from t where 组别 = a.组别 and type < a.type)
drop table #t-------------------------------------------------------------
(8 行受影响)
id aid type
----------- ----------- -----------
1 1 1
4 1 4
5 2 2
6 2 4
7 3 2
8 4 4(6 行受影响)