关于一条SQL语句的修改,紧急求救!在线等

vgerz 2008-11-04 02:55:09
各位大哥大姐,在下先有礼了!以下是这条SQL
select v_bom_parent_rpt.InvCode as 母件编码,
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
order by v_bom_parent_rpt.InvCode
这段代码没有错,只是我想实现取“子件编码”相同,并且“结算时间”最大时对应的所有行,请各位大牛帮帮我,小弟十分感谢。
我是新人,所以没分,希望大家能帮帮忙
...全文
152 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
vgerz 2008-11-08
谢谢大家来了,这两天有事没上来,不好意思。我这就结贴。。
回复
等不到来世 2008-11-05
用临时表:
select v_bom_parent_rpt.InvCode as 母件编码, 
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计
into #temp
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode

select * from #temp a
where not exists(select 1 from #temp where 母件编码=a.母件编码 and 子件编码=a.子件编码 and 供应商代码=a.供应商代码 and 结算时间>a.结算时间)
回复
zjcxc 2008-11-05
join 的表太多, 不容易看清楚表之间的关系, 这样好了

select v_bom_parent_rpt.InvCode as 母件编码, 
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
WHERE NOT EXISTS( -- 过滤条件
SELECT * FROM(
select
PurSettleVouchs.cInvCode,
PurSettleVouch.dsvdate
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
) AA
WHERE AA.cInvCode = PurSettleVouchs.cInvCode
AND AA.dsvdate > PurSettleVouch.dsvdate)
order by v_bom_parent_rpt.InvCode
回复
viva369 2008-11-05
[Quote=引用 6 楼 vgerz 的回复:]
to:zjcxc
谢谢邹大牛,但是运行的时候提示
列名‘dsvdate’无效。
[/Quote]

牛!!!^_^
回复
vgerz 2008-11-05
可能是我没表达清楚吧,我是想根据列出的这条查询结果来再次查询出子件编码相等时,结算时间最大时所对应的所有行。有劳各位了
回复
vgerz 2008-11-05
to:zjcxc
谢谢邹大牛,但是运行的时候提示
列名‘dsvdate’无效。
回复
ilovewalk 2008-11-05
[Quote=引用 2 楼 viva369 的回复:]
select aa.* from #t as aa
inner join
(select 子件编码,max(结算时间) 结算时间 from #t group by 子件编码) as bb
on aa.子件编码 = bb.子件编码 and aa.结算时间 = bb.结算时间
[/Quote]
R?
回复
zjcxc 2008-11-05
select v_bom_parent_rpt.InvCode as 母件编码, 
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
WHERE NOT EXISTS( -- 过滤条件
SELECT * FROM PurSettleVouchs AA
WHERE AA.cInvCode = PurSettleVouchs.cInvCode
AND AA.dsvdate > PurSettleVouchs.dsvdate
AND AA.PSVID=PurSettleVouchs.PSVID)
order by v_bom_parent_rpt.InvCode
回复
lihuanmei 2008-11-05
select v_bom_parent_rpt.InvCode as 母件编码, 
Inventory.cInvName as 母件名称,
Inventory.cInvStd as 母件型号,
PurSettleVouchs.cInvCode as 子件编码,
v1.cInvName as 子件名称,v1.cInvStd as 子件型号,
v1.cVenCode as 供应商代码,
PurSettleVouch.dsvdate as 结算时间,
PurSettleVouchs.iSVCost/0.83 as 单价,
v_bom_opcomponent_rpt.BaseQtyN as 基本用量,
PurSettleVouchs.iSVCost/0.83*v_bom_opcomponent_rpt.BaseQtyN as 小计 into #
from Inventory RIGHT JOIN (v_bom_parent_rpt LEFT JOIN (v_bom_opcomponent_rpt LEFT JOIN (PurSettleVouch RIGHT JOIN (PurSettleVouchs LEFT JOIN Inventory v1 on PurSettleVouchs.cInvCode=v1.cInvCode) on PurSettleVouch.PSVID=PurSettleVouchs.PSVID) on v_bom_opcomponent_rpt.InvCode=PurSettleVouchs.cInvCode) on v_bom_parent_rpt.BomId=v_bom_opcomponent_rpt.BomId) on Inventory.cInvCode=v_bom_parent_rpt.InvCode
order by v_bom_parent_rpt.InvCode
select max(结算时间),子件编码 from # a where #.子件编码=a.子件编码
回复
viva369 2008-11-05
select aa.* from #t as aa
inner join
(select 子件编码,max(结算时间) 结算时间 from #t group by 子件编码) as bb
on aa.子件编码 = bb.子件编码 and aa.结算时间 = bb.结算时间
回复
vgerz 2008-11-05
用临时表或者存储过程都可以的,再次感谢
回复
相关推荐
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2008-11-04 02:55
社区公告
暂无公告