T-SQL：使用单个查询连接一个表或另一个表

Question

我的问题如下： 假设您有三个不同的表（产品、账单和退货）。

| ProductId | Name |
=====================
| 1         | Car  |

| BillId | ProductId | Amount |
=================================
| 1      | 1         | 100$   |
| 2      | 1         | 200$   |

| ReturnId | ProductId | Amount |
===================================
| 1        | 1         | 50$    |

如何编写一条单个查询语句以获得以下输出结果：

| Product-ID | Name | Type | Amount |
=====================================
| 1          | Car  | Bill | 100$   |
| 1          | Car  | Bill | 200$   |
| 1          | Car  | Ret  | 50$    |

我尝试使用各种连接方式，但是我还是无法理解。我做错了什么？ 目前我找到的最接近的解决方案是这样的：

SELECT p.*,
       (CASE
           WHEN b.Amount IS NOT NULL THEN 'Bill'
           ELSE 'Ret'
       END) AS Type,
       COALESCE(b.Amount, r.Amount) AS Amount
FROM Products p
LEFT JOIN Bills b ON b.ProductId = p.ProductId
LEFT JOIN Returns r ON r.ProductId = p.ProductId

对我来说有一件非常重要的事情：真实场景查询要比使用Union时复制/粘贴整个查询逻辑更加庞大。

提示：Union是SQL语句中用于合并两个或多个SELECT语句结果集的关键字。

- Shion

Answer 1

下面的代码可以按照要求正常工作，

SELECT  Products.*, 
        [Type], 
        Amount
FROM    Products
        INNER JOIN
        (   SELECT  ProductID, 'Bill' [Type], Amount
            FROM    Bills
            UNION ALL
            SELECT  ProductID, 'Ret' [Type], Amount
            FROM    Returns
        ) transactions
            ON transactions.ProductID = Products.ProductID

- GarethD

---

只是为了澄清我为什么在其他答案之后发布，因为在子查询中执行联合操作可以减少对产品表的扫描次数，并使查询更加高效。在这种情况下，有3个表扫描和1个连接，而其他解决方案需要4个表扫描（两次产品）和2个连接。 - GarethD

这确实是最好的解决方案，而且它像魔法一样奏效！对我来说，这也是最简单的一个。 :) - Shion

Answer 2

您可以使用 UNION：

SELECT p.*, 'Bill' as [Type], b.Amount
FROM Products p
  INNER JOIN Bills b
    ON b.ProductId = p.ProductId
UNION 
SELECT p.*, 'Ret' as [Type], r.Amount
FROM Products p
  INNER JOIN Returns r
    ON r.ProductId = p.ProductId

- Oded

---

实际场景中，查询语句要复杂得多，我不想复制整个逻辑。还有其他方法吗？（我会编辑我的帖子） - Shion

@Shion - 我相信这是最易维护的方法。 - Oded

我认为@johntotetwoo的答案（就在你的上面）更好一些。特别是如果我有其他连接和很多where条件。 - Shion

Answer 3

如果您不喜欢嵌套子查询的外观，您可以采用GarethD的绝佳解决方案并将其转换为公共表达式（CTE）。

WITH Transactions AS (
    SELECT ProductID, 'Bill' [Type], Amount FROM Bills
      UNION ALL
    SELECT ProductID, 'Ret'  [Type], Amount FROM Returns
)
SELECT  p.*,  [Type], Amount
FROM    Products p
JOIN    Transactions t
            ON t.ProductID = p.ProductID

在SqlFiddle上查看演示

- KyleMit

Answer 4

SELECT *
(SELECT Products.ProductID,
        Products.Name,
        'Bill' as Type,
        Bills.Amount
FROM        Products INNER JOIN Bills
            on Products.ProductID = Bills.ProductID
UNION
SELECT  Products.ProductID,
        Products.Name,
        'Ret' as Type,
        Returns.Amount
FROM        Products INNER JOIN Returns
            on Products.ProductID = Returns.ProductID) as iTable
WHERE   iTable.ProductID = 1

- John Woo

---

我会试一下，看起来非常不错 :) - Shion

@Shion - 这与我的答案有何不同？ - Oded

他已经将联合包装成一个表，这样我就可以为其他where和join查询源使用单个查询了。 - Shion

@Shion - 他没有。或者至少没有使用任何有效的语法。缺少FROM、一个闭合括号和一个别名。 - Martin Smith

@马丁：当然了。虽然我同意语法错误的观点。好处是：现在已经不重要了，因为GarethD提供了一个更清晰的版本（即使只是给我看的 :)) - Shion

Answer 5

尝试使用UNION查询 - 类似于以下内容

SELECT *, 'bill' FROM 
dbo.product
INNER JOIN bills ON dbo.product.Product_Id = dbo.bills.product_id
UNION
SELECT *, 'return' FROM 
dbo.product
INNER JOIN dbo.returns ON dbo.product.Product_Id = dbo.returns.product_id

- slapthelownote