求SQL语句写法？

Question

如何用SQL开通过上面的数据表生成下面的数据表格呢？
感觉怎么写都不太能搞定。
有数据库大神能指点一下不？？？

select id, name ,(case when years=1992 then cost else 0 end) as year1992 from pivot group by name;

简单的写了一下，可是这个确爆出了奇怪的错误》》

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nctest.pivot.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这要如何求解了？

Answer 1

感谢各位的帮助。以及自己解决了。。

select id, name ,
sum(case when years=1992 then cost else 0 end) as year1992,
sum(case when years=1993 then cost else 0 end) as year1993,
sum(case when years=1994 then cost else 0 end) as year1994
from pivot 
group by id, name;

也可以用if代替case when

select id, name ,
sum(if(years=1992,cost,0)) as year1992,
sum(if(years=1993,cost,0)) as year1993,
sum(if(years=1994,cost,0)) as year1994
from pivot
group by id, name;

Answer 2

Oracle下有一个wm_concat函数，版本较新的数据库需要把结果转成字符类型

Answer 3

CREATE TABLE coustmor(
id INT(10),
NAME VARCHAR(50),
YEAR VARCHAR(50) ,
cost INT(10)
)

查询：
SELECT DISTINCT coustmor.id ,coustmor.name,year1992.cost AS year1992 ,year1993.cost year1993,year1994.cost year1994
FROM coustmor NATURAL JOIN (SELECT id, cost FROM coustmor WHERE YEAR IN(1992) GROUP BY id) year1992,
(SELECT id,cost FROM coustmor WHERE YEAR IN(1993) GROUP BY id) year1993,
(SELECT id, IFNULL(cost,0) cost FROM coustmor WHERE YEAR IN(1994) GROUP BY id) year1994