请教一个SQL语句的写法

问题

id name
----------- --------------------
1 A
1 B
1 C
1 D
1 E
1 F
2 A
2 C
2 D
2 E
3 D
3 E
3 W
3 Q
3 A
4 A
4 C
5 J
5 H
6 A
6 E
6 F
6 B
7 Q

表的结构很简单，只有两列，上面是示例数据

现在要查出name值中有"A,B,C,D"中任意一个值的id

也就是说结果应该是 1，2，3，4，6

如果数据量比较大时如何查询，向大家请教比较高效的方案

测试SQL语句：

View Code

create table TestTable(
id int not null,
name nvarchar(20)
)
Go

INSERT INTO TestTable values(1,'A');
INSERT INTO TestTable values(1,'B');
INSERT INTO TestTable values(1,'C');
INSERT INTO TestTable values(1,'D');
INSERT INTO TestTable values(1,'E');
INSERT INTO TestTable values(1,'F');
INSERT INTO TestTable values(2,'A');
INSERT INTO TestTable values(2,'C');
INSERT INTO TestTable values(2,'D');
INSERT INTO TestTable values(2,'E');
INSERT INTO TestTable values(3,'D');
INSERT INTO TestTable values(3,'E');
INSERT INTO TestTable values(3,'W');
INSERT INTO TestTable values(3,'Q');
INSERT INTO TestTable values(3,'A');
INSERT INTO TestTable values(4,'A');
INSERT INTO TestTable values(4,'C');
INSERT INTO TestTable values(5,'J');
INSERT INTO TestTable values(5,'H');
INSERT INTO TestTable values(6,'A');
INSERT INTO TestTable values(6,'E');
INSERT INTO TestTable values(6,'F');
INSERT INTO TestTable values(6,'B');
INSERT INTO TestTable values(7,'Q');
Go

select * from TestTable

最佳回答

select id from (select top 1 ID,NEWID()as tmp from TestTable where name in(N'A',N'B',N'C',N'D') order by tmp)as a