获取具有相同"Text"但不同"Code"的记录

Question

我有一个如下表（使用SQL server 2008 R2 Enterprise）：

create table Message
(
ID int Not Null Primary Key,
Text nvarchar(100) not null,
Code nvarchar(50) null
)
Insert Into Message Values (1,'Hello Everybody','T6/45')
Insert Into Message Values (2,'Hello Everybody',Null)
Insert Into Message Values (3,'Hello Everybody','T6/45')
Insert Into Message Values (4,'Hello Everybody','T6/45')
Insert Into Message Values (5,'Hello Everybody','T6/70')
Insert Into Message Values (6,'Hello Everybody','T6/70')
Insert Into Message Values (7,'Hello','T6/70')
Insert Into Message Values (8,'Hello','T6/45')
Insert Into Message Values (9,'Hello Everybody',Null)
Insert Into Message Values (10,'Hello Everybody','T6/70')
Insert Into Message Values (11,'Hello',Null)

我需要得到所有具有相同Text但不同Code的记录。并且Code将不是Null。

因此，预期输出将是：

Insert Into Message Values (1,'Hello Everybody','T6/45')
Insert Into Message Values (5,'Hello Everybody','T6/70')
Insert Into Message Values (7,'Hello','T6/70')
Insert Into Message Values (8,'Hello','T6/45')

我尝试了以下查询，但返回了几行：

select m1.* 
from Message M1 
With (nolock)
JOIN Message M2 
With (Nolock) 
On m1.Text=m2.Text
where (m1.ID<>m2.ID 
  and m1.Code<>m2.Code 
  and m1.Code is not null)

我怎样才能得到预期的结果？

此外，这是一个示例数据库。我需要在一个大约有5000万条记录的表上运行查询。因此，任何优化的查询都会有很大帮助。

Answer 1

SELECT ID,TEXT,CODE
FROM message
WHERE ID IN 
(
  SELECT MIN(ID) FROM message
 WHERE CODE IS NOT NULL GROUP BY TEXT,CODE
)

或

SELECT 
   MIN(ID)
   ,TEXT
   ,CODE 
FROM message
WHERE CODE IS NOT NULL GROUP BY TEXT,CODE
ORDER BY MIN(ID)

Answer 2

试试这个；SQL-FIDDLE-DEMO

;with cte as (
  select id,text,code, row_number() over (partition by text,code order by id) rn
  from message
  where code is not null
)
select id,text,code
from cte 
where rn = 1
order by id

Answer 3

SELECT m.*
FROM message m
WHERE EXISTS (
      SELECT 1
        FROM message m2
       WHERE m2.text = m.text
         AND m2.code = m.code
         AND m2.code IS NOT NULL
    GROUP BY m2.text, m2.code
      HAVING MIN(m2.id) = m.id
      )

Answer 4

SELECT DISTINCT m.* 
FROM Message m 
WHERE EXISTS (
  SELECT * 
  FROM Message m1 
  WHERE m1.Id<>m.id 
  AND m1.code<>m.code 
  AND m1.Text=m.Text) 
AND m.Code IS NOT NULL