同一个帐号(acno)每天发生三次以上或每天发生持续5天以上 这个都是指tram是正的select a.trdt,a.acno from tb_down_adsva a
where exists(select b.trdt,b.acno from tb_down_adsva b where a.trdt>='20051001' and a.trdt<='20051030' and b.trdt>='20051001' and b.trdt<='20051030' and a.trdt=b.trdt and a.acno=b.acno and datediff(day,a.trdt,b.trdt)<=5 and count(distinct a.trdt)>=6 )这是我的想法,上面的SQL语句不能执行呀!谁能帮我解决一下,谢谢!
where exists(select b.trdt,b.acno from tb_down_adsva b where a.trdt>='20051001' and a.trdt<='20051030' and b.trdt>='20051001' and b.trdt<='20051030' and a.trdt=b.trdt and a.acno=b.acno and datediff(day,a.trdt,b.trdt)<=5 and count(distinct a.trdt)>=6 )这是我的想法,上面的SQL语句不能执行呀!谁能帮我解决一下,谢谢!
(trdt varchar(8),acno varchar(20),tram int)
insert adsva
select '20051021','1102',200 union all
select '20051022','1102',300 union all
select '20051023','1102',100 union all
select '20051024','1102',200 union all
select '20051025','1102',300 union all
select '20051026','1106',100 union all
select '20051026','1106',200 union all
select '20051026','1106',500 union all
select '20051028','1102',200 union all
select '20051029','1102',300 union all
select '20051030','1102',100 union all
select '20051031','1102',200 union all
select '20051101','1102',-100 union all
select '20051102','1102',200 union all
select '20051103','1103',500 union all
select '20051104','1104',100 union all
select '20051105','1102',100 union all
select '20051106','1102',-100 union all
select '20051107','1102',-200 union all
select '20051107','1102',-100 union all
select '20051108','1102',-100 union all
select '20051109','1102',-100 union all
select '20051110','1102',-100 union all
select '20051110','1104',500 union all
select '20051112','1102',200 union all
select '20051113','1102',300 union all
select '20051114','1102',400 union all
select '20051115','1102',100 union all
select '20051116','1102',200 union all
select '20051117','1102',100 union all
select '20051118','1102',100 union all
select '20051120','1104',500
go --过程有点复杂
select distinct acno,trdt into #1 from adsva
where tram<0
--select * from #1
SELECT id=IDENTITY(int),acno,trdt into #2 FROM #1 a
WHERE NOT EXISTS(
SELECT 1 FROM #1 WHERE acno=a.acno AND trdt=dateadd(dd,-1,a.trdt))SELECT id=IDENTITY(int),acno,trdt into #3 FROM #1 a
WHERE NOT EXISTS(
SELECT 1 FROM #1 WHERE acno=a.acno AND trdt=dateadd(dd,1,a.trdt))--得到小于0的acno的日期分布图
select a.acno,a.trdt as [Start],b.trdt as [End]
from #2 a,#3 b
where a.id=b.id and a.trdt<dateadd(dd,-3,b.trdt)--得到分布图后,应该简单吧,自己写
acno的日期分布
where tram<0我怕速度不行呀,不知道上面我的想法有没有问题,好不容易才打开这个页面,晚上网络不行,只能明天再来问了得到分布图后,应该简单吧,自己写,后面怎么处理呢?还不是很清楚?谢谢!
谢谢!
create table #adsva
(trdt varchar(8),acno varchar(20),tram int)
insert #adsva
select '20051021','1102',200 union all
select '20051022','1102',300 union all
select '20051023','1102',100 union all
select '20051024','1102',200 union all
select '20051025','1102',300 union all
select '20051026','1106',100 union all
select '20051026','1106',200 union all
select '20051026','1106',500 union all
select '20051028','1102',200 union all
select '20051029','1102',300 union all
select '20051030','1102',100 union all
select '20051031','1102',200 union all
select '20051101','1102',-100 union all
select '20051102','1102',200 union all
select '20051103','1103',500 union all
select '20051104','1104',100 union all
select '20051105','1102',100 union all
select '20051106','1102',100 union all
select '20051107','1102',-200 union all
select '20051107','1102',100 union all
select '20051108','1102',100 union all
select '20051109','1102',100 union all
select '20051110','1102',100 union all
select '20051110','1104',500 union all
select '20051112','1102',200 union all
select '20051113','1102',300 union all
select '20051114','1102',400 union all
select '20051115','1102',100 union all
select '20051116','1102',200 union all
select '20051117','1102',100 union all
select '20051118','1102',100 union all
select '20051120','1104',500
go SELECT DISTINCT trdt, acno FROM #adsva a
WHERE (SELECT COUNT(acno) FROM #adsva WHERE trdt=a.trdt AND acno=a.acno)>=3
OR (
(SELECT COUNT(DISTINCT trdt) FROM #adsva WHERE acno=a.acno AND (trdt BETWEEN a.trdt
AND CONVERT(VARCHAR(8), (CONVERT(DATETIME, a.trdt, 112)+4), 112)))=5
AND
(SELECT SUM(CASE WHEN tram>=0 THEN 0 ELSE -1 END) FROM #adsva WHERE acno=a.acno AND (trdt BETWEEN a.trdt
AND CONVERT(VARCHAR(8), (CONVERT(DATETIME, a.trdt, 112)+4), 112)))=-1
)go
drop table #adsva
go
(
rid int ,
days int
)go
insert into t1( rid ,days) values( 1, 1)
insert into t1( rid ,days) values( 1, 3)
insert into t1( rid ,days) values( 1, 4)
insert into t1( rid ,days) values( 1, 5)
insert into t1( rid ,days) values( 1, 6)
insert into t1( rid ,days) values( 1, 7)
insert into t1( rid ,days) values( 1, 9)
insert into t1( rid ,days) values( 1, 10)select a.* From t1 a
inner join t1 b on a.days = b.days
inner join t1 c on a.days = c.days + 1
inner join t1 d on a.days = d.days + 2
inner join t1 e on a.days = e.days + 3
inner join t1 f on a.days = f.days + 4
这样把连续5天的最大的天找出来了。select a.* From t1 a
inner join t1 b on a.days = b.days
inner join t1 c on a.days = c.days - 1
inner join t1 d on a.days = d.days - 2
inner join t1 e on a.days = e.days - 3
inner join t1 f on a.days = f.days - 4
这样把连续5天的最小的天找出来了。楼主你稍微转换一下就行了。
我的所有代码都是通过自己测试的.======================================
http://chinadba.cn
深圳骄子数据库技术网,最具实战经验的数据库优化高手为您服务
语句应改为
select a.* From t1 a
inner join t1 b on a.rid = b.rid and a.days = b.days
inner join t1 c on a.rid = c.rid and a.days = c.days + 1
inner join t1 d on a.rid = d.rid and a.days = d.days + 2
inner join t1 e on a.rid = e.rid and a.days = e.days + 3
inner join t1 f on a.rid = f.rid and a.days = f.days + 4如果你加上 rid 的索引,应该是非常快的。我没有这么多时间看您的表结构,还是请你看我的然后理解我的意思吧。
那我问一下,如下情况算不算
20051001 100
20051002 -200
20051003 100
20051004 100
20051006 100
五天连续出现,只有一次为负
select * from adsva a
inner join b on a.acno=b.acno and trdt between b.start and b.end
where a.trdt between '20051001' and '20051030'
就可以得到所有记录
inner join
(select * from
(SELECT a.acno,[start]=trdt,
[ends]=(
SELECT MIN(trdt) FROM adsva aa
WHERE acno=a.acno AND trdt>=a.trdt
AND NOT EXISTS(
SELECT * FROM adsva WHERE acno=aa.acno AND trdt=dateadd(dd,1,aa.trdt)))
FROM adsva a
WHERE tram>0 and NOT EXISTS(
SELECT * FROM adsva WHERE acno=a.acno and trdt=dateadd(dd,-1,a.trdt))
and trdt between '20051001' and '20051030')a
where start <dateadd(dd,-3,ends)
and (Select count(1) from adsva where acno=a.acno and trdt between a.start and a.ends and tram<0)=1)a --只有一个为负
on a.acno=b.acno and b.trdt between a.start and a.ends
哈哈,测试数据就是你给出的,还有每天发生三次tram是正以上(这里tram是正的3次),也出现负的1次,(一共是4次,3正1负)这个应该不难,自己写啦
这是我的实现你连续5天的算法。其它的出现3次那种,不太理解你的意思。select distinct a.* from adsva a
inner join adsva b on a.acno = b.acno and a.trdt = b.trdt - 1
inner join adsva c on a.acno = c.acno and a.trdt = c.trdt - 2
inner join adsva d on a.acno = d.acno and a.trdt = d.trdt - 3
inner join adsva e on a.acno = e.acno and a.trdt = e.trdt - 4
这样也行呀,你自已写好看看吧,我测过的好像有问题
create table adsva
(trdt varchar(8),acno varchar(20),tram int)
insert adsva
select '20051021','1102',200 union all
select '20051022','1102',300 union all
select '20051023','1102',100 union all
select '20051024','1102',200 union all
select '20051025','1102',300 union all
select '20051026','1106',100 union all
select '20051026','1106',200 union all
select '20051026','1106',500 union all
select '20051028','1102',200 union all
select '20051029','1102',300 union all
select '20051030','1102',100 union all
select '20051031','1102',200 union all
select '20051101','1102',-100 union all
select '20051102','1102',200 union all
select '20051103','1103',500 union all
select '20051104','1104',100 union all
select '20051105','1102',100 union all
select '20051106','1102',100 union all
select '20051107','1102',-200 union all
select '20051107','1102',100 union all
select '20051108','1102',100 union all
select '20051109','1102',100 union all
select '20051110','1102',100 union all
select '20051110','1104',500 union all
select '20051112','1102',200 union all
select '20051113','1102',300 union all
select '20051114','1102',400 union all
select '20051115','1102',100 union all
select '20051116','1102',200 union all
select '20051117','1102',100 union all
select '20051118','1102',100 union all
select '20051120','1104',500
go select distinct a.* from adsva a
inner join adsva b on a.acno = b.acno and a.trdt = dateadd(dd,-1,b.trdt)
inner join adsva c on a.acno = c.acno and a.trdt = dateadd(dd,-2,c.trdt)
inner join adsva d on a.acno = d.acno and a.trdt = dateadd(dd,-3,d.trdt)
inner join adsva e on a.acno = e.acno and a.trdt = dateadd(dd,-4,e.trdt)
结果如下:
20051021 1102 200
20051028 1102 200
20051029 1102 300
20051105 1102 100
20051106 1102 100
20051112 1102 200
20051113 1102 300
20051114 1102 400
你好,我试了你的语句,发觉过了10分钟还没有出来结果,主要是这个表很大javanow(想我生命的意义)
你好,我是根据以前的数据进行处理,没办法把 trdt 变为 datetime
==================
http://chinadba.cn
数据库优化,管理,设计,培训
(trdt varchar(8),acno varchar(20),tram int)
insert #adsva
select '20051021','1102',200 union all
select '20051022','1102',300 union all
select '20051023','1102',100 union all
select '20051024','1102',200 union all
select '20051025','1102',300 union all
select '20051026','1106',100 union all
select '20051026','1106',200 union all
select '20051026','1106',500 union all
select '20051028','1102',200 union all
select '20051029','1102',300 union all
select '20051030','1102',100 union all
select '20051031','1102',200 union all
select '20051101','1102',-100 union all
select '20051102','1102',200 union all
select '20051103','1103',500 union all
select '20051104','1104',100 union all
select '20051105','1102',100 union all
select '20051106','1102',100 union all
select '20051107','1102',-200 union all
select '20051107','1102',100 union all
select '20051108','1102',100 union all
select '20051109','1102',100 union all
select '20051110','1102',100 union all
select '20051110','1104',500 union all
select '20051112','1102',200 union all
select '20051113','1102',300 union all
select '20051114','1102',400 union all
select '20051115','1102',100 union all
select '20051116','1102',200 union all
select '20051117','1102',100 union all
select '20051118','1102',100 union all
select '20051120','1104',500
go SELECT DISTINCT trdt, acno FROM #adsva a
WHERE (SELECT COUNT(acno) FROM #adsva WHERE trdt=a.trdt AND acno=a.acno and tram>=0)>3
OR (
(SELECT COUNT(DISTINCT trdt) FROM #adsva WHERE acno=a.acno AND (trdt BETWEEN a.trdt
AND CONVERT(VARCHAR(8), (CONVERT(DATETIME, a.trdt, 112)+4), 112)))=5
AND
(SELECT SUM(CASE WHEN tram>=0 THEN 0 ELSE -1 END) FROM #adsva WHERE acno=a.acno AND (trdt BETWEEN a.trdt
AND CONVERT(VARCHAR(8), (CONVERT(DATETIME, a.trdt, 112)+4), 112)))=-1
)go
drop table #adsva
go输出结果:
trdt acno
-------- --------------------
20051028 1102
20051029 1102
20051105 1102
20051106 1102这个不对吗?
trdt acno
-------- --------------------
20051028 1102
20051029 1102
20051105 1102
20051106 1102这个不对呀,日期没有连续5天呀!
inner join adsva b on a.acno = b.acno and a.trdt = b.trdt - 1
inner join adsva c on a.acno = c.acno and a.trdt = c.trdt - 2
inner join adsva d on a.acno = d.acno and a.trdt = d.trdt - 3
inner join adsva e on a.acno = e.acno and a.trdt = e.trdt - 4你如果不是datetime,需要将 trdt 改为 convert(datetime,trdt)
inner join adsva b on b.trdt>='20051120' and b.trdt<='20051130' and a.acno = b.acno and convert(datetime,a.trdt) = convert(datetime,b.trdt) - 1
inner join adsva c on c.trdt>='20051120' and c.trdt<='20051130' and a.acno = c.acno and convert(datetime,a.trdt) = convert(datetime,c.trdt) - 2
inner join adsva d on d.trdt>='20051120' and d.trdt<='20051130' and a.acno = d.acno and convert(datetime,a.trdt) = convert(datetime,d.trdt) - 3
inner join adsva e on e.trdt>='20051120' and e.trdt<='20051130' and a.acno = e.acno and convert(datetime,a.trdt) = convert(datetime,e.trdt) - 4where a.trdt>='20051120' and a.trdt<='20051130' order by a.acno,a.trdt这样的速度也是很慢的,过了很长时间也没有出来
我觉得我写的语句已经相当优化了。
如果要避免列运算,你加个字段,就是该列的时间与 1900相差的天数。这种结构就完美了。-----------------
http://chinadba.cn
最具实战经验的优化,管理,设计,培训网站
我是站长
trdt acno tram
-------- ------- ------------
20051025 1102 100
20051026 1102 200
20051027 1102 300
20051028 1102 -100
20051029 1102 200
20051030 1102 100因为对于第一条记录来说,连续5天只有一个tram为负;对于第二条记录来说,也是连续5天只有一个tram为负。
在一时间段内
1.找出某个帐号(acno)某天发生三次以上(tram是负的只能一次,且必需有一次),
2.找出这段时间内某个帐号tram正的总次数大于10,且tram负的总次数小于4且大于1
1和2两个条件只要满足一个条件就要找出来,并要找出交易明细
满足1条件的,只要找出符合要求这一天的数据就可以了(包括trdt,acno,tram三个字段)
满足2条件的,要找出符合要求这一个账号在这段时间的明细数据(包括trdt,acno,tram三个字段)把符合1,2的条件数据要合在一起,并按帐号,日期顺序显示谢谢!
我想这样速度应当能快多了吧!