【讨论】这个比较有难度的SQL怎么写呢？

显示全部楼层 · 2010-3-1 11:19:07

建表语句
create table D_USER_LOG
(
USERIDNUMBER(20),
STATE VARCHAR2(50),
ADDDATE DATE
)
测试数据如下（部分）
useridstate adddate
100账面为零2010-08-26
100账面为零2010-08-25
100账面为零2010-08-24
100账面已充值2010-08-23
100账面为零2010-08-22
100账面为零2010-08-21
表中数据的最大日期就是昨日
现在的需求是：
从昨日（也就是2010-08-26）开始往前查日期，如果遇到state不为账面为零，则停止，计算中间连续的天数
比如上面，从2010-08-26往前查，到了08-23状态是为账面已充值，这个时候就不再往前查了，计算中间连续的天数为3天。
查询出这样的效果
userid days
100 3
这个SQL怎么写呢？
我想过用树形结构查询，可是那是查询出一个连续日期。像这样的往前查的好像不行吧

千问 · 2010-3-1 11:19:07

with t as (

select '账面为零' as id, to_date('2010-08-26', 'yyyy-mm-dd') as rq
from dual
union all
select '账面为零' as id, to_date('2010-08-25', 'yyyy-mm-dd') as rq
from dual
union all
select '账面为零' as id, to_date('2010-08-24', 'yyyy-mm-dd') as rq
from dual
union all
select '账面已充值' as id, to_date('2010-08-23', 'yyyy-mm-dd') as rq
from dual
union all
select '账面为零' as id, to_date('2010-08-22', 'yyyy-mm-dd') as rq
from dual
union all
select '账面为零' as id, to_date('2010-08-21', 'yyyy-mm-dd') as rq
from dual

union all
select '账面已充值' as id, to_date('2010-08-20', 'yyyy-mm-dd') as rq from dual)

select to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') - 1 - --取昨天日期
max(case
--取第一个不是账面为零的日期

when id'账面为零' then

rq

else

null

end)
from t
where t.rq'账面为零'
group by userid
-- Result

USERID
DAYS
1
100
3

千问 · 2010-3-1 11:19:07

应该不是很难吧，记录的时间都是连续的吗？如果记录时间不连续中断的时间怎么计算？

千问 · 2010-3-1 11:19:07

原帖由 6666444 于 2010-8-27 16:21 发表
应该不是很难吧，记录的时间都是连续的吗？如果记录时间不连续中断的时间怎么计算？

记录的时间是连续的，但是也可能会有中间中断的情况，时间中断不要紧，主要是看状态是不是—账面为零，如果是这个状态，就继续往前找
直到遇上状态为——账面已充值，然后就停止

千问 · 2010-3-1 11:19:07

3L的就可以了啊

千问 · 2010-3-1 11:19:07

原帖由 szusunny 于 2010-8-27 16:21 发表
-- 这个简单的语句不知道是否能满足LZ的要求.
with D_USER_LOG as(
select '100' as userid,'账面为零' as state,to_date('2010-08-26','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面为零' as state,to_date('2010-08-25','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面为零' as state,to_date('2010-08-24','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面已充值' as state,to_date('2010-08-23','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面为零' as state,to_date('2010-08-22','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面为零' as state,to_date('2010-08-21','yyyy-mm-dd') as adddate from dual)
--
select
userid,trunc(sysdate-1) - trunc(max(adddate)) as days
from D_USER_LOG
where state'账面为零'
group by userid
-- Result
USERIDDAYS
11003

szusunny 兄，按照你的我测试了，好像还有点不对
select userid,
trunc(sysdate - 1) - trunc(max(adddate)) as days
from test2
where state'账面为零'
group by userid
如图
是不是因为我测试数据有不连续的日期造成的？

千问 · 2010-3-1 11:19:07

如上面，
1007这个是没错
1014这里的4应该是1，因为从昨天开始往前找，只有08-23这天的状态是符合要求的，到了22号就已经变换了，所以就1天

千问 · 2010-3-1 11:19:07

原帖由 dingjun123 于 2010-8-27 16:33 发表
3L的就可以了啊

好像对中间有中断的日期就不行吧，dingjun兄
按照这个我测了，结果在上面图那
你看下

千问 · 2010-3-1 11:19:07

with D_USER_LOG as(
select '100' as userid,'账面为零' as state,to_date('2010-08-26','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面为零' as state,to_date('2010-08-25','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面为零' as state,to_date('2010-08-24','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面已充值' as state,to_date('2010-08-23','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面为零' as state,to_date('2010-08-22','yyyy-mm-dd') as adddate from dual union all
select '100' as userid,'账面为零' as state,to_date('2010-08-21','yyyy-mm-dd') as adddate from dual union all
select '101' as userid,'账面为零' as state,to_date('2010-08-26','yyyy-mm-dd') as adddate from dual union all
select '101' as userid,'账面为零' as state,to_date('2010-08-25','yyyy-mm-dd') as adddate from dual union all
select '101' as userid,'账面为零' as state,to_date('2010-08-24','yyyy-mm-dd') as adddate from dual union all
select '101' as userid,'账面已充值' as state,to_date('2010-08-23','yyyy-mm-dd') as adddate from dual union all
select '101' as userid,'账面为零' as state,to_date('2010-08-22','yyyy-mm-dd') as adddate from dual union all
select '101' as userid,'账面为零' as state,to_date('2010-08-21','yyyy-mm-dd') as adddate from dual)
select min(adddate), max(adddate), count(*),userid
from (select adddate + row_number() over(partition by state, userid order by adddate desc) rn,

d.*

from D_USER_LOG d
where state = '账面为零')
group by rn, userid having max(adddate) = trunc(sysdate -1)
不知道我这样写的对不对