表如下:
id ac ac_value
1 ww 7
2 ww 12
3 ss 9
4 ss 6
5 ss 26
6 dd 3最后得到的结果是每ac_value字段中的最大值,结果如下:
2 ww 12
5 ss 26
6 dd 3
请问这个SQL语句这么写啊?
id ac ac_value
1 ww 7
2 ww 12
3 ss 9
4 ss 6
5 ss 26
6 dd 3最后得到的结果是每ac_value字段中的最大值,结果如下:
2 ww 12
5 ss 26
6 dd 3
请问这个SQL语句这么写啊?
where a.id>b.id and a.id>c.id and
b.ac>a.ac and b.ac>c.ac and
c.ac_value>a.ac_value and c.ac_value>b.ac_value
where
(a.ac_value> b.ac_value and a.ac=b.ac and a.id>b.id) or(select count(*) from ac_tb c where c.ac=a.ac)=1 group by a.id ,a.ac, a.ac_value
性能低了些你可以改为union
select d.* from
(select max(a.id) as id from ac_tb a , ac_tb b
where (a.ac_value> b.ac_value and a.ac=b.ac or(select count(*) from ac_tb c where c.ac=a.ac)=1 )
group by a.ac) c join ac_tb d on c.id=d.id
这个应该没问题了
create table myTable (
id int,
ac char(10),
ac_value int
)goinsert into myTable select 1,'ww',7
union all select 2,'ww',12
union all select 3,'ss',9
union all select 4,'ss',6
union all select 5,'ss',26
union all select 6,'dd',3gocreate function dbo.get(@value int)
returns int
as
begin
declare @rv int
set @rv=0
select @rv=id from myTable where ac_value=@value
return @rv
end
goselect dbo.get(max(ac_value)) as id,ac,max(ac_value) as max_value from myTable group by ac order by iddrop function get
drop table myTable/***
(所影响的行数为 6 行)id ac max_value
----------- ---------- -----------
2 ww 12
5 ss 26
6 dd 3(所影响的行数为 3 行)
***/