求一个MYSQL查询语句!

Question

DROP TABLE IF EXISTS n_select;
CREATE TABLE n_select(
name varchar(10) NOT NULL default '',
count char(10) NOT NULL default '',
score_one FLOAT(3) NOT NULL default 0,
score_two FLOAT(3) NOT NULL default 0
)TYPE=MyISAM;INSERT INTO n_select values('aaa','3-17','0.7','0.9');
INSERT INTO n_select values('bbb','3-17','1.6','1.8');
INSERT INTO n_select values('ccc','3-17','2.7','2.4');
INSERT INTO n_select values('aaa','3-18','1.5','1.4');
INSERT INTO n_select values('bbb','3-18','2.1','2.3');
INSERT INTO n_select values('ccc','3-18','0.6','0.8');
INSERT INTO n_select values('aaa','3-19','2.5','2.9');
INSERT INTO n_select values('bbb','3-19','0.3','0.7');
INSERT INTO n_select values('ccc','3-19','1.2','1.1');SQL语句的目的：通过一次查询，以count='3-18'的用户中,(score_one+score_two)的和DESC排序,并且连带查出count='3-17'和count='3-19'的(score_one+score_two)的和,这三个和的别名按count=3-17,count=3-18和count=3-19分别叫score_1,score_2,score_3最后想得到这样的数据结果(以count='3-18'为准)：name              score_1              score_2              score_3
bbb               2.4                  4.4                  1.0
aaa               1.6                  2.9                  5.4
ccc               5.1                  1.4                  2.3解释:在count='3-18'中,(score_one+score_two)也就是score_2的值最高的为bbb,所以b排第一位,然后score_1和score_3分别是count='3-17'和count='3-19'的值,虽然这两个值都不是最高,但是只要count='3-18'的值最高,那么bbb就是最高

Answer 1

select name,sum(case when count='3-17' then score_one+score_two end) as socre_1,
sum(case when count='3-18' then score_one+score_two end) as socre_2,
sum(case when count='3-19' then score_one+score_two end) as socre_3
from tb
group by name

Answer 2

可以解释下这个case when then的意思吗?

Answer 3

case when then已经了解了~
不过你这个好像并没有按进行order by啊?我试了下,bbb不是在第一位,它是按score_3排序的

Answer 4

select name,sum(case when count='3-17' then score_one_+score_two end) as score_1,
sum(case when count='3-18' then score_one+score_two end) as score_2,
sum(case when count='3-19' then score_one_score_two end) as score_3
from n_select
group by name;

Answer 5

select name,
 sum(if(count='3-17',score_one+score_two,0)) as socre_1,
 sum(if(count='3-18',score_one+score_two,0)) as socre_2,
 sum(if(count='3-19',score_one+score_two,0)) as socre_3
 from n_select group by name

Answer 6

嵌套查询，根据1楼修改select name,score_1,score_2,score_3 from(select name,sum(case when count='3-17' then score_one+score_two end) as score_1,
sum(case when count='3-18' then score_one+score_two end) as score_2,
sum(case when count='3-19' then score_one+score_two end) as score_3
from n_select group by name) as tt ORDER BY score_2 DESC