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

[vgerz]

各位大哥大姐，在下先有礼了！以下是这条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
这段代码没有错，只是我想实现取“子件编码”相同，并且“结算时间”最大时对应的所有行，请各位大牛帮帮我，小弟十分感谢。
我是新人，所以没分，希望大家能帮帮忙

[vgerz]

用临时表或者存储过程都可以的，再次感谢

[viva369]

select aa.* from #t as aa
inner join
(select 子件编码,max(结算时间) 结算时间 from #t group by 子件编码) as bb
on aa.子件编码 = bb.子件编码 and aa.结算时间 = bb.结算时间

[lihuanmei]

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.子件编码

[zjcxc]

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 

[ilovewalk]

[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?

[vgerz]

to:zjcxc
谢谢邹大牛，但是运行的时候提示
列名‘dsvdate’无效。

[vgerz]

可能是我没表达清楚吧，我是想根据列出的这条查询结果来再次查询出子件编码相等时，结算时间最大时所对应的所有行。有劳各位了

[viva369]

[Quote=引用 6 楼 vgerz 的回复:]
to:zjcxc
谢谢邹大牛，但是运行的时候提示
列名‘dsvdate’无效。
[/Quote]

牛！！！^_^

[zjcxc]

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 

[等不到来世]

用临时表：

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.结算时间)

[vgerz]

谢谢大家来了，这两天有事没上来，不好意思。我这就结贴。。