SQL:如何在某些列上选择不同的选择

Question

我有一张表格看起来像这样：

+---+------------+----------+
|ID | SomeNumber | SomeText |
+---+------------+----------+
|1  | 100        | 'hey'    |
|2  | 100        | 'yo'     |
|3  | 100        | 'yo'     | <- Second occurrence
|4  | 200        | 'ey'     |
|5  | 200        | 'hello'  |
|6  | 200        | 'hello'  | <- Second occurrence
|7  | 300        | 'hey'    | <- Single
+---+------------+----------+

我想提取 SomeNumber的行超过一个行，而 SomeNumbers和 SomeText是不同的。这意味着我想要以下内容：

+---+------------+----------+
|ID | SomeNumber | SomeText |
+---+------------+----------+
|1  | 100        | 'hey'    |
|2  | 100        | 'yo'     |
|4  | 200        | 'ey'     |
|5  | 200        | 'hello'  |
+---+------------+----------+

我不知道在这里做什么。

我需要一些东西：

SELECT t.ID, DISTINCT(t.SomeNumber, t.SomeText)  --this is not possible
FROM (
    SELECT mt.ID, mt.SomeNumber, mt.SomeText
    FROM MyTable mt
    GROUP BY mt.SomeNumber, mt.SomeText --can't without mt.ID
    HAVING COUNT(*) > 1
)

有什么建议？

Answer 1

使用带有行号和计数行的CTE可能会给您带来所需的东西：

创建和填充示例表（请在您将来的问题中保存此步骤）：

CREATE TABLE MyTable(id int, somenumber int, sometext varchar(10));
INSERT INTO MyTable VALUES
(1,100,'hey'),
(2,100,'yo'),
(3,100,'yo'),
(4,200,'ey'),
(5,200,'hello'),
(6,200,'hello'),
(7,300,'hey');

查询：

;WITH cte as 
(
    SELECT id, 
           someNumber,
           someText, 
           ROW_NUMBER() OVER (PARTITION BY someNumber, someText ORDER BY ID) rn, 
           COUNT(id) OVER (PARTITION BY someNumber) rc
    FROM MyTable
)
SELECT id, someNumber, someText
FROM cte
WHERE rn = 1
AND rc > 1

结果：

id  someNumber  someText
1   100         hey 
2   100         yo  
4   200         ey  
5   200         hello