使用2个表作为JOIN条件

Question

如何为JOIN条件选择两个表，

我试图查询所有客户的特价(需要出口(，每个客户有3种类型的产品折扣存储在3张表中，

我想得到的是(价格是样品定价(：

type | sku | Unit_Price | customer_group
---------------------------
item | DS875 | 50         | COMPANY X
item | DS875 | 25       |   COMPANY Y
item | DS875 | 30       |   COMPANY Z
item | DS544 | 50         | COMPANY X
item | DS544 | 25       |   COMPANY Y
item | DS544 | 30       |   COMPANY Z
item | DS855 | 50         | COMPANY X
item | DS855 | 25       |   COMPANY Y
item | DS855 | 30       |   COMPANY Z

这是我的查询

SELECT 'item' AS type, p.PRODUCT_SKU AS sku, 
CASE 
WHEN pp.PRICE IS NULL AND pp.PRICE > 0 
THEN
pp.PRICE
WHEN cp.Price IS NOT NULL AND cp.Price > 0 
THEN
cp.Price
WHEN c.MarkupDiscount <> 0 
THEN
p.PRODUCT_PRICE -
(p.PRODUCT_PRICE / 100 * c.MarkupDiscount)
END AS Unit_Price,
c.COMPANY_NAME AS customer_group
FROM
T002_PRODUCTS p
LEFT JOIN CUSTOMERS c ON c.VISIBLE = 1 AND c.ACTIVE = 1
RIGHT JOIN PROMOTION_PRICES pp ON 
(c.CUSTOMER_ID = pp.CUSTOMER_ID AND 
p.PRODUCT_ID = pp.PRODUCT_ID) AND 
pp.ACTIVE = 1 AND pp.DATE_FROM <= GETDATE() AND 
pp.DATE_TO >= GETDATE()
RIGHT JOIN AFC cp ON (c.CUSTOMER_ID = cp.HH AND 
cp.VC = p.PRODUCT_ID) AND cp.SA = 1

不知道如何做到这一点：(

样本数据

产品

PRODUCT_ID|PRODUCT_SKU|PRODUCT_PRICE
____________________________________
8         | DS544     | 3.99
9         | DS855     | 5
10        | DS875     | 7

客户

CUSTOMER_ID | COMPANY_NAME | MarkupDiscount | VISIBLE | ACTIVE
______________________________________________________
78         | COMPANY X  |  15          |  1     | 1
79         | COMPANY Y  |  0           |  1     | 1
80         | COMPANY Z  |  0           |  1     | 1

促销_价格

CUSTOMER_ID | PRODUCT_ID | DATE_FROM   | DATE_TO    | ACTIVE | PRICE
____________________________________________________________
78          |  8         | '2018-01-01'|'2019-01-01'| 1      | 50

AFC-

HH | VC | SA | Price
_____________________
80 |  8 |  1 | 50

Answer 1

这是未经测试的：

SELECT
'item'         AS type
, p.PRODUCT_SKU  AS sku
, CASE
WHEN pp.PRICE IS NULL AND
pp.PRICE > 0 THEN pp.PRICE
WHEN cp.Price IS NOT NULL AND
cp.Price > 0 THEN cp.Price
WHEN c.MarkupDiscount <> 0 THEN p.PRODUCT_PRICE -
(p.PRODUCT_PRICE / 100 * c.MarkupDiscount)
END            AS Unit_Price
, c.COMPANY_NAME AS customer_group
FROM AFC cp
INNER JOIN CUSTOMERS c
ON cp.HH = c.CUSTOMER_ID AND c.VISIBLE = 1 AND c.ACTIVE = 1
INNER JOIN T002_PRODUCTS p
ON cp.VC = p.PRODUCT_ID
INNER JOIN PROMOTION_PRICES pp
ON c.CUSTOMER_ID = pp.CUSTOMER_ID
AND p.PRODUCT_ID = pp.PRODUCT_ID
AND GETDATE() BETWEEN pp.DATE_FROM AND pp.DATE_TO
WHERE cp.SA = 1

选择"来自表"是IMHO使查询易于遵循的关键。在这里，唯一一个同时拥有客户端和产品的表是AFC，因为我们需要两者来实现这一点，所以从这一点开始。从那时起，双方的关系似乎变得清晰起来。

提示：许多经验丰富的SQL用户从不使用"right join"，这并不是因为它不好或不正确，纯粹是因为您可以随时更改表的顺序以避免这种联接类型。然后，连接的"流"更容易以"从上到下"的方式遵循。一旦引入了正确的联接，你就需要从相反的方向思考，这可能会让人很困惑。因此，一旦您看到自己使用了正确的联接，请暂停并重新考虑您真正想要从哪个表开始

nb：不是每个人都会同意这个建议，但更多的人会同意