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

建立表格 coustmor(
id INT(10),
NAME VARCHAR(50),
YEAR VARCHAR(50) ,
cost INT(10)
)

)

查詢：
SELECT DISTINCT coustmor.id ,coustmor.name,year1992.cost ASyear1992 ,year1993.costyear1993,year1994.costyear1994<, Iustco下來1992) GROUP BY id）1992年，
（SELECT id，cost FROM coustmor WHERE YEAR IN（1993）GROUP BY id）1993年，
（SELECT id，IFNULL（cost，0）cost FROMco IN（Amorst WHERE Yco IN（Amorst WHEREY EAR） 1994）按id分組）1994年