mysql - 求SQL语句写法？

Answer 1

Thank you for your help. And solved it myself. .

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;

You can also use if instead of 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

There is a wm_concat function under Oracle. Newer versions of databases need to convert the results into character types

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