在SQL中選擇一個組的前N個獨占值？

SQL（最好是SQL Server）中有沒有辦法選擇一個組中排除其他組的前N條記錄？

例如：

DROP TABLE IF EXISTS #DISTANCE

CREATE TABLE #DISTANCE
(
    GNAME VARCHAR(3)
,   CNAME VARCHAR(3)
,   DIST NUMERIC(5,3)
)

INSERT INTO #DISTANCE
VALUES ('E1', 'C1', 1), ('E1','C2',2),
       ('E2', 'C1', 1.5), ('E2','C2',2.5)

如果我按距離 ASC 為每個 ENAME 尋找第一個獨占 CNAME，我希望輸出如下：

請注意，E1|C2 和 E2|C1 被省略，因為它們將是該組排名結果中的第二個值。

我已經提出了一些 SQL 方法來嘗試正確地提取它，但是當我在 ENAME 上添加其他組以及如果我更改我的前 N ??值時，我的作業就會崩潰。

如果我增加了復雜性：

TRUNCATE TABLE #DISTNACE

INSERT INTO #DISTANCE
VALUES ('E1', 'C1', 1), ('E1', 'C2', 2), 
       ('E1', 'C3', 3), ('E1', 'C4', 5),
       ('E2', 'C1', 2.5), ('E2', 'C2', 4), 
       ('E2', 'C3', 3.5), ('E2', 'C4', 6),
       ('E3', 'C4', 7), ('E3', 'C5', 6), 
       ('E3', 'C6', 4)

我試圖獲得的 SQL 輸出如下所示：

我可以使用以下代碼使其在此特定實體中作業：

WITH X AS  
(
    SELECT * 
    --, RNK             = DENSE_RANK() OVER (ORDER BY DIST ASC) 
    , CNAME_RNK_BY_DIST = DENSE_RANK() OVER (PARTITION BY CNAME ORDER BY DIST ASC)
    , CNAME_RNK_BY_DIST = DENSE_RANK() OVER (PARTITION BY CNAME ORDER BY DIST ASC) 
    FROM #DISTANCE
    )
,MINDIST AS ( -- FIRST OCCURANCE OF CNAME VALUE
    SELECT 
        CNAME
    ,   MIN(DIST) MINDIST
    FROM X GROUP BY CNAME
)
            -- SELECT * , CALC = SUM(CNAME_RNK_BY_DIST / 4) OVER (PARTITION BY CNAME ORDER BY DIST ASC) FROM X order by CNAME, DIST
, X2 AS (

    SELECT *, CALC = SUM(FLOOR(CNAME_RNK_BY_DIST / 4)) OVER (PARTITION BY CNAME ORDER BY DIST ASC) FROM X
)
    --SELECT * FROM X2 order by CNAME, dist
, CALC AS (
    SELECT CNAME, MAXINC = MAX(CALC) FROM X2 GROUP BY CNAME
)
    --SELECT * FROM CALC
, FIRST_OCCURANCE_PAIRS AS (
    SELECT A.*
        ,OCCURANCE = RANK() OVER (PARTITION BY CNAME ORDER BY DIST)
    FROM X A
    JOIN MINDIST B ON A.CNAME = B.CNAME AND A.DIST = B.MINDIST
)
    --SELECT * FROM FIRST_OCCURANCE_PAIRS

,ISO AS 
(
    SELECT * fROM FIRST_OCCURANCE_PAIRS WHERE OCCURANCE > 3 
)
--select * from FIRST_OCCURANCE_PAIRS
--  SELECT * FROM ISO
, NEXT_OCCURANCE AS (
    SELECT A.*
    FROM  X AS A
    JOIN CALC ON A.CNAME = CALC.CNAME
    JOIN ISO B ON A.CNAME_RNK_BY_DIST  =  CALC.MAXINC               and A.CNAME = B.CNAME
)
    --select * from NEXT_OCCURANCE
, FRAME AS (

SELECT 
    CNAME
,   CNAME
,   DIST

FROM FIRST_OCCURANCE_PAIRS
--WHERE OCCURANCE <=3
UNION
SELECT
    CNAME
,   CNAME
,   DIST
FROM NEXT_OCCURANCE
)
--select * from FRAME
, FINAL AS (
    SELECT * ,FINALRNK = ROW_NUMBER() OVER (PARTITION BY CNAME ORDER BY DIST)
    FROM
    FRAME
)
    SELECT * FROM FINAL WHERE FINALRNK <4

但隨著更多記錄的添加，邏輯失敗。有沒有辦法清理這個 SQL 并獲得任意數量組合的結果？

uj5u.com熱心網友回復：

似乎您想要具有最少distper的行cname。那很簡單：

select gname, cname, dist
from
(
  select
    gname, cname, dist, rank() over (partition by cname order by dist) as rnk
  from mytable
)as r
where rnk = 1;

我在RANK這里使用是為了處理關系。因此，當有兩行dista 的最小值相同時cname，您將獲得 one 的這兩行cname。如果您希望每個 僅允許一行cname，則必須使用ROW_NUMBER代替RANK，但您還必須決定gname顯示哪個系結。

uj5u.com熱心網友回復：

在對我的問題進行了更多研究之后，我認為我沒有為我的問題提供足夠有用的資訊。經過一些調整，我想我能夠實作我想要的。我想我必須運行批處理執行步驟來傳遞我的資料以提取我需要的內容，而不是能夠在單個查詢中執行此操作。也許我有辦法減少/優化下面的代碼？

