急求一条sql语句!!! 一.贷物进出明细表ID 物贷物 进贷量 出贷量 当前库存量1 A 10 0 102 A 0 5 53 A 0 2 34 B 12 0 125 B 10 0 22功能需求:现在只需要每种贷物的最后一条记录,效果如: 3 A 0 2 35 B 10 0 22用一条sql语句怎么实现? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select * from tb a where not exists(select 1 from tb where id>a.id and 物贷物=a.物贷物) select a.* from tb as ainner join(select max(id) as id from tb where 物贷物=a.物贷物) on a.id=b.id order by a.id SELECT * FROM 贷物进出明细表 A WHERE NOT EXISTS(SELECT 1 FROM 贷物进出明细表 WHERE 贷物=A.贷物 AND ID>A.ID) create table tb(ID int,物贷物 varchar(10), 进贷量 int,出贷量 int,当前库存量 int)insert into tb select 1, 'A' ,10, 0, 10 insert into tb select 2, 'A' ,0 ,5, 5 insert into tb select 3, 'A' ,0 ,2 ,3 insert into tb select 4 ,'B', 12, 0, 12 insert into tb select 5, 'B', 10 ,0 ,22 select * from tb T where not exists(select 1 from tb where 物贷物 = T.物贷物 and id >T.id)/*ID 物贷物 进贷量 出贷量 当前库存量 -----------------------------------3 A 0 2 35 B 10 0 22*/drop table tb 改下:select a.* from [贷物进出明细表] as a inner join ( select max(id) as id from [贷物进出明细表] group by 物贷物 ) as b on a.id=b.id select a.*from table ainner join (select 物贷物,max(id) as 'id' from table group by 物贷物) b on a.id = b.id --如果ID是流水号,或者自增列.SELECT * FROM [贷物进出明细表] A WHERE NOT EXISTS(SELECT 1 FROM [贷物进出明细表] WHERE [贷物]=A.[贷物] AND [ID]>A.[ID]) 谢谢各位!我自己也写了一条,请各位帮参考下:Select * from [表名] where ID in (select max(ID) from [表名] group by [分类]) 小弟求教,望各位指点 关系代数中模式的投影指的是? 如何在sql server的查询中动态生成列序号(很急的问题) 表drop不掉,怎么回事? 定时执行问题请高手指点 (紧急呼救!!!!!!!!!!) 现在就告诉我答案 删除记录大问题!!!!!!!!! 请教sql问题,谢谢大家 100分求解问题。 MySQL设置问题 SQL server不存在或拒绝访问 (急)如何获取数据库的datetime值的年月日值
select * from tb a where not exists(select 1 from tb where id>a.id and 物贷物=a.物贷物)
inner join
(
select max(id) as id from tb where 物贷物=a.物贷物
) on a.id=b.id order by a.id
SELECT * FROM 贷物进出明细表 A WHERE NOT EXISTS(SELECT 1 FROM 贷物进出明细表 WHERE 贷物=A.贷物 AND ID>A.ID)
insert into tb select 2, 'A' ,0 ,5, 5
insert into tb select 3, 'A' ,0 ,2 ,3
insert into tb select 4 ,'B', 12, 0, 12
insert into tb select 5, 'B', 10 ,0 ,22 select * from tb T where not exists(select 1 from tb where 物贷物 = T.物贷物 and id >T.id)
/*
ID 物贷物 进贷量 出贷量 当前库存量
-----------------------------------
3 A 0 2 3
5 B 10 0 22*/drop table tb
改下:select a.* from [贷物进出明细表] as a inner join
( select max(id) as id from [贷物进出明细表] group by 物贷物 ) as b on a.id=b.id
from table a
inner join
(select 物贷物,max(id) as 'id' from table group by 物贷物) b on a.id = b.id
SELECT * FROM [贷物进出明细表] A WHERE NOT EXISTS(SELECT 1 FROM [贷物进出明细表] WHERE [贷物]=A.[贷物] AND [ID]>A.[ID])
我自己也写了一条,请各位帮参考下:
Select * from [表名] where ID in
(
select max(ID) from [表名]
group by [分类]
)