表数据查询例题

1、有一个数据库表aaa，

year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
怎样查成这样一个结果
year  m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

解：

select year, 
(select amount from   aaa m where month=1   and m.year=aaa.year) as m1,
(select amount from   aaa m where month=2   and m.year=aaa.year) as m2,
(select amount from   aaa m where month=3   and m.year=aaa.year) as m3,
(select amount from   aaa m where month=4   and m.year=aaa.year) as m4
from aaa  group by year

2、

pid	type	resource
1337	Memory	1020
1337	CPU	70
1433	Memory	2020
1433	CPU	60

 

 

 

 

 

要变成

 

pid	Memory	CPU
1337	1020	70
1433	2020	60

 

 

 

 

解：

 

select pid

(select resource   from table m   where type=Memory and table.pid=m.pid) as Memory,

(select resource   from table m   where type=CPU and table.pid=m.pid) as CPU

from table

group by pid

后续：

查看from table m的语法使用