面试题:怎么把这样一个表儿
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, t1.amount, t2.amount, t3.amount, t4.amount from tableA a
join (select amount, year from teableA where year=a.year and month=1) t1
on (a.year=t1.year)
join (select amount, year from teableA where year=a.year and month=2) t2
on (a.year=t2.year)
join (select amount, year from teableA where year=a.year and month=3) t3
on (a.year=t3.year)
join (select amount, year from teableA where year=a.year and month=4) t4
on (a.year=t4.year)
a group by year;答案一、
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
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, t1.amount, t2.amount, t3.amount, t4.amount from tableA a
join (select amount, year from teableA where year=a.year and month=1) t1
on (a.year=t1.year)
join (select amount, year from teableA where year=a.year and month=2) t2
on (a.year=t2.year)
join (select amount, year from teableA where year=a.year and month=3) t3
on (a.year=t3.year)
join (select amount, year from teableA where year=a.year and month=4) t4
on (a.year=t4.year)
a group by year;答案一、
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
SELECT YEAR,
MAX(DECODE(MONTH,1,AMOUNT,0)) M1,
MAX(DECODE(MONTH,2,AMOUNT,0)) M2,
MAX(DECODE(MONTH,3,AMOUNT,0)) M3,
MAX(DECODE(MONTH,4,AMOUNT,0)) M4
FROM AAA
GROUP BY YEAR
SELECT YEAR,
SUM(CASE WHEN MONTH='1' THEN AMOUNT END) M1,
SUM(CASE WHEN MONTH='2' THEN AMOUNT END) M2,
SUM(CASE WHEN MONTH='3' THEN AMOUNT END) M3,
SUM(CASE WHEN MONTH='4' THEN AMOUNT END) M4
FROM AAA
GROUP BY YEAR
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
答案二:
SELECT YEAR,
MAX(DECODE(MONTH,1,AMOUNT,0)) M1,
MAX(DECODE(MONTH,2,AMOUNT,0)) M2,
MAX(DECODE(MONTH,3,AMOUNT,0)) M3,
MAX(DECODE(MONTH,4,AMOUNT,0)) M4
FROM AAA
GROUP BY YEAR
--1M:
SELECT YEAR,
SUM(CASE WHEN MONTH='1' THEN AMOUNT END) M1,
SUM(CASE WHEN MONTH='2' THEN AMOUNT END) M2,
SUM(CASE WHEN MONTH='3' THEN AMOUNT END) M3,
SUM(CASE WHEN MONTH='4' THEN AMOUNT END) M4
FROM AAA
GROUP BY YEAR --2M:
select year,
(select amount from aaa m where month=1 and m.year=b.year) as m1,
(select amount from aaa m where month=2 and m.year=b.year) as m2,
(select amount from aaa m where month=3 and m.year=b.year) as m3,
(select amount from aaa m where month=4 and m.year=b.year) as m4
from aaa b group by year--3M: maybe only used on oracle
SELECT YEAR,
MAX(DECODE(MONTH,1,AMOUNT,0)) M1,
MAX(DECODE(MONTH,2,AMOUNT,0)) M2,
MAX(DECODE(MONTH,3,AMOUNT,0)) M3,
MAX(DECODE(MONTH,4,AMOUNT,0)) M4
FROM AAA
GROUP BY YEAR学习了,呵呵
pivot sqlserver2005新特性,代替case和分组,行列转换问题变得很简单了