表结构:
C1 C2 C3
101 w 1
101 w 2
102 w 7
102 w 8
103 w 3
103 w 5如何得到下面的结果集(在C1相等的情况下,SELECT出C3最大值的结果集):
101 w 2
102 w 8
103 w 5
C1 C2 C3
101 w 1
101 w 2
102 w 7
102 w 8
103 w 3
103 w 5如何得到下面的结果集(在C1相等的情况下,SELECT出C3最大值的结果集):
101 w 2
102 w 8
103 w 5
declare @tb table([C1] int,[C2] nvarchar(1),[C3] int)
Insert @tb
select 101,N'w',1 union all
select 101,N'w',2 union all
select 102,N'w',7 union all
select 102,N'w',8 union all
select 103,N'w',3 union all
select 103,N'w',5select * from @tb t where not exists(select 1 from @tb where c1 =t.c1 and c3 > t.c3)
/*
C1 C2 C3
----------- ---- -----------
101 w 2
102 w 8
103 w 5
*/