SQLServerJOIN回傳多行-左側需要一行

我已經谷歌搜索并進行了廣泛的測驗。我想我的大腦可能會衰退。

我們的網站有各種產品的特定登陸頁面。

該Control表包含頁面標題和產品搜索資訊。

Control

ID       Title                Category        Colour
------------------------------------------------------------
1        Blue Postcards       Postcard        Blue
2        Blue Photos          Photo           Blue
3        Yellow Postcards     Postcard        Yellow
4        Postcards            Postcard        null

Products

pk_ProdID       Category         Price
-------------------------------------------
100             Postcard         59
200             Photo            42
300             Postcard         33
400             Photo            97
500             Postcard         78
600             Postcard         48

每個產品都有零個或多個顏色的銷售記錄。

Products_Colours

fk_ProdID  Colour
-------------------------
100        Blue
100        Yellow
200        Blue
300        Yellow
400        Red
500        Blue
500        Yellow
600        Blue

如果的 IDControl為 1，則頁面應顯示藍色明信片。

以下是所需的結果：

ProdID         Category         Price
-------------------------------------------
100            Postcard         59
500            Postcard         78
600            Postcard         48

我實際得到的是：

ProdID         Category         Price
-------------------------------------------
100            Postcard         59
100            Postcard         59
500            Postcard         78
500            Postcard         78
600            Postcard         48

查詢陳述句

此存盤程序為我的 PHP 頁面回傳兩個記錄集，以減少對資料庫的請求數。

DECLARE @OrderBy varchar(1)
SET @OrderBy = 'D'
DECLARE @Row int
SET @Row = 1
DECLARE @ControlID int
SET @Control = 1

/* get control info for web page */
SELECT 
c.ID,
c.Title

FROM dbo.Control c

WHERE c.ID = @ControlID;

/* get search criteria */
WITH ControlSearch AS 
(
SELECT 
ID,
Category,
Colour
FROM Control WHERE ID = @ControlID
),

/* get products that match search criteria */
ProductSearch AS 
(
SELECT 
DISTINCT p.ProdID,       
p.Category
Count(1) OVER() As Total_Records,
   
   ROW_NUMBER() OVER(
   ORDER BY 
    CASE WHEN @OrderBy = 'D' THEN p.Price END DESC,
    CASE WHEN @OrderBy = 'U' THEN p.Price END ASC
    ) As RowNum       
       
   FROM dbo.Products p
   JOIN ControlSearch l ON l.ID = @ControlID
   LEFT JOIN dbo.Products_Colours co ON p.ProdID = co.ProdID
   WHERE
  
   (p.Category = l.Category)
   
   AND       
  
   (co.Colour = l.Colour OR l.Colour is null)
)   

/* return recordset */
SELECT * FROM ProductSearch WHERE RowNum >= @Row AND RowNum < (@Row   50)

我向用戶顯示匹配記錄的總數，并輸出行號，以便我可以按 50 個產品分頁。

為什么這會根據它有多少顏色回傳多個產品？

uj5u.com熱心網友回復：

在黑暗中有點刺痛，但也許你真正想要的是EXISTS：

DECLARE @ControlID int
SET @ControlID = 1;

SELECT *
FROM dbo.Products P
WHERE EXISTS (SELECT 1
              FROM dbo.Control C
                   LEFT JOIN dbo.Products_Colours PC ON C.Colour = PC.Colour
              WHERE C.Category = P.Category
                AND PC.fk_ProdID = P.pk_ProdID
                AND (PC.Colour IS NOT NULL OR C.Colour IS NULL)
                AND C.ID = @ControlID);

uj5u.com熱心網友回復：

看起來你需要這樣的東西

SELECT
  p.ProdID,       
  p.Category,
  COUNT(*) OVER () AS Total_Records,
  CASE WHEN @OrderBy = 'U'
    THEN COUNT(*) OVER () - ROW_NUMBER() OVER (ORDER BY p.Price)   1
    ELSE ROW_NUMBER() OVER (ORDER BY p.Price) END
    AS RowNum   
FROM dbo.Products P
JOIN dbo.Control C ON C.ID = @ControlID  -- single-row cross join
WHERE C.Category = P.Category
  AND (
    C.Colour IS NULL OR EXISTS (SELECT 1
        FROM dbo.Products_Colours PC
        WHERE C.Colour = PC.Colour
          AND PC.fk_ProdID = P.pk_ProdID)
  );

標籤：sql-server 加入 左连接

上一篇：如何在Pandas中將多個列與多個其他列（INCROSS）連接起來并在結果輸出中給出陣列？

下一篇：排序軸并使資料更具表現力