请教这个SQL语句怎么写？
列：c1,c2,c3,c4
主键：c1+c2+c3
数据：
1,a,a,10
2,a,a,20
1,a,b,15
2,a,b,10
3,a,b,12
1,b,a,10
2,b,a,20
...
要求结果：各键值c1为最大值的行(黑体行)
sql
[解决办法]
select * from tb t where not exists(select 1 from tb where c2=t.c2 and c3=t.c3 and c1>t.c1)
declare @tab table
(
C1 int,
C2 nvarchar(50),
C3 nvarchar(50),
C4 int
)
insert into @tab(C1,C2,C3,C4)
select 1,'a','a',10
union all
select 2,'a','a',20
union all
select 1,'a','b',15
union all
select 2,'a','b',10
union all
select 3,'a','b',12
union all
select 1,'b','a',10
union all
select 2,'b','a',20
select C1,C2,C3,C4 from
(
select row_number() over(partition by C2,C3 order by C1 desc) as pg, * from @tab
) t where pg=1
select a.* from #tb a right join
(select max(C1) as C1,C2,C3 from #tb b group by C2,C3 )c
on a.C1=c.C1 and a.C2=c.C2 and a.C3=c.C3 order by C2 asc
