求一个MYSQL查询语句!

[stneo1990]

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就是最高

[rucypli]

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

[stneo1990]

引用 1 楼 rucypli 的回复:

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 sco……

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

[stneo1990]

引用 1 楼 rucypli 的回复:

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 sco……

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

[Rotel-刘志东]

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;

[WPooh]

嵌套查询，根据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

[WWWWA]

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