--测试数据--create table lag_test (
area VARCHAR2(20),
arttype VARCHAR2(10),
allcount NUMBER(8),
acct_month VARCHAR2(7)
); insert into lag_test
select '北京', '说', 15, '201201' from dual
union all
select '北京', '说', 12, '201202' from dual
union all
select '北京', '说', 21, '201203' from dual
union all
select '北京', '说', 31, '201204' from dual
union all
select '北京', '学', 14, '201201' from dual
union all
select '北京', '学', 12, '201202' from dual
union all
select '北京', '学', 21, '201203' from dual
union all
select '北京', '学', 15, '201204' from dual
union all
select '北京', '逗', 15, '201201' from dual
union all
select '北京', '逗', 16, '201202' from dual
union all
select '北京', '逗', 17, '201203' from dual
union all
select '北京', '逗', 18, '201204' from dual
--查询1--select t.area,
t.arttype,
t.allcount,
t.acct_month,
lag(t.allcount, 1, 0) over(partition by area, arttype order by acct_month) as pre_month_allcount
from lag_test t
where t.acct_month='201203'
--查询1结果--
AREA ARTTYPE ALLCOUNT ACCT_MONTH PRE_MONTH_ALLCOUNT
1 北京 逗 17 201203 0
2 北京 说 21 201203 0
3 北京 学 21 201203 0
--查询2--select * from
(select t.area,
t.arttype,
t.allcount,
t.acct_month,
lag(t.allcount, 1, 0) over(partition by area, arttype order by acct_month) as pre_month_allcount
from lag_test t)
where acct_month='201203'
--查询2结果--
AREA ARTTYPE ALLCOUNT ACCT_MONTH PRE_MONTH_ALLCOUNT
1 北京 逗 17 201203 16
2 北京 说 21 201203 12
3 北京 学 21 201203 12问题:为何查询1和查询2得出的结果不同呢?
area VARCHAR2(20),
arttype VARCHAR2(10),
allcount NUMBER(8),
acct_month VARCHAR2(7)
); insert into lag_test
select '北京', '说', 15, '201201' from dual
union all
select '北京', '说', 12, '201202' from dual
union all
select '北京', '说', 21, '201203' from dual
union all
select '北京', '说', 31, '201204' from dual
union all
select '北京', '学', 14, '201201' from dual
union all
select '北京', '学', 12, '201202' from dual
union all
select '北京', '学', 21, '201203' from dual
union all
select '北京', '学', 15, '201204' from dual
union all
select '北京', '逗', 15, '201201' from dual
union all
select '北京', '逗', 16, '201202' from dual
union all
select '北京', '逗', 17, '201203' from dual
union all
select '北京', '逗', 18, '201204' from dual
--查询1--select t.area,
t.arttype,
t.allcount,
t.acct_month,
lag(t.allcount, 1, 0) over(partition by area, arttype order by acct_month) as pre_month_allcount
from lag_test t
where t.acct_month='201203'
--查询1结果--
AREA ARTTYPE ALLCOUNT ACCT_MONTH PRE_MONTH_ALLCOUNT
1 北京 逗 17 201203 0
2 北京 说 21 201203 0
3 北京 学 21 201203 0
--查询2--select * from
(select t.area,
t.arttype,
t.allcount,
t.acct_month,
lag(t.allcount, 1, 0) over(partition by area, arttype order by acct_month) as pre_month_allcount
from lag_test t)
where acct_month='201203'
--查询2结果--
AREA ARTTYPE ALLCOUNT ACCT_MONTH PRE_MONTH_ALLCOUNT
1 北京 逗 17 201203 16
2 北京 说 21 201203 12
3 北京 学 21 201203 12问题:为何查询1和查询2得出的结果不同呢?
所以针对area, arttype的前一条记录都是没有的,就是0SQL2是计算出area, arttype的前一条记录后,然后再通过查询条件t.acct_month='201203'
查出记录。