请教一个查询语句

[nnoovvee]

表A有2个字段name、score
name score
wang 95
zhang 95
li 80
huang 74
. .
. .
. .
要查询出score相同的记录，显示格式如：
name1 name2 name... acore
wang zhang ... 95
这个sql语句该怎么写？

[JL99000]

再问个索引问题：
100万条数据的表，有一个字段索引，要查询出一条记录，最多查询多少次？
--通过索引就能查找到相应的记录，索引记录数据记录的位置

[zhujinqiang]

create table tb(name varchar(10),score int)
insert into tb values('wang' ,           95) 
insert into tb values('zhang',           95) 
insert into tb values('li'   ,           80) 
insert into tb values('huang',           74) 
go

--动态SQL，同一个分数score不知道有多少个，以最多的px一个为准。
declare @sql varchar(8000)
set @sql = 'select score'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then name else '' '' end) [name' + cast(px as varchar) + ']'
from (select distinct px from (select px = (select count(1) from tb where score = t.score and name < t.name) + 1 , * from tb t) m) as a
set @sql = @sql + ' from (select px = (select count(1) from tb where score = t.score and name < t.name) + 1 , * from tb t) m group by score order by score'
exec(@sql) 
/*
score       name1      name2
----------- ---------- ----------
74          huang       
80          li          
95          wang       zhang

学习。

[dawugui]

说说啥意思?

[nnoovvee]

显示出所有分数相同的人名和分数，如这几条数据，查询出：
wang zhang 95

[nnoovvee]

再问个索引问题：
100万条数据的表，有一个字段索引，要查询出一条记录，最多查询多少次？

[dawugui]

create table tb(name varchar(10),score int)
insert into tb values('wang' ,           95) 
insert into tb values('zhang',           95) 
insert into tb values('li'   ,           80) 
insert into tb values('huang',           74) 
go

select score , 
  max(case px when 1 then name else '' end) 'name1',
  max(case px when 2 then name else '' end) 'name2'
from
(
  select px = (select count(1) from tb where score = t.score and name < t.name) + 1 , * from tb t
) m
group by score
order by score

drop table tb

/*
score       name1      name2
----------- ---------- ----------
74          huang      
80          li         
95          wang       zhang

(3 行受影响)
*/

[dawugui]

create table tb(name varchar(10),score int)
insert into tb values('wang' ,           95) 
insert into tb values('zhang',           95) 
insert into tb values('li'   ,           80) 
insert into tb values('huang',           74) 
go

--静态SQL，指同一个分数最多有2(N)个。
select score , 
  max(case px when 1 then name else '' end) 'name1',
  max(case px when 2 then name else '' end) 'name2'
from
(
  select px = (select count(1) from tb where score = t.score and name < t.name) + 1 , * from tb t
) m
group by score
order by score
/*
score       name1      name2
----------- ---------- ----------
74          huang      
80          li         
95          wang       zhang

(3 行受影响)
*/

--静态SQL，指同一个分数不知道有多少个，以最多的一个为准。
declare @sql varchar(8000)
set @sql = 'select score'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then name else '' '' end) [name' + cast(px as varchar) + ']'
from (select distinct px from (select px = (select count(1) from tb where score = t.score and name < t.name) + 1 , * from tb t) m) as a
set @sql = @sql + ' from (select px = (select count(1) from tb where score = t.score and name < t.name) + 1 , * from tb t) m group by score order by score'
exec(@sql) 
/*
score       name1      name2
----------- ---------- ----------
74          huang       
80          li          
95          wang       zhang

(3 行受影响)
*/

drop table tb

[dawugui]

再问个索引问题：
100万条数据的表，有一个字段索引，要查询出一条记录，最多查询多少次？

-----------
不知道你说的意思.