怎样在一个查询中查询出一个表中相隔月份记录，并放在一条记录显示，我描述不太清晰，看表结构吧
本帖最后由 dnkaka 于 2013-03-01 09:56:43 编辑 现在是由这样一个表结构：
id roomno year month cost
1 2001 2012 1 500
2 2002 2012 1 600
3 2001 2012 2 300
4 2002 2012 2 700
5 2001 2013 1 650
6 2002 2013 1 750
7 2001 2013 2 800
8 2002 2013 2 400
现在想查询出这种效果：
roomno year month cost roomno year month cost
2001 2012 1 500 2001 2012 2 300
2002 2012 1 600 2002 2012 2 700
2001 2013 1 650 2001 2013 2 800
2002 2013 1 750 2002 2013 2 400
相隔月份
[解决办法]
行列转化？？
[解决办法]
with tb (id, roomno, year, month , cost)as(
select 1, 2001, 2012, 1, 500 union all
select 2, 2002, 2012, 1, 600 union all
select 3, 2001, 2012, 2, 300 union all
select 4, 2002, 2012, 2, 700 union all
select 5, 2001, 2013, 1, 650 union all
select 6, 2002, 2013, 1, 750 union all
select 7, 2001, 2013, 2, 800 union all
select 8, 2002, 2013, 2, 400
),tbb as(
select roomno=(case when month =1 then roomno else null end),
year=(case when month=1 then year else null end),month=1,
cost=(case when month=1 then cost else null end) from tb
where (case when month =1 then roomno else null end) is not null
),tbbb as(
select roomno=(case when month =2 then roomno else null end),
year=(case when month=2 then year else null end),month=2,
cost=(case when month=2 then cost else null end) from tb
where (case when month =2 then roomno else null end) is not null)
select * from tbb left join tbbb on tbb.roomno=tbbb.roomno and tbb.year=tbbb.year
懒得整理了，这样看吧..
[解决办法]
with a as(
select 2001 as roomo,2012 as year,1 as month,500 as cost union all
select 2002 as roomo,2012 as year,1 as month,600 as cost union all
select 2001 as roomo,2012 as year,2 as month,300 as cost union all
select 2002 as roomo,2012 as year,2 as month,700 as cost union all
select 2001 as roomo,2012 as year,3 as month,300 as cost union all
select 2002 as roomo,2012 as year,3 as month,700 as cost union all
select 2001 as roomo,2013 as year,1 as month,650 as cost union all
select 2002 as roomo,2013 as year,1 as month,750 as cost union all
select 2001 as roomo,2013 as year,2 as month,800 as cost union all
select 2002 as roomo,2013 as year,2 as month,400 as cost union all
select 2001 as roomo,2013 as year,3 as month,800 as cost union all
select 2002 as roomo,2013 as year,3 as month,400 as cost union all
select 2001 as roomo,2014 as year,1 as month,650 as cost union all
select 2002 as roomo,2014 as year,1 as month,750 as cost union all
select 2001 as roomo,2014 as year,2 as month,800 as cost union all
select 2002 as roomo,2014 as year,2 as month,400 as cost union all
select 2001 as roomo,2014 as year,3 as month,800 as cost union all
select 2002 as roomo,2014 as year,3 as month,400 as cost
)
select *
from a
join a as b on a.roomo = b.roomo and a.[year] = b.[year] and a.month < b.month
join a as c on a.roomo = c.roomo and a.[year] = c.[year] and b.month < c.month and a.month < c.month
where a.month = 1
select *
from a
join a as b on a.roomo = b.roomo and a.[year] = b.[year] and a.month < b.month
join a as c on a.roomo = c.roomo and a.[year] = c.[year] and b.month < c.month and a.month < c.month
where a.month = 1
给你的示例稍微扩展了一下 如果如果多1列就要多自关联一次 如果只是两列就可以把 join c的部分注释掉
roomo year month cost roomo year month cost roomo year month cost
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2001 2012 1 500 2001 2012 2 300 2001 2012 3 300
2002 2012 1 600 2002 2012 2 700 2002 2012 3 700
2001 2013 1 650 2001 2013 2 800 2001 2013 3 800
2002 2013 1 750 2002 2013 2 400 2002 2013 3 400
2001 2014 1 650 2001 2014 2 800 2001 2014 3 800
2002 2014 1 750 2002 2014 2 400 2002 2014 3 400
------解决方案--------------------
上面多复制了一遍查询可以删除掉