面试题:怎么把这样一个表儿
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我对m 很不理解,望达人给我解释一下
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我对m 很不理解,望达人给我解释一下
解决方案 »
- 想问一下SQL 2005客户端安装问题
- 请教,如何获得数据库中某表的最大n个ID,帮忙写个SQL语句,给大家拜个年了,3Q~
- 请教一个最简单,但是却很重要的问题,我百思不得其解,请有经验的朋友来看一下
- 在线等待:MS SQL里面有没有oracle里的序列或者用其他办法来模拟oracle里的序列!!!
- ■■■■■■■■寻求旋转距阵的最佳方案!最佳方案者再赠送200分!■■■■■■■■
- 请大家帮忙 急!!!!!!!!!!!!
- 请问使用SQL Server,SqlSvr内存空间一直在增加的解决办法
- 新手入门,请高手指点!
- 删除操作,请指教!
- sql 分类查询按月统计问题
- 求一SQL写法
- sql 判断 when case 吗?
declare @tb table (year int,month int,amount decimal(10,2))
insert into @tb 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 1991,1,2.1
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, [1] m1,[2] as m2,[3] as m3,[4] as m4 from @tb a pivot (min(amount) for month in ([1],[2],[3],[4])) bYEAR m1 m2 m3 m4
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1991 1.10 1.20 1.30 1.40
1992 2.10 2.20 2.30 2.40(2 行受影响)
就是AMOUNT呀,同一年不同月份的
declare @tb table (year int,month int,amount decimal(10,2))
insert into @tb 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 1991,1,2.1
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,
MAX(CASE MONTH WHEN 1 THEN amount ELSE 0 END)M1,
MAX(CASE MONTH WHEN 2 THEN amount ELSE 0 END)M2,
MAX(CASE MONTH WHEN 3 THEN amount ELSE 0 END)M3,
MAX(CASE MONTH WHEN 4 THEN amount ELSE 0 END)M4
FROM @TB
GROUP BY YEARYEAR M1 M2 M3 M4
1991 2.10 1.20 1.30 1.40
1992 2.10 2.20 2.30 2.40