求一条mysql 查询语句

[zhanghang19890621]

表 A
id name type score
1 a x 0
2 b y 54
3 a z 87
4 c z 0
5 b w 20
6 c x 75
7 d y 32
8 e w 23

需要把表A转换成如下显示：
范围 X Y Z W
0-30 1 0 1 1
31-60 - - - -
61-90 - - - -
>90 - - - -

统计显示 也就是说score 返回在0-30之间的有多少人 31-60 的有多少人 依此类推...

[百年树人]

mysql的

[code=SQL]create table `A`(`id` int,`name` varchar(1),`type` varchar(1),`score` int);
insert `A` values(1,'a','x',0);
insert `A` values(2,'b','y',54);
insert `A` values(3,'a','z',87);
insert `A` values(4,'c','z',0);
insert `A` values(5,'b','w',20);
insert `A` values(6,'c','x',75);
insert `A` values(7,'d','y',32);
insert `A` values(8,'e','w',23);

select 
 case when score <30 then '0-30'
      when score >=30 and score <60 then '31-60'
      when score >=60 and score <90 then '51-90'
      when score >=90 then '>90' end as `范围`,
 sum(case when type='x' then 1 else 0 end) as X,
 sum(case when type='y' then 1 else 0 end) as Y,
 sum(case when type='z' then 1 else 0 end) as Z,
 sum(case when type='w' then 1 else 0 end) as W
from A
group by case when score <30 then '0-30'
      when score >=30 and score <60 then '31-60'
      when score >=60 and score <90 then '51-90'
      when score >=90 then '>90' end

/**
范围	X	Y	Z	W
0-30	1	0	1	2
31-60	0	2	0	0
51-90	1	0	1	0
**/

[/code]

[Mr_Nice]

mysql语法不懂，关注...

[昵称被占用了]

sql server写法

select 范围=case when score <30 then '0-30'
      when score >=30 and score <60 then '31-60'
      when score >=60 and score <90 then '51-90'
      when score >=90 then '>90' end
,X=sum(case when type='x' then 1 else 0 end)
,Y=sum(case when type='y' then 1 else 0 end)
,Z=sum(case when type='z' then 1 else 0 end)
,W=sum(case when type='w' then 1 else 0 end)
from A
group by case when score <30 then '0-30'
      when score >=30 and score <60 then '31-60'
      when score >=60 and score <90 then '51-90'
      when score >=90 then '>90' end

[hao1hao2hao3]

[Quote=引用 2 楼 haiwer 的回复:]
sql server写法


SQL code
select 范围=case when score <30 then '0-30'
when score >=30 and score <60 then '31-60'
when score >=60 and score <90 then '51-90'
when score >=90 then '>……
[/Quote]


支持！ LZ是不是写错了？要的就是MSSQLServer 的写法

[zhanghang19890621]

[Quote=引用 2 楼 haiwer 的回复:]
sql server写法


SQL code
select 范围=case when score <30 then '0-30'
when score >=30 and score <60 then '31-60'
when score >=60 and score <90 then '51-90'
when score >=90 then '>……
[/Quote]
谢谢 我瞅瞅看

[zhanghang19890621]

[Quote=引用 3 楼 hao1hao2hao3 的回复:]
引用 2 楼 haiwer 的回复:
sql server写法


SQL code
select 范围=case when score <30 then '0-30'
when score >=30 and score <60 then '31-60'
when score >=60 and score <90 then '51-90'
when score >=90 then ……
[/Quote]
不是 就是要的mysql的写法 不过mysql的语法很多跟sql类似........