求最大ID记录
autoid cinvcode cwhcode cost
123 A 001 10.5
135 A 001 10.8
189 A 001 30.9求同一cinvcode,cwhcode,同时AUTOID最大的cost值的记录
该怎么写最简单?如下写法不对?
select max(autoid) as autoid ,cinvcode,cwhcode,cost
from IA_Sub where isnull(cost,0)<>0
group by cinvcode,cwhcode,ioutcost
autoid cinvcode cwhcode cost
123 A 001 10.5
135 A 001 10.8
189 A 001 30.9求同一cinvcode,cwhcode,同时AUTOID最大的cost值的记录
该怎么写最简单?如下写法不对?
select max(autoid) as autoid ,cinvcode,cwhcode,cost
from IA_Sub where isnull(cost,0)<>0
group by cinvcode,cwhcode,ioutcost
select cinvcode,
cwhcode,
max(autoid) 'max_autoid',
max(case when autoid=max(autoid) then cost else 0 end) 'cost'
from [表名]
group by cinvcode,cwhcode
from [表名] a
inner join
(select cinvcode,
cwhcode,
max(autoid) 'max_autoid'
from [表名]
group by cinvcode,cwhcode) b on a.cinvcode=b.cinvcode and a.cwhcode=b.cwhcode and a.autoid=b.max_autoid
这个条件怎么放?
isnull(cost,0)<>0
select a.autoid, a.cinvcode, a.cwhcode, a.cost
from [表名] a
inner join
(select cinvcode,
cwhcode,
max(autoid) 'max_autoid'
from [表名]
where isnull(cost,0)<>0
group by cinvcode,cwhcode) b
on a.cinvcode=b.cinvcode and a.cwhcode=b.cwhcode and a.autoid=b.max_autoid
select a.autoid, a.cinvcode, a.cwhcode, a.cost
from [表名] a
inner join
(select cinvcode,
cwhcode,
max(autoid) 'max_autoid'
from [表名]
where isnull(cost,0)<>0
group by cinvcode,cwhcode) b
on a.cinvcode=b.cinvcode and a.cwhcode=b.cwhcode and a.autoid=b.max_autoid
高手……面面俱到