有一个表tb结构如下:
trdt 日期
acno 帐号
tram 金额示例如下:
20060510 101 190.4
20060510 102 103.4
20060510 103 140.4
20060510 104 1004.4
20060510 105 7004.4
20060511 101 140.4
20060511 102 103.4
20060511 103 140.4
20060511 104 1004.4
20060511 105 7004.4
20060512 101 190.4
20060512 102 203.4
20060512 103 140.4
20060512 104 5004.4
20060512 105 9004.4
20060512 106 2004.4现要相求出任意两个时间点余额相差超过1000的帐户如求20060510与20060512
得出的结果为:
104 5004.4 1004.4
105 9004.4 7004.4
106 2004.4 0106 由于在5.10时不存在,属于新增加,金额也超过1000请问如何写SQL语句?谢谢!!!
我自己写的SQL:
select a.acno,a.tram,b.tram from
(select * from tb where trdt='20060512') a
left join
(select * from tb where trdt='20060510') b
on a.acno=b.acno and abs(a.tram-b.tram)>=1000
这个表比较大,如何写,速度也会快一点?谢谢!
trdt 日期
acno 帐号
tram 金额示例如下:
20060510 101 190.4
20060510 102 103.4
20060510 103 140.4
20060510 104 1004.4
20060510 105 7004.4
20060511 101 140.4
20060511 102 103.4
20060511 103 140.4
20060511 104 1004.4
20060511 105 7004.4
20060512 101 190.4
20060512 102 203.4
20060512 103 140.4
20060512 104 5004.4
20060512 105 9004.4
20060512 106 2004.4现要相求出任意两个时间点余额相差超过1000的帐户如求20060510与20060512
得出的结果为:
104 5004.4 1004.4
105 9004.4 7004.4
106 2004.4 0106 由于在5.10时不存在,属于新增加,金额也超过1000请问如何写SQL语句?谢谢!!!
我自己写的SQL:
select a.acno,a.tram,b.tram from
(select * from tb where trdt='20060512') a
left join
(select * from tb where trdt='20060510') b
on a.acno=b.acno and abs(a.tram-b.tram)>=1000
这个表比较大,如何写,速度也会快一点?谢谢!
select acno,min(tram),max(tram)
from
tb
where trdt between '20060512' and '20060510'
group by acno
having
max(tram)-min(tram)>=1000
-----------------------------------
select a.acno,min(a.tram),max(a.tram)
from tb a
inner join
(
select acno,min(trdt) as startdate,max(trdt) as enddate
from
tb
where trdt between '20060512' and '20060510'
group by acno
)b
on
a.acno=b.acno
and
(
a.trdt=b.startdate
or
a.trdt=b.enddate
)
group by a.acno
having
max(a.tram)-min(a.tram)>=1000
(
trdt varchar(8),
acno int,
tram money
)
insert into @t
select '20060510',101,190.4 union all
select '20060510',102,103.4 union all
select '20060510',103,140.4 union all
select '20060510',104,1004.4 union all
select '20060510',105,7004.4 union all
select '20060511',101,140.4 union all
select '20060511',102,103.4 union all
select '20060511',103,140.4 union all
select '20060511',104,1004.4 union all
select '20060511',105,7004.4 union all
select '20060512',101,140.4 union all
select '20060512',102,203.4 union all
select '20060512',103,140.4 union all
select '20060512',104,5004.4 union all
select '20060512',105,9004.4 union all
select '20060512',106,2004.4select acno,min(tram),max(tram)
from
@t
where trdt between '20060510' and '20060512'
group by acno
having
(max(tram)-min(tram)>=1000)
or
count (acno)=1select a.acno,min(a.tram),max(a.tram)
from @t a
inner join
(
select acno,min(trdt) as startdate,max(trdt) as enddate
from
@t
where trdt between '20060510' and '20060512'
group by acno
)b
on
a.acno=b.acno
and
(
a.trdt=b.startdate
or
a.trdt=b.enddate
)
group by a.acno
having
(max(a.tram)-min(a.tram)>=1000)
or
(count (a.acno)=1)/*
acno
----------- --------------------- ---------------------
104 1004.4000 5004.4000
105 7004.4000 9004.4000
106 2004.4000 2004.4000
acno
----------- --------------------- ---------------------
104 1004.4000 5004.4000
105 7004.4000 9004.4000
106 2004.4000 2004.4000
*/
trdt 日期
acno 帐号
tram 金额现要相求出任意两个时间点余额相差超过1000的帐户
如求20060510与20060512declare @dOneDate varchar(20),@dOtherDate varchar(20)
select @dOneDate='2006-05-10'
,@dOtherDate='2006-05-12'
select case when isnull(A.acno,'')='' then B.trdt else A.trdt end trdt
,case when isnull(A.acno,'')='' then B.acno else A.acno end acno
,isnull(A.tram,0) Atram
,isnull(B.tram,0) Btram
from
(
select * from tb
where convert(char(10),trdt,120)=@dOneDate
)A
full join
(
select * from tb where convert(char(10),trdt,120)=@dOtherDate
) B
on A.acno=B.acno
where abs(isnull(A.tram,0)-isnull(B.tram,0))>1000
from tb a
inner join
(
select acno,min(trdt) as startdate,max(trdt) as enddate
from
tb
where trdt between '20060510' and '20060512'
group by acno
)b
on
a.acno=b.acno
and
(
a.trdt=b.startdate
or
a.trdt=b.enddate
)
group by a.acno
having
(max(a.tram)-min(a.tram)>=1000)
or
(count (a.acno)=1)