从数据库中按分数段查询人数

[王可诚]

MS_SQL Server
要求显示结果如下:

分数段 人数
<60 XX
60~69 XX
70~79 XX
80~89 XX
90~99 XX
100 XX

[王可诚]

期待完美答案中。。。。。。。。。（坚着排列）

[王可诚]

（竖着排列）

[feixianxxx]

[code=SQL]
select 分数段='[100]',count(*) as 个数 from tb where 分数=100
union all
select 分数段='[90~99]',count(*) as 个数 from tb where 分数 between 90 and 99
union all
select 分数段='[80~89]',count(*) as 个数 from tb where 分数 between 80 and 89 
union all
select 分数段='[70~79]',count(*) as 个数 from tb where 分数 between 70 and 79 
union all
select 分数段='[60~69]',count(*) as 个数 from tb where 分数 between 60 and 69 
union all
select 分数段='[<60]',count(*) as 个数 from tb where 分数<60

[/code]

[feixianxxx]

select 分数段='[100]',count(*) as 个数 from tb where 分数=100
union all
select 分数段='[90~99]',count(*) as 个数 from tb where 分数 between 90 and 99
union all
select 分数段='[80~89]',count(*) as 个数 from tb where 分数 between 80 and 89 
union all
select 分数段='[70~79]',count(*) as 个数 from tb where 分数 between 70 and 79 
union all
select 分数段='[60~69]',count(*) as 个数 from tb where 分数 between 60 and 69 
union all
select 分数段='[<60]',count(*) as 个数 from tb where 分数<60

[黄_瓜]

select * from
(
select 分数段='[100]',count(*) as 个数 from tb where 分数=100
union all
select 分数段='[90~99]',count(*) as 个数 from tb where 分数 between 90 and 99
union all
select 分数段='[80~89]',count(*) as 个数 from tb where 分数 between 80 and 89 
union all
select 分数段='[70~79]',count(*) as 个数 from tb where 分数 between 70 and 79 
union all
select 分数段='[60~69]',count(*) as 个数 from tb where 分数 between 60 and 69 
union all
select 分数段='[<60]',count(*) as 个数 from tb where 分数<60
)

[--小F--]

select * from
(
 select  分数段 = '<60' , 分数 = 语文,人数 from tb 
 union all
 select  分数段 = '60~69' , 分数 = 数学,人数 from tb
 union all
 select  分数段 = '70~79' , 分数 = 物理,人数 from tb
 union all
 select  分数段 = '90~99' , 分数 = 数学,人数 from tb
 union all
 select  分数段 = '100' , 分数 = 物理,人数 from tb

) t
order by  case when 分数<60 then 1 
          case when 分数 between 60 and 69 then 2 
          case when 分数 between 70 and 79 then 3 
          case when 分数 between 80 and 89 then 4 
          case when 分数 between 90 and 99 then 5
          case when 分数=100 then 6 end 

[水族杰纶]

...

[--小F--]

select * from
(
 select  分数段 = '<60' , count(1) as 人数 from tb 
 union all
 select  分数段 = '60~69' , count(1) as 人数 from tb
 union all
 select  分数段 = '70~79' , count(1) as 人数 from tb
 union all
 select  分数段 = '90~99' , count(1) as 人数 from tb
 union all
 select  分数段 = '100' , count(1) as 人数 from tb

) t
order by  case when 分数<60 then 1 
          case when 分数 between 60 and 69 then 2 
          case when 分数 between 70 and 79 then 3 
          case when 分数 between 80 and 89 then 4 
          case when 分数 between 90 and 99 then 5
          case when 分数=100 then 6 end 

[水族杰纶]

嵌套多余
影响效率~~~

[--小F--]

[Quote=引用 12 楼 wufeng4552 的回复:]
嵌套多余
影响效率~~~
[/Quote]

我是用来oder by 的

[水族杰纶]

[Quote=引用 13 楼 fredrickhu 的回复:]
引用 12 楼 wufeng4552 的回复:
嵌套多余
影响效率~~~


我是用来oder by 的
[/Quote]
不须order by
它会按照union all
的顺序排列的

[SQL77]

SELECT 分数段,
SUM(CASE WHEN 分数<60 THEN 1 ELSE 0)AS  人数..
SUM(CASE WHEN 分数<60 THEN 1 ELSE 0)AS  人数..
SUM(CASE WHEN 分数<60 THEN 1 ELSE 0)AS  人数..


FROM TB

[水族杰纶]

select sum(case when 分数<60 then 1 else 0 end )[<60],
       sum(case when 分数 between 60 and 69 then 1 else 0 end )[60~69], 
       sum(case when 分数 between 70 and 79 then 1 else 0 end )[70~79],
       sum(case when 分数 between 80 and 89 then 1 else 0 end )[80~89],
       sum(case when 分数 between 90 and 99 then 1 else 0 end )[90~99],
       sum(case when 分数=100 then 1 else 0 end )[100]
from tb 

[--小F--]

[Quote=引用 2 楼 wufeng4552 的回复:]
SQL codeselectsum(casewhen 分数<60then1else0end )[<60],sum(casewhen 分数between60and69then1else0end )[60~69],sum(casewhen 分数between70and79then1else0end )[70~79],sum(casewhen 分数between80and89then1else0end ¡­
[/Quote]

水哥正解

[gxjwqm]

[Quote=引用 7 楼 feixianxxx 的回复:]
SQL codeselect 分数段='[100]',count(*)as 个数from tbwhere 分数=100unionallselect 分数段='[90~99]',count(*)as 个数from tbwhere 分数between90and99unionallselect 分数段='[80~89]',count(*)as 个数from tbwhere 分数between80and8¡­
[/Quote]