显示一个表中的所有记录以及另一表中的行总和 [英] to display all records from one table and sum of rows from another table

问题描述

我有两个桌子
1. HouseKeeping 具有字段

房间号，
楼号，
StockItem，
要求

和

2. 库存表带有字段

项目，
可用.

我想从HouseKeeping表中选择所有行，并从库存表中选择需求总和.
我已使用此查询从库存表中获取需求总和

I have two tables
1. HouseKeeping with fields

RoomNo,
FloorNo,
StockItem,
Requirement

and

2. Stock table with fields

Item,
Available.

I want to select all rows from HouseKeeping table and sum of requirement from stock table.
I have used this query to get sum of requirement from stock table

SELECT SUM(Requirement) AS Requirement, StockItem
FROM  HouseKeeping
GROUP BY StockItem

除此之外，我还希望所有记录(从库存中选择*).
怎么做?
在此先感谢...

In addition to this I also want all records(select * from Stock).
How to do it?
Thanks in advance...

推荐答案

SELECT        s.Item, s.Available, ISNULL(SUM(h.Requirement), 0) AS Requirement
FROM            Stock AS s LEFT OUTER JOIN
                         HouseKeeping AS h ON h.StockItem = s.Item
GROUP BY s.Item, s.Available

这篇关于显示一个表中的所有记录以及另一表中的行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！