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',20select 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
insert into tb(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',20select c1,c2,c3,c4 from ( select *, ROW_NUMBER() over(PARTITION by c2,c3 order by c1 desc) as rownum from tb )t where rownum = 1 /* c1 c2 c3 c4 2 a a 20 3 a b 12 2 b a 20 */
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t CREATE TABLE t ( C1 int, C2 nvarchar(50), C3 nvarchar(50), C4 int ) GO insert into t(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 GO SELECT * FROM t AS a WHERE c1 IN (SELECT MAX(c1) FROM t WHERE c2=a.c2 AND c3=a.c3 GROUP BY c2,c3)
insert into #tb(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',20select 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
调整了下顺序..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
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',20select 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
--gocreate table tb
(
C1 int,
C2 nvarchar(50),
C3 nvarchar(50),
C4 int
)
insert into tb(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',20select c1,c2,c3,c4
from
(
select *,
ROW_NUMBER() over(PARTITION by c2,c3 order by c1 desc) as rownum
from tb
)t
where rownum = 1
/*
c1 c2 c3 c4
2 a a 20
3 a b 12
2 b a 20
*/
DROP TABLE t
CREATE TABLE t
(
C1 int,
C2 nvarchar(50),
C3 nvarchar(50),
C4 int
)
GO
insert into t(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
GO
SELECT * FROM t AS a
WHERE c1 IN (SELECT MAX(c1) FROM t
WHERE c2=a.c2 AND c3=a.c3
GROUP BY c2,c3)
create table #tb
(
C1 int,
C2 nvarchar(50),
C3 nvarchar(50),
C4 int
)
insert into #tb(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',20select 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
(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