mysql - 求SQL语句写法？

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