如何从两个表中正确选择（SQL）

I have two tables in my database Sales and Appliances:

我的数据库Sales和Appliances中有两个表：

Sales

销售

SaleDate   EmployeeID AppID Qty
---------- ---------- ----- -----------
2010-01-01 1412       150   1
2010-01-05 3231       110   1
2010-01-03 2920       110   2
2010-01-13 1412       100   1
2010-01-25 1235       150   2
2010-01-22 1235       100   2
2010-01-12 2920       150   3
2010-01-14 3231       100   1
2010-01-15 1235       300   1
2010-01-03 2920       200   2
2010-01-31 2920       310   1
2010-01-05 1412       420   1
2010-01-15 3231       400   2

Appliances

家电

ID   AppType              StoreID Cost          Price
---- -------------------- ------- ------------- -------------
100  Refrigerator         22      150           250
110  Refrigerator         20      175           300
150  Television           27      225           340
200  Microwave Oven       22      120           180
300  Washer               27      200           325
310  Washer               22      280           400
400  Dryer                20      150           220
420  Dryer                22      240           360

How can I obtain this results table. (That lists the number of sales of refrigerators and for each sale, displays the total sale price as well (total sale price = qty * price).

我如何获得此结果表。 （列出冰箱的销售数量和每次销售，也显示总销售价格（总销售价格=数量*价格）。

AppID AppType          Qty         total sale price
----- ---------------- ----------- ----------------
110   Refrigerator     1           300
110   Refrigerator     2           600
100   Refrigerator     1           250
100   Refrigerator     2           500
100   Refrigerator     1           250

---

My Attempt:

我的尝试：

SELECT AppID, AppType, Qty, (Qty * Price) as 'total sale price'
FROM Sales s, Appliances a
WHERE (AppID) IN (SELECT ID FROM Appliances WHERE AppType = 'Refrigerator')
AND (AppType) IN ('Refrigerator')

Currently produces

目前生产

AppID AppType              Qty         total sale price
----- -------------------- ----------- ----------------
110   Refrigerator         1           250
110   Refrigerator         1           300
110   Refrigerator         2           500
110   Refrigerator         2           600
100   Refrigerator         1           250
100   Refrigerator         1           300
100   Refrigerator         2           500
100   Refrigerator         2           600
100   Refrigerator         1           250
100   Refrigerator         1           300

2 个解决方案

#1

Do a join on sales.AppId = appliances.ID

在sales.AppId = appliances.ID上加入联接

select s.AppId, a.AppType, s.Qty, 
   a.Price * s.Qty as total
from appliances a
join sales s on s.AppId = a.ID
where a.AppType = 'Refrigerator'

#2

You can do this by using JOIN:

您可以使用JOIN执行此操作：

ONLINE DEMO

在线演示

SELECT
    AppID   = p.ID,
    p.AppType,
    s.Qty,
    [Total Sales Price] = s.Qty * p.Price
FROM Sales s
INNER JOIN Appliances p
    ON p.ID = s.AppID
WHERE p.AppType = 'Refrigerator'

Note

注意

• You need to avoid using the old-style JOIN syntax and use the JOIN keyword instead.
• 您需要避免使用旧式JOIN语法并改为使用JOIN关键字。