我如何通過INNERJOIN在查詢中使用Distinct-有解無憂

我從我的產品和因素以及因素專案中查詢以顯示它們。

在此查詢中，我為用戶顯示了一份來自因子的報告。

我的查詢是：

WITH CTE AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY FactoriTems.datesave ASC) AS rn,
        FactoriTems.code, FactoriTems.replacement, 
        FactoriTems.suggcode, Factors.dateexport,
        Productions.descriptions, 
        FactoriTems.countt, FactoriTems.price, 
        FactoriTems.countt * FactoriTems.price AS 'total', 
        Productions.country             
    FROM 
        Productions 
    INNER JOIN 
        FactoriTems ON Productions.code = FactoriTems.code
    INNER JOIN 
        Factors ON Factors.gid = FactoriTems.gid
    WHERE 
        (FactoriTems.gid = @gid)
) 
SELECT * 
FROM CTE 
ORDER BY rn

這個查詢是可以的，但是有一個問題，在Productions表中某個類別有兩個或三個產品一個代碼，當這些代碼在factoritems表中時，我的結果集中顯示兩行！

結果是：

恩	代碼	日期匯出	說明	伯爵	價格	全部的
1	啊啊啊	12/24/2021	...	100	2	200
2	bbb	12/24/2021	...	200	3	600
3	抄送	12/24/2021	...	100	2	200
4	滴滴	12/24/2021	...	200	3	600
5	滴滴	12/24/2021	...	100	2	200
6	ee	12/24/2021	...	200	3	600

現在如何只顯示一行'ddd'產品代碼？

我嘗試使用，DISTINCT但出現錯誤。

我希望輸出如下：

恩	代碼	日期匯出	說明	伯爵	價格	全部的
1	啊啊啊	12/24/2021	...	100	2	200
2	bbb	12/24/2021	...	200	3	600
3	抄送	12/24/2021	...	100	2	200
4	滴滴	12/24/2021	...	200	3	600
5	ee	12/24/2021	...	200	3	600

謝謝

uj5u.com熱心網友回復：

只是缺少內部查詢中列的PARTITIN BY子句code。重寫您當前的查詢，例如

WITH CTE AS
 (SELECT ROW_NUMBER() OVER (PARTITION BY ft.code ORDER BY ft.datesave) AS rn0,         
         ft.code,
         ft.replacement,
         ft.suggcode,
         f.dateexport,
         p.descriptions,
         ft.countt,
         ft.price,
         ft.countt * ft.price AS total,
         p.country
    FROM Productions p
    JOIN FactoriTems ft
      ON p.code = ft.code
    JOIN Factors f
      ON f.gid = ft.gid
   WHERE (ft.gid = @gid))
SELECT ROW_NUMBER() OVER (ORDER BY datesave,code) AS rn, 
       code, replacement, suggcode, dateexport, descriptions,
       countt, price, total, country   
  FROM CTE 
 WHERE rn0 = 1
 ORDER BY rn

為了過濾掉每個不同分組的重復項 code

uj5u.com熱心網友回復：

試試這個代碼：

WITH CTE AS
(SELECT ROW_Number() over (PARTITION BY FactoriTems.code ORDER by FactoriTems.datesave ASC) as rn,FactoriTems.code, 
        FactoriTems.replacement, FactoriTems.suggcode,Factors.dateexport,
        Productions.descriptions, FactoriTems.countt, FactoriTems.price, 
        FactoriTems.countt * FactoriTems.price AS 'total', Productions.country              
FROM Productions 
    INNER JOIN FactoriTems ON Productions.code = FactoriTems.code
    INNER JOIN Factors ON Factors.gid = FactoriTems.gid
WHERE (FactoriTems.gid = @gid)) 
SELECT  *   FROM CTE 
WHERE CTE.rn  = 1
ORDER BY rn

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/393887.html

標籤：sql sql-server 内部联接清楚的

上一篇：與SQLServer的幕后PowerBI關系

下一篇：用于計算每天特定時間銷售總額的SQL