首页新闻找找看学习计划

请教一个SQL语句的写法

0
悬赏园豆:100 [已解决问题] 解决于 2012-03-28 19:00
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
artwl的主页 artwl | 专家六级 | 园豆:16526
提问于:2012-03-28 16:58
< >
分享
最佳答案
0

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

收获园豆:70
hexllo | 菜鸟二级 |园豆:405 | 2012-03-28 17:19
其他回答(1)
0

select id from 

TestTable where name in('A','B','C','D')
id应该是聚集索引吧,name建非聚集索引
收获园豆:30
ailove | 园豆:382 (菜鸟二级) | 2012-03-28 17:13
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册