表1
year month account
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
怎样用标准sql查询如下信息: year 1月 2月 3月 4月
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
year month account
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
怎样用标准sql查询如下信息: year 1月 2月 3月 4月
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
select year,
(select account from a as a1 where a1.year=a.year and a1.month=1 ) as 一月,
(select account from a as a1 where a1.year=a.year and a1.month=2 ) as 二月,
(select account from a as a1 where a1.year=a.year and a1.month=3 ) as 三月
from a group by year
t1.year as 年 ,
t2.account as 一月,
t3.account as 二月,
t4.account as 三月,
t5.account as 四月 from
table t1,
table t2,
table t3,
table t4
table t5where t1.year = t2.year
and t2.year = t3.year
and t3.year = t4.year
and t4.year = t5.year
and t2.month = 1
and t3.month = 2
and t4.month = 3
and t4.month = 4
行列互换,,自己查吧,实在不愿意写
insert test
select 1991, 1, 1.1 union all
select 1991, 2, 1.2 union all
select 1991, 3, 1.3 union all
select 1991, 4, 1.4 union all
select 1992, 1, 2.1 union all
select 1992 ,2 ,2.2 union all
select 1992 ,3 ,2.3 union all
select 1992, 4, 2.4
select year,
sum(case when month=1 then account else null end) as '1月',
sum(case when month=2 then account else null end) as '2月',
sum(case when month=3 then account else null end) as '3月',
sum(case when month=4 then account else null end) as '4月'
from test
group by yeardrop table test
year 1月 2月 3月 4月
------- ----- ----- ----- -----
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4