如何显示左表中连接到另一个表的所有记录 [英] How to display all the records from left table which is joined to another table
问题描述
尊敬的先生，
我正在使用带有MS-Access作为后端的vb.net开发Windows应用程序。
我想通过选择perticuler month来生成销售报告/>
为我试图开发SQL查询...
表：股票
- --------------------------------------------
id item尺寸单位MRP
------------------------------------------- ---
1 abc 500 gm 120
2 xyz 500 gm 180
3 pqr 150 gm 60
4 lmn 50 gm 20
5 pqr 1 kg 500
----------------------------------------------
表：Sale
------------------------------------- --------
sr Billno bill_Date itemID Qnty Total
---------------------------- -----------------
1 123 14/11/2013 1 2 240
2 123 14/11/2013 3 2 120
3 123 14/11/2013 4 3 60
4 124 20/02 / 02 / 2013 5 2 1000
5 124 20/02/2013 3 5 300
---------------------------- -----------------
我想显示所需月份的销售报告，例如低于
，例如月份= 11
sr ItemName size unit MRP totalQnty TotalAmt
------------------------------------- -------------
1. abc 500 gm 120 1 240
2. xyz 500 gm 180 0 0
3. pqr 150 gm 60 7 420
4. lmn 50 gm 20 3 60
5. pqr 1 kg 500 0 0
----------------------- ----------------------------
在这里，我想要取消所有的支持库存中存在的物品以及在此特定月份中销售的物品数量，如果该物品未在给定月份出售，那么它应该是空白的，如上面示例sr所示。 5 pqr在月份没有= 2而不是在11中出售所以它显示为零...类似...
这将是什么SQL查询..
请帮帮我，
谢谢，..
你有为此使用LEFT OUTER JOIN。请尝试以下
SELECT stock.item AS ItemName，stock.size，
stock.unit，stock.MRP，ISNULL（MonthSale.Qnty， 0 ） AS totalQnty，ISNULL（MonthSale.Total， 0 ） AS TotalAmt
FROM stock LEFT OUTER JOIN （ SELECT sale.itemID，SUM（sale.Qnty） AS Qnty，SUM（sale.Total） AS 总计
FROM sale
WHERE 年（sale.bill_Date）= 2013 和月（销售） .bill_Date）= 11
GROUP B是 sale.itemID，sale.billdate）MonthSale
stock.id = MonthSale.itemID
试试这个：
; WITH cte as
（ SELECT t.id
，t.item
，t.size
，t.unit
，t.MRP
， CASE WHEN 月份（a.bill_Date）= 11 那么总和（a.Qnty）
ELSE 0 END AS totalQnty
， CASE WHEN 月（a.bill_Date）= 11 THEN sum（a.Total）
ELSE 0 结束 AS totalAmt，a.bill_Date
FROM 股票t
left JOIN sale a ON t.id = a.itemID
WHERE a.bill_Date IS NULL 或 MONTH（a.bill_Date）= 11
GROUP BY a.itemID，t.item，t.size，t.unit，t.MRP， t.id，a.bill_Date）
SELECT s.item AS ItemName ，
s.size，
s.unit，
s.MRP，
isnull（c.totalQnty， 0 ） AS TotalQnty，
isnull（c.totalAmt， 0 ） AS TotalAmt
FROM 股票 as s
left join cte as c ON s.id = c.id
实际上，您的结果应如下所示，为期11个月要求：
sr ItemName size unit MRP totalQnty TotalAmt
----------------- -------------------------------------------------- ----------
1. abc 500 gm 120 2 240
2. xyz 500 gm 180 0 0
3. pqr 150克60 2 120
4. lmn 50 gm 20 3 60
5. pqr 1 kg 500 0 0
- -------------------------------------------------- --------------------------
您错误地计算了大量项目'' abc''和pqr的TotalAmt。
这样......
选择 id，item，size，unit，MRP， sum（Qnty） as totalQnty， sum（Total） as TotalAmt
来自 stock s
left join 销售sl s.id = sl.itemid
其中 < u>月（bill_Date） = 2
group by id，item，size，unit，MRP
Happy Coding ！
：）
Respected sir,
I m developing windows application using vb.net with MS-Access as backend.
I want to generate sales report by selecting perticuler month
for that i m trying to develope sql query...
table : Stock
----------------------------------------------
id item size unit MRP
----------------------------------------------
1 abc 500 gm 120
2 xyz 500 gm 180
3 pqr 150 gm 60
4 lmn 50 gm 20
5 pqr 1 kg 500
----------------------------------------------
table : Sale
---------------------------------------------
sr Billno bill_Date itemID Qnty Total
---------------------------------------------
1 123 14/11/2013 1 2 240
2 123 14/11/2013 3 2 120
3 123 14/11/2013 4 3 60
4 124 20/02/2013 5 2 1000
5 124 20/02/2013 3 5 300
---------------------------------------------
I want to show required month's sales report like below
for example month=11
sr ItemName size unit MRP totalQnty TotalAmt
--------------------------------------------------
1. abc 500 gm 120 1 240
2. xyz 500 gm 180 0 0
3. pqr 150 gm 60 7 420
4. lmn 50 gm 20 3 60
5. pqr 1 kg 500 0 0
---------------------------------------------------
here i want to dislpay all the items present in stock and beside that how many items are sold in this perticular month,if that item is not sold in given month then it should be blank as i shown in above example sr. 5 pqr was sold in month no=2 and not in 11 so its showing zero..likewise...
what would be SQL query for this..
Please help me,
Thank you,..
You have to use LEFT OUTER JOIN for this. Please try following
SELECT stock.item AS ItemName, stock.size, stock.unit,stock.MRP,ISNULL(MonthSale.Qnty,0) AS totalQnty, ISNULL(MonthSale.Total,0) AS TotalAmt FROM stock LEFT OUTER JOIN (SELECT sale.itemID,SUM(sale.Qnty) AS Qnty,SUM(sale.Total) AS Total FROM sale WHERE YEAR(sale.bill_Date)=2013 AND MONTH(sale.bill_Date)=11 GROUP BY sale.itemID, sale.billdate) MonthSale on stock.id = MonthSale.itemID
Try this:
;WITH cte as (SELECT t.id ,t.item ,t.size ,t.unit ,t.MRP ,CASE WHEN MONTH(a.bill_Date)=11 THEN sum(a.Qnty) ELSE 0 END AS totalQnty ,CASE WHEN month(a.bill_Date) = 11 THEN sum(a.Total) ELSE 0 END AS totalAmt,a.bill_Date FROM Stock t left JOIN sale a ON t.id = a.itemID WHERE a.bill_Date IS NULL OR MONTH(a.bill_Date) = 11 GROUP BY a.itemID,t.item,t.size,t.unit,t.MRP,t.id,a.bill_Date) SELECT s.item AS ItemName, s.size, s.unit, s.MRP, isnull(c.totalQnty,0) AS TotalQnty, isnull(c.totalAmt,0) AS TotalAmt FROM Stock as s left join cte as c ON s.id = c.id
Actually your result should be as follows for 11th month for your requirement:
sr ItemName size unit MRP totalQnty TotalAmt
-----------------------------------------------------------------------------
1. abc 500 gm 120 2 240
2. xyz 500 gm 180 0 0
3. pqr 150 gm 60 2 120
4. lmn 50 gm 20 3 60
5. pqr 1 kg 500 0 0
------------------------------------------------------------------------------
You incorrectly calculated totlaQnty of item ''abc'' and TotalAmt of pqr.
This way...
select id,item,size,unit,MRP, sum(Qnty) as totalQnty, sum(Total) as TotalAmt from stock s left join Sale sl on s.id=sl.itemid Where month(bill_Date) = 2 group by id,item,size,unit,MRP
Happy Coding!
:)
这篇关于如何显示左表中连接到另一个表的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
