SELECT
PU_AppVouch.cCode AS [采购请购单号],PU_AppVouch.dDate AS [采购请购日期],
PU_AppVouch.cMaker AS [请购人],
pu_appvouchs.cInvCode AS [存货编码],
PO_Pomain.cPOID AS [采购订单号],PO_Pomain.dPODate AS [订单日期],
PO_Podetails.cInvCode AS [订单存货编码],PU_ArrivalVouch.cCode AS [采购到货单号],
PU_ArrivalVouch.dDate AS [到货日期],PU_ArrivalVouchs.cInvCode AS [到货存货编码] ,
RdRecord.cCode as [收发单据号] ,RdRecord.dDate as [入库日期] ,
RdRecord.cOrderCode as [采购订单号] , RdRecord.cARVCode as [采购到货单号] ,
RdRecords.cInvCode as [入库存货编码],PO_Pomain.cPOID AS [采购订单号],PO_Pomain.dPODate AS [采购订单日期],PO_Podetails.iQuantity AS [采购订单数量],PU_ArrivalVouchs.iQuantity AS [采购到货数量],RdRecords.iQuantity AS [入库数量]
FROM ( po_pomain JOIN PO_Podetails ON PO_Pomain.POID = PO_Podetails.POID )
left join ( PU_ArrivalVouch INNER JOIN PU_ArrivalVouchs ON PU_ArrivalVouch.ID = PU_ArrivalVouchs.ID )
on PO_Podetails.ID = pu_arrivalvouchs.iPOsID
left join ( rdrecord inner join rdrecords on rdrecord.id =rdrecords.id )
on pu_arrivalvouchs.autoid = rdrecords.iarrsid
left join ( pu_appvouch inner join pu_appvouchs on pu_appvouchs.id =pu_appvouch.id )
on pu_appvouchs.autoid = po_podetails.iappids
PU_AppVouch 是请购单主表
PU_AppVouchS 是请购单子表
PO_Pomain 采购订单主表
PO_Podetails采购订单子表
PU_ArrivalVouch到货单主表
PU_ArrivalVouchS 到货单子表
RdRecord 入库主表
RdRecords 入库子表 我要的结果是,请购这个商品后,能在这张表中可以看到请购品名,数量,订单日期及数量,到货日期,数量,入库数量并且写请购单,没有订,也会显示,有请有订没有到货和入库也显示,依次下推.这样的一个表.但现在这个代码pu_appvouchs.autoid = po_podetails.iappids中使用的是LEFT JOIN连接,这个执行完是请购部分数据都为空(其余查询数据结果正确).因为我要从请订到入库四个步骤.所以就是得改成RIGHT JOIN后,查询与现在结果不相符了.请专家给予指导.谢谢
PU_AppVouch.cCode AS [采购请购单号],PU_AppVouch.dDate AS [采购请购日期],
PU_AppVouch.cMaker AS [请购人],
pu_appvouchs.cInvCode AS [存货编码],
PO_Pomain.cPOID AS [采购订单号],PO_Pomain.dPODate AS [订单日期],
PO_Podetails.cInvCode AS [订单存货编码],PU_ArrivalVouch.cCode AS [采购到货单号],
PU_ArrivalVouch.dDate AS [到货日期],PU_ArrivalVouchs.cInvCode AS [到货存货编码] ,
RdRecord.cCode as [收发单据号] ,RdRecord.dDate as [入库日期] ,
RdRecord.cOrderCode as [采购订单号] , RdRecord.cARVCode as [采购到货单号] ,
RdRecords.cInvCode as [入库存货编码],PO_Pomain.cPOID AS [采购订单号],PO_Pomain.dPODate AS [采购订单日期],PO_Podetails.iQuantity AS [采购订单数量],PU_ArrivalVouchs.iQuantity AS [采购到货数量],RdRecords.iQuantity AS [入库数量]
FROM ( po_pomain JOIN PO_Podetails ON PO_Pomain.POID = PO_Podetails.POID )
left join ( PU_ArrivalVouch INNER JOIN PU_ArrivalVouchs ON PU_ArrivalVouch.ID = PU_ArrivalVouchs.ID )
on PO_Podetails.ID = pu_arrivalvouchs.iPOsID
left join ( rdrecord inner join rdrecords on rdrecord.id =rdrecords.id )
on pu_arrivalvouchs.autoid = rdrecords.iarrsid
left join ( pu_appvouch inner join pu_appvouchs on pu_appvouchs.id =pu_appvouch.id )
on pu_appvouchs.autoid = po_podetails.iappids
PU_AppVouch 是请购单主表
PU_AppVouchS 是请购单子表
PO_Pomain 采购订单主表
PO_Podetails采购订单子表
PU_ArrivalVouch到货单主表
PU_ArrivalVouchS 到货单子表
RdRecord 入库主表
RdRecords 入库子表 我要的结果是,请购这个商品后,能在这张表中可以看到请购品名,数量,订单日期及数量,到货日期,数量,入库数量并且写请购单,没有订,也会显示,有请有订没有到货和入库也显示,依次下推.这样的一个表.但现在这个代码pu_appvouchs.autoid = po_podetails.iappids中使用的是LEFT JOIN连接,这个执行完是请购部分数据都为空(其余查询数据结果正确).因为我要从请订到入库四个步骤.所以就是得改成RIGHT JOIN后,查询与现在结果不相符了.请专家给予指导.谢谢
基本步骤:select a.* , b.* , c.* , d.*
from a
left join b on a.id = b.id
left join c on b.id = c.id
left join d on c.id = d.id
where .........不过如果用到group by 的话,建议将每个表分组后再left join如:select a.* , m.* , n.* , t.*
from a
left join (select id , sum(val) from b where ... group by id ) on a.id = m.id
.....