导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

请教一个查询语句

nnoovvee 2008-01-07 10:58:54
表A有2个字段name、score
name score
wang 95
zhang 95
li 80
huang 74
. .
. .
. .
要查询出score相同的记录,显示格式如:
name1 name2 name... acore
wang zhang ... 95
这个sql语句该怎么写?
...全文
83 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2008-01-07
再问个索引问题:
100万条数据的表,有一个字段索引,要查询出一条记录,最多查询多少次?

-----------
不知道你说的意思.
回复
dawugui 2008-01-07
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 2008-01-07
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 行受影响)
*/
回复
nnoovvee 2008-01-07
再问个索引问题:
100万条数据的表,有一个字段索引,要查询出一条记录,最多查询多少次?
回复
nnoovvee 2008-01-07
显示出所有分数相同的人名和分数,如这几条数据,查询出:
wang zhang 95
回复
dawugui 2008-01-07
说说啥意思?
回复
zhujinqiang 2008-01-07
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

学习。
回复
JL99000 2008-01-07
再问个索引问题:
100万条数据的表,有一个字段索引,要查询出一条记录,最多查询多少次?
--通过索引就能查找到相应的记录,索引记录数据记录的位置
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告