如果只要一条的话 select top 1 a.ID,b.cinvcode,b.iunitcost from RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01' Order by a.Id desc
select top 1 a.ID,b.cinvcode,b.iunitcost from RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01' Order by a.Id desc
Select * From (select a.ID,b.cinvcode,b.iunitcost from RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01') a where not exists (Select * From (select a.ID,b.cinvcode,b.iunitcost from RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01') b where b.id>a.id)
select top 1 a.ID,b.cinvcode,b.iunitcost from RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01' order by a.ID
select top 1 a.ID,b.cinvcode,b.iunitcost from RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01' order by a.ID
create table #RR ( id int, cinvcode varchar(20), iunitcost int ) insert into #RR select 1,'b01',10 union all select 2,'b01',9 union all select 3,'a01',127 union all select 4,'a01',12select * from #RR R where not exists(select * from #RR where R.cinvcode=cinvcode and R.iunitcost>iunitcost)
select a.ID,b.cinvcode,b.iunitcost from RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01' and exists(select 1 from ( select top 1 a.ID,b.cinvcode,b.iunitcost from RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01' ) c where c.cinvocde = b.cinvcode and a.ID>c.ID )
select top 1 a.ID,b.cinvcode,b.iunitcost
from
RdRecord a inner join RdRecords b on a.id=b.id
where a.cVouchType='01'
Order by a.Id desc
select top 1 a.ID,b.cinvcode,b.iunitcost
from
RdRecord a inner join RdRecords b on a.id=b.id
where a.cVouchType='01'
Order by a.Id desc
Select *
From
(select a.ID,b.cinvcode,b.iunitcost
from
RdRecord a inner join RdRecords b on a.id=b.id
where a.cVouchType='01') a
where not exists (Select * From (select a.ID,b.cinvcode,b.iunitcost from
RdRecord a inner join RdRecords b on a.id=b.id where a.cVouchType='01') b where b.id>a.id)
from
RdRecord a inner join RdRecords b on a.id=b.id
where a.cVouchType='01' order by a.ID
是想得到cinvcode相同ID最大的记录.例如:
/*原数据
id cinvcode iunitcost
1 b01 10
2 b01 9
3 a01 127
4 a01 12
*//*要想得到的结果数据:id cinvcode iunitcost
2 b01 9
4 a01 12
*/
select top 1 a.ID,b.cinvcode,b.iunitcost
from
RdRecord a inner join RdRecords b on a.id=b.id
where a.cVouchType='01'
order by a.ID
(
id int,
cinvcode varchar(20),
iunitcost int
)
insert into #RR select 1,'b01',10
union all select 2,'b01',9
union all select 3,'a01',127
union all select 4,'a01',12select * from #RR R where not exists(select * from #RR where R.cinvcode=cinvcode and R.iunitcost>iunitcost)
----------- -------------------- -----------
2 b01 9
4 a01 12(2 行受影响)
select a.ID,b.cinvcode,b.iunitcost
from
RdRecord a inner join RdRecords b on a.id=b.id
where a.cVouchType='01' and exists(select 1 from
( select top 1 a.ID,b.cinvcode,b.iunitcost
from
RdRecord a inner join RdRecords b on a.id=b.id
where a.cVouchType='01'
) c where c.cinvocde = b.cinvcode and a.ID>c.ID )
提示:列名 'cinvocde' 无效。