Question

我要指出,在一个A、B、C、D组中,E、F栏组合的价值超过1个。

例如,考虑到以下数据:

SELECT * FROM MyTable   

A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
1          1          1          1          A          B         
1          1          1          1          A          B         
1          1          1          2          A          C         
1          1          1          2          A          D

我希望返回。

A          B          C          D          
---------- ---------- ---------- ----------     
1          1          1          2

..because there ar multiple distinct (E,F) combinations within that (A,B,C,D) grouping).

在这里,Sangkk制造了你试验的故事!

CREATE TABLE [dbo].[MyTable](
    [A] [nchar](10) NULL,
    [B] [nchar](10) NULL,
    [C] [nchar](10) NULL,
    [D] [nchar](10) NULL,
    [E] [nchar](10) NULL,
    [F] [nchar](10) NULL
) ON [PRIMARY]

GO
INSERT INTO [dbo].[MyTable]([A], [B], [C], [D], [E], [F])
SELECT N 1          , N 1          , N 1          , N 1          , N A          , N B           UNION ALL
SELECT N 1          , N 1          , N 1          , N 1          , N A          , N B           UNION ALL
SELECT N 1          , N 1          , N 1          , N 2          , N A          , N C           UNION ALL
SELECT N 1          , N 1          , N 1          , N 2          , N A          , N D

Answer 1

SELECT
    A,B,C,D
FROM
    (
    SELECT
        A,B,C,D,
        ROW_NUMBER() OVER (PARTITION BY A,B,C,D,E,F ORDER BY (SELECT 1)) AS DistinctTuplesLong,
        ROW_NUMBER() OVER (PARTITION BY A,B,C,D ORDER BY (SELECT 1)) AS DistinctTuples
    FROM
        Mytable
    ) T
WHERE 
    DistinctTuplesLong < DistinctTuples

Edit: 错误的窗口功能

Edit: 现在更清楚抽样数据

Answer 2

SELECT A,B,C,D
FROM [dbo].[MyTable]
GROUP BY A,B,C,D
HAVING COUNT(DISTINCT E) > 1 OR COUNT(DISTINCT F) > 1

或

WITH T
     AS (SELECT *,
                DENSE_RANK() OVER (PARTITION BY A, B, C, D ORDER BY E, F) Rnk
         FROM   [dbo].[MyTable])
SELECT DISTINCT A,
                B,
                C,
                D
FROM   T
WHERE  Rnk = 2

友情链接