有如下表:
--id-----a----------b----------c----------d-----
1 s e t 6
2 s e t 7
3 s e t 5
4 v f n 10
5 v f n 11
6 v f n 12
求一查询:
按 a b c 分组,取出每组中 d 值较大的行的id
--id-----a----------b----------c----------d-----
1 s e t 6
2 s e t 7
3 s e t 5
4 v f n 10
5 v f n 11
6 v f n 12
求一查询:
按 a b c 分组,取出每组中 d 值较大的行的id
on a.a=b.a and a.b=b.b and a.c = b.c and a.d=b.d
如取最大:select top 1 ID from 表 order by d desc
where not exists(select * from tb where a=a.a and b=a.b and c = a.c and d>a.d)
INNER JOIN
(SELECT a+','+b+','+c newf,MAX(d) md FROM tb GROUP BY a+','+b+','+c) y
ON a+','+b+','+c=newf AND d=md或者
select id from tb a
where 1>(select count(1) from tb b where b.a=a.a and b.b=a.b and b.c=a.c and b.d>a.d)
或者用not exists..
方法很多,不一一写了.
select '1','s','e','t','6' union all
select '2','s','e','t','7' union all
select '3','s','e','t','5' union all
select '4','v','f','n','10' union all
select '5','v','f','n','11' union all
select '6','v','f','n','12'goselect (select id from tb1 where checksum(a.a,a.b,a.c)=checksum(a,b,c) and d=max(a.d)),a,b,c,max(d) from tb1 a
group by a,b,cgodrop table tb1
insert into @tb
select 1,'s','e','t',6 union all
select 2,'s','e','t',7 union all
select 3,'s','e','t',5 union all
select 4,'v','f','n',10 union all
select 5,'v','f','n',11 union all
select 6,'v','f','n',12select id from @tb a where exists(select * from @tb group by a,b,c having max(d)=a.d)
insert into @t
select 1,'s','e','t',6
union all
select 2,'s','e','t',7
union all
select 3,'s','e','t',5
union all
select 4,'v','f','n',10
union all
select 5,'v','f','n',11
union all
select 6,'v','f','n',12select t1.* from @t t1 inner join
(select a,b,c,max(d) as d from @t group by a,b,c ) t2
on t1.d=t2.d/*The result:*/id a b c d
----------- ---------- ---------- ---------- ----------
2 s e t 7
6 v f n 12 (2 row(s) affected)
---------------------------------------select t1.* from @t t1 left join
(select a,b,c,max(d) as d from @t group by a,b,c ) t2
on t1.d=t2.d
where t2.d is null/*the result*/
id a b c d
----------- ---------- ---------- ---------- ----------
1 s e t 6
3 s e t 5
4 v f n 10
5 v f n 11 (4 row(s) affected)
insert into @tb
select 1,'s','e','t',6 union all
select 2,'s','e','t',7 union all
select 3,'s','e','t',5 union all
select 4,'v','f','n',10 union all
select 5,'v','f','n',11 union all
select 6,'v','f','n',12select [id] from @tb
where d in
(select max(d) d from @tb group by a,b,c)