DROP TABLE IF EXISTS #DISTANCE
CREATE TABLE #DISTANCE
(
    GNAME       VARCHAR(3)
,   CNAME       VARCHAR(3)
,   DIST        NUMERIC(5,3)
)

DROP TABLE IF EXISTS #RESULT
CREATE TABLE #RESULT
(
    GNAME       VARCHAR(3)
,   CNAME       VARCHAR(3)
,   DIST        NUMERIC(5,3)
--, FINRNK      INT
)
--INSERT INTO #DISTANCE
--VALUES 
--  ('E1', 'C1', 1  )       
--, ('E1', 'C2', 2  )
--, ('E1', 'C3', 3  )
--, ('E1', 'C4', 5  )
--, ('E2', 'C1', 2.5)   
--, ('E2', 'C2', 4  )
--, ('E2', 'C3', 3.5)
--, ('E2', 'C4', 6  )
--, ('E3', 'C4', 7  )       
--, ('E3', 'C5', 6  )
--, ('E3', 'C6', 4  )
---- CONCLUSIONG; SCENARIO WORKS, VALUES SHIFT.


--INSERT INTO #DISTANCE
--VALUES 
--  ('E1', 'C1', 1  )       
--, ('E1', 'C2', 2  )
--, ('E1', 'C3', 3  )
--, ('E1', 'C4', 5  )
--, ('E2', 'C7', 2.5)   
--, ('E2', 'C8', 4  )
--, ('E2', 'C9', 3.5)
--, ('E2', 'C4', 6  )
--, ('E3', 'C4', 7  )       
--, ('E3', 'C5', 6  )
--, ('E3', 'C6', 4  )
----,   ('E3', 'C3', 1  )
-- CONCLUSIONG; SCENARIO WORKS, VALUES SHIF

INSERT INTO #DISTANCE
VALUES 
    ('E1', 'C1', 1  )       
,   ('E1', 'C2', 2  )
,   ('E1', 'C3', 3  )
,   ('E1', 'C4', 4  ) -- switch with E2 dist to switch groups in results
,   ('E2', 'C1', 2.5)   
,   ('E2', 'C2', 4  )
,   ('E2', 'C3', 3.5)
,   ('E2', 'C4', 6  ) -- switch dist with e1 to move groups in results
,   ('E3', 'C4', 7  )       
,   ('E3', 'C5', 6  )
,   ('E3', 'C6', 4  )
,   ('E3', 'C3', 1  )

--INSERT INTO #DISTANCE
--VALUES 
--  ('E1', 'C1', 1  )       
--, ('E1', 'C2', 2  )
--, ('E1', 'C3', 3  )
--, ('E1', 'C4', 5  )
--, ('E2', 'C7', 2.5)   -- CHANGE CVAL TO UNIQUE TO PULL THROUGH    
--, ('E2', 'C8', 4  )   -- CHANGE CVAL TO UNIQUE TO PULL THROUGH
--, ('E2', 'C11', 3.5)  -- CHANGE CVAL TO UNIQUE TO PULL THROUGH
--, ('E2', 'C4', 6  )
--, ('E3', 'C9', 3  ) -- NEW
--, ('E3', 'C4', 7  )       
--, ('E3', 'C5', 6  )
--, ('E3', 'C6', 4  )
-- SCENARIO CORRECT. C4 SHOULD NOT BE PULLED IN.
GO

BEGIN
    ;
    WITH 
     NOROOM AS (
        SELECT GNAME FROM #RESULT GROUP BY GNAME HAVING COUNT(1) >= 3 -- HAS TO BE NOROOM BECAUSE 'HASROOM' WOULD not make sense in first pass.
    )
    ,X AS (
        SELECT 
            A.CNAME
        ,   A.DIST
        ,   A.GNAME
        ,   RNK = RANK()        OVER (PARTITION BY A.GNAME ORDER BY A.DIST ASC)
        ,   RNK2 = ROW_NUMBER() OVER (ORDER BY A.DIST ASC)
        ,   #RESULT.CNAME AS RSLT
        FROM #DISTANCE A
        LEFT JOIN #RESULT ON A.CNAME = #RESULT.CNAME
        LEFT JOIN NOROOM ON A.GNAME = NOROOM.GNAME
        WHERE NOROOM.GNAME IS NULL AND #RESULT.CNAME IS NULL --ORDER BY RNK
        )
        --SELECT * FROM X
    , Y AS (
        SELECT X.CNAME, MINRNK2 = MIN(X.RNK2) 
        FROM X 
        --LEFT JOIN #RESULT B ON X.CNAME = B.CNAME
        GROUP BY  X.CNAME
    )
    --  select * from y
    --  SELECT * FROM x JOIN Y ON X.CNAME = Y.CNAME AND X.RNK2 = Y.MINRNK2
    INSERT INTO #RESULT
    SELECT X.GNAME, X.CNAME, X.DIST FROM X 
     JOIN Y AS SQ ON X.CNAME = SQ.CNAME AND X.RNK2 = SQ.MINRNK2
    WHERE X.RNK <=3 --AND   #RESULT.GNAME IS NULL

     PRINT 'LOOP'
END
GO 10
     SELECT * FROM #RESULT ORDER BY GNAME

標籤：sql sql-server 查询语句

上一篇：有沒有辦法在sqlserver中將表名作為變數？[復制]

下一篇：如何在antlr4中獲取多個命令？