將子查詢重構為 JOIN 和 CROSS APPLY，父表中的每條記錄只獲取一行

給定以下查詢：

SELECT
 p.ProductName,
 CASE
   WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1)
   THEN
     1
   ELSE
     0
   END
 AS HasImage,
 (SELECT Sum(StockBalance) FROM Product WHERE ProductSuperID = p.ProductSuperID) AS StockBalance,
 CASE
   WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND Price IS NULL)
   AND EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND DiscountPrice IS NULL)
   THEN
     0
   ELSE
     1
   END
   AS HasPrice
FROM ProductSuper p

-- SCHEMA
CREATE TABLE ProductSuper
(
 ProductSuperID int,
 ProductName varchar(255)
)
CREATE TABLE Product
(
 ProdID int,
 ProductSuperID int,
 HasImage bit,
 StockBalance int,
 Price decimal(10,2),
 DiscountPrice decimal(10,2)
)
INSERT INTO ProductSuper
 (ProductSuperID, ProductName)
VALUES
 (1, 'Product 1'),
 (2, 'Product 2')
INSERT INTO Product
 (ProductSuperID, HasImage, StockBalance, Price, DiscountPrice)
VALUES
 (1, 0, 10, 10.00, 9.00),
 (1, 0, 0, 10.00, 9.00),
 (2, 0, 10, 10.00, 9.00),
 (2, 0, 2, 10.00, 9.00),
 (2, 1, 5, 10.00, 9.00)

我想了解如何最好地重寫它以使用JOINor CROSS APPLY，如果有的話可以避免一些程式碼重複。我嘗試編寫一個基於 JOIN 的版本（以及一個帶有 APPLY 的版本），但是對於表中的每一行，我得到的結果不止一個ProductSuper，而我只想要一行。

即預期結果：

+-------------+-----+-----+-----+
|  Product 1  |  0  |  10 |  1  |
+-------------+-----+-----+-----+
|  Product 2  |  1  |  17 |  1  |
+-------------+-----+-----+-----+

（我知道對於這段特定的程式碼，重寫幾乎沒有什麼好處，因為子查詢很快。但這只是一個例子。）

謝謝。

我可能會按如下方式構造這個查詢

WITH ProductDetails
    AS (SELECT ProductSuperID,
               HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END),
               StockBalance = Sum(StockBalance),
               HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END
        FROM   Product
        GROUP  BY ProductSuperID)
SELECT p.ProductName,
      HasImage = ISNULL(pd.HasImage,0),
      pd.StockBalance,
      HasPrice = ISNULL(pd.HasPrice,0)
FROM   ProductSuper p
      LEFT JOIN ProductDetails pd
        ON p.ProductSuperID= pd.ProductSuperID; 

因為CROSS APPLY它可以這樣寫

SELECT ps.ProductName,
      pd.HasImage,
      pd.StockBalance,
      pd.HasPrice
FROM   ProductSuper ps
      CROSS APPLY (SELECT HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END),
                          StockBalance = Sum(StockBalance),
                          HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END
                   FROM   Product p
                   WHERE  p.ProductSuperID= ps.ProductSuperID) pd 

引用自：https://dba.stackexchange.com/questions/227687