数据库表结构
id hotelcd Score year month
1 01 100 2012 5
2 02 100 2012 7
3 01 90 2013 6
4 02 100 2013 7怎么计算去年同共期平均得分及同比得分? 查询条件是 2013 01-01 -2013 07-25SQL数据库
id hotelcd Score year month
1 01 100 2012 5
2 02 100 2012 7
3 01 90 2013 6
4 02 100 2013 7怎么计算去年同共期平均得分及同比得分? 查询条件是 2013 01-01 -2013 07-25SQL数据库
select AVG(score),
(select AVG(score) from 表名 where year=2012 and month between 1 and 7) from 表名
where year=2013 and month between 1 and 7
create table hu
(id int, hotelcd varchar(5), Score int, year varchar(6), month varchar(6))insert into hu
select 1, '01', 100, '2012', '5' union all
select 2, '02', 100, '2012', '7' union all
select 3, '01', 90, '2013', '6' union all
select 4, '02', 100, '2013', '7'
select a.hotelcd,a.Score,a.[year],a.[month],
(select b.Score from hu b
where datediff(m,
cast(b.[year]+'-'+b.[month]+'-01' as datetime),
cast(a.[year]+'-'+a.[month]+'-01' as datetime))=12
) '年同共期平均得分'
from hu a
where cast(a.[year]+'-'+a.[month]+'-01' as datetime) between '2013-01-01' and '2013-07-25'
/*
hotelcd Score year month 年同共期平均得分
------- ----------- ------ ------ -----------
01 90 2013 6 NULL
02 100 2013 7 100(2 row(s) affected)
*/
(id int, hotelcd varchar(5), Score int, year varchar(6), month varchar(6))
insert into hu
select 1, '01', 100, '2012', '5' union all
select 2, '02', 100, '2012', '7' union all
select 3, '01', 90, '2013', '6' union all
select 4, '02', 100, '2013', '7'
select a.hotelcd,a.Score,a.[year],a.[month],
(select b.Score from hu b
where datediff(m,
cast(b.[year]+'-'+b.[month]+'-01' as datetime),
cast(a.[year]+'-'+a.[month]+'-01' as datetime))=12
) '年同共期平均得分'
from hu a
where cast(a.[year]+'-'+a.[month]+'-01' as datetime) between '2013-01-01' and '2013-07-25'
/*
hotelcd Score year month 年同共期平均得分
------- ----------- ------ ------ -----------
01 90 2013 6 NULL
02 100 2013 7 100
(2 row(s) affected)
*/