如何在主查询中使用子查询 [英] How to use the sub-query in main query

问题描述

我有两个表1）购买（名称 - TBL_Pur_Invdet）2）促销（名称 - TBL_Sale_Invdet）



i想要显示总购买数量，销售数量，单个查询中的购买退货，销售退货。



我使用SQL Server 2008 R2

请帮助我......



我的尝试：



主要查询---

I have two table 1)Purchase(Name- TBL_Pur_Invdet) 2)Sale(Name- TBL_Sale_Invdet)

i want to show the total Purchase Qty, sale Qty, Purchase Return, Sale Return in a single query.

I use the SQL server 2008 R2
pLEASE HELP ME......

What I have tried:

Main Query---

select   c.catnm,i.itemnm,i.opnstk,u.untnm,(i.ITMcgst*2) as StkGSTRate from tbl_item as i
INNER JOIN tblcat  AS c   ON c.catid=i.ITMcatid    
INNER JOIN tblsubcat  AS sc   ON sc.subcatid=i.ITMsubcatid 
INNER JOIN tblUnit  AS u   ON u.untid=i.ITMcatid   
INNER JOIN TBL_pur_invdet  AS pd   ON pd.ItemIDpur =i.itemid

Sub-query
select distinct ItemIDpur,sum(PDQty )As purchaseQty,p_type  FROM TBL_pur_invdet where p_type='P' group by ItemIDpur,p_type
union all
select distinct ItemIDsale,sum(sDQty )As SaleQty,S_type  FROM TBL_sale_invdet where S_type='S' group by ItemIDsale,S_type
UNION ALL
select distinct ItemIDpur,sum(PDQty ) As purchaseReturnQty,p_type  FROM TBL_pur_invdet where p_type='PR' group by ItemIDpur,p_type
UNION ALL
select distinct ItemIDsale,sum(sDQty )As SaleReturnQty,S_type  FROM TBL_sale_invdet where S_type='SR' group by ItemIDsale,S_type

推荐答案

--i don't know relation but i have tried for OP 
select   c.catnm,i.itemnm,i.opnstk,u.untnm,(i.ITMcgst*2) as StkGSTRate,
(select sum(PDQty )As purchaseQty
      FROM TBL_pur_invdet T where p_type='P' AND 
             T.ItemIDpur=pd.ItemIDpur  group by ItemIDpur,p_type) AS purchasequantity,
(select sum(sDQty )As SaleQty 
     FROM TBL_sale_invdet T where S_type='S' AND 
             T.ItemIDsale=pd.ItemIDpur   group by ItemIDsale,S_type) AS salesQty,
(select sum(PDQty ) As purchaseReturnQty  
    FROM TBL_pur_invdet T  where p_type='PR' AND T.ItemIDpur=pd.ItemIDpur group by 
                                                ItemIDpur,p_type)AS PurchaseReturns,
(select sum(sDQty )As SaleReturnQty
    FROM TBL_sale_invdet T where S_type='SR' AND T.ItemIDsale=pd.ItemIDpur group by 
                                                 ItemIDsale,S_type) AS saleReturns
 from tbl_item as i
INNER JOIN tblcat  AS c   ON c.catid=i.ITMcatid    
INNER JOIN tblsubcat  AS sc   ON sc.subcatid=i.ITMsubcatid 
INNER JOIN tblUnit  AS u   ON u.untid=i.ITMcatid   
INNER JOIN TBL_pur_invdet  AS pd   ON pd.ItemIDpur =i.itemid

这篇关于如何在主查询中使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！