分享测试过程中常用到的sql 语句－－－－帖二

有同学继续拿出私藏珍贵版本进行分享了

以下是内部交易价格查询，适用于当怀疑集中采购、集中销售、多方调拨流程中价格不对时，可查询内部交易价格表，看取值对不对。
——调拨订单内部交易价格表查询
select case fbizrole when 1 then '需求方' when 2 then '供应方' when 3 then '收货方' when 4 then '发货方' end as "取数方",fprice as "单价",case fdiscounttype when 0 then '折扣率' when 1 then '单位折扣额' end as "折扣方式", fdiscountrate as "折扣率",factualprcie as "实际单价",ftaxrate as "税率", ftaxprice as "含税单价",factualtaxprice as "实际含税单价" from t_im_multisidestransferprices where ftransferbillid = (select fid from t_im_transferorderbill where fnumber = '调拨订单编码')

——集中销售内部交易价格表查询
select * from T_SD_SaleInnerDealPrice where fsaleorderid = (select fid from t_sd_saleorder where fnumber = '销售订单单据编号')

——集中采购内部交易价格查询
select * from T_SM_PurInnerDealPrice where fpurorderid in ( select fid from t_sm_purorder where fnumber = '采购订单单据编号')

——集中销售退货申请单内部交易价格查询
select * from T_SD_SalereturnInnerDealPrice where fSaleretrunid = (select fid from T_SD_Salereturns where fnumber = '销售退货申请单单据编号')

——集中采购多方结算内部交易价格查询
SELECT Pi.fPrice AS "单价",
Pi.fDiscountMode AS "折扣方式",
Pi.fDiscount AS "折扣（率）",
Pi.FacturAlpRice AS "实际单价",
Pi.fTaxRate AS "税率",
Pi.fTaxPrice AS "含税单价",
Pi.FactualTaxPrice AS "实际含税单价",
so.Fname_l2 AS "销售方销售组织",
co.Fname_l2 AS "销售方财务组织"
FROM t_sm_purInnerDealPrice Pi
LEFT OUTER JOIN t_sm_SettlementRouteEntry re
ON Pi.FrouTeenTryId = re.fId
LEFT OUTER JOIN T_ORG_Company co
ON re.fCompanyOrGunItId = co.fId
LEFT OUTER JOIN T_ORG_Sale so
ON re.FSaleOrgUnitID = so.fId
WHERE Pi.fpurOrderId = (SELECT fId
FROM t_sm_purOrder
WHERE fNumber = '采购订单单据编号');