有一个表card
列属性
(1)id
(2)acount //消费金额
(3)asn //卡号
(4)consumtime //消费时间我想查找,任意10分钟之内,找到同一张卡,消费金额相同的信息(就是同一张卡在十分钟之内消费了相同的金额)
输出卡号和消费信息以及消费时间
我之前写的是这样,我随便查找了一个日期
select * from card where acount || asn in
(select acount ||asn from tbl_cardtrade where trunc(OCCURTIME)=to_date('2008-8-1','yyyy-mm-dd')
group by acount ||asn having count(AMOUNT)>=2))
有高手指导下吗
列属性
(1)id
(2)acount //消费金额
(3)asn //卡号
(4)consumtime //消费时间我想查找,任意10分钟之内,找到同一张卡,消费金额相同的信息(就是同一张卡在十分钟之内消费了相同的金额)
输出卡号和消费信息以及消费时间
我之前写的是这样,我随便查找了一个日期
select * from card where acount || asn in
(select acount ||asn from tbl_cardtrade where trunc(OCCURTIME)=to_date('2008-8-1','yyyy-mm-dd')
group by acount ||asn having count(AMOUNT)>=2))
有高手指导下吗
解决方案 »
- ocibindbypos中的参数rcodep到底指什么,查了很多资料还是不理解,求点拨!!
- 求partition的用法 报错invalid SQL statement
- Oracle10G内存占用99%以上
- 如何将某一时间段的记录分解成多条?
- 请教一sql
- oracle中有像server一样的@@rowcount记录所选的记录数呢?rownum好像不行呀
- 关于ORACLE冷备份恢复的问题!!!急!!!!SOS!!!
- 【delphi中利用ClientDataSet和ADOQuery分别如何调用oracle中的存储过程-JCC0128-400分奉上】
- 请问一下,oracle的developer在什么地方?我装的服务器版,可是找不到
- 怎么在OEM2。0里找不到数据庫!在线等待!
- BULK COLLECT INTO问题
- sqlload 导入问题
(select acount ||asn from tbl_cardtrade where trunc(OCCURTIME)=to_date('2008-8-1','yyyy-mm-dd')
group by asn ||asn having sum(AMOUNT)>=2))
select asn,sum(amount) as tamount from card
where trunc(OCCURTIME)=to_date('2008-8-1','yyyy-mm-dd')
group by asn
having sum(AMOUNT)>=2)
FROM card
WHERE (acount, asn) IN (SELECT acount, asn, MIN(OCCURTIME) mintime, MAX(OCCURTIME) maxtime
FROM tbl_cardtrade
WHERE trunc(OCCURTIME) = to_date('2008-8-1', 'yyyy-mm-dd')
GROUP BY acount, asn
HAVING COUNT(AMOUNT) >= 2)
WHERE maxtime - mintime < 1 / 144
SELECT *
FROM card b
WHERE EXISTS (SELECT *
FROM ((SELECT acount, asn, MIN(OCCURTIME) mintime, MAX(OCCURTIME) maxtime
FROM tbl_cardtrade
WHERE trunc(OCCURTIME) = to_date('2008-8-1', 'yyyy-mm-dd')
GROUP BY acount, asn
HAVING COUNT(AMOUNT) >= 2) WHERE maxtime - mintime < 1 / 144) a
WHERE a.asn = b.asn AND
a.acount = b.acount)
是任意的十分钟之内,同一张卡在十分钟之内消费了相同的金额
2008-8-1 是我自己先从简单的做起的
select '1' id, 19 account,'0001' asn,to_date('2009-01-02 12:16:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
union all
select '2' id, 20 account,'0002' asn,to_date('2009-01-02 12:14:23','yyyy-mm-dd hh24:mi:ss') consumticme from dual
union all
select '3' id, 19 account,'0001' asn,to_date('2009-01-02 12:15:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
union all
select '4' id, 20 account,'0002' asn,to_date('2009-01-02 12:27:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
union all
select '5' id, 17 account,'0003' asn,to_date('2009-01-02 12:11:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
union all
select '6' id, 15 account,'0002' asn,to_date('2009-01-02 12:17:22','yyyy-mm-dd hh24:mi:ss') consumtime from dual
union all
select '4' id, 20 account,'0002' asn,to_date('2009-01-02 12:32:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
)
select * from (
select c1.id,c1.account,c1.asn,c1.consumtime,
lead(consumtime) over(partition by c1.asn,c1.account order by consumtime) nextconsumtime from card c1,
(
select account,asn from card group by account,asn having count(asn) > 1
) c2 where c1.account = c2.account and c1.asn = c2.asn
) where nextconsumtime is not null and nextconsumtime <= consumtime+1/(24*60)*10
试一试不知道,符合要求不
SQL>
SQL> with card as(
2 select '1' id, 19 account,'0001' asn,to_date('2009-01-02 12:16:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
3 union all
4 select '2' id, 20 account,'0002' asn,to_date('2009-01-02 12:14:23','yyyy-mm-dd hh24:mi:ss') consumticme from dual
5 union all
6 select '3' id, 19 account,'0001' asn,to_date('2009-01-02 12:15:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
7 union all
8 select '4' id, 11 account,'0002' asn,to_date('2009-01-02 12:27:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
9 union all
10 select '5' id, 17 account,'0003' asn,to_date('2009-01-02 12:11:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
11 union all
12 select '6' id, 15 account,'0002' asn,to_date('2009-01-02 12:17:22','yyyy-mm-dd hh24:mi:ss') consumtime from dual
13 union all
14 select '4' id, 20 account,'0002' asn,to_date('2009-01-02 12:32:23','yyyy-mm-dd hh24:mi:ss') consumtime from dual
15 )
16 select account, asn
17 from (select id,
18 account,
19 asn,
20 consumtime,
21 lead(consumtime, 1) over(partition by asn, account order by consumtime) intev,--比较时间用
22 dense_rank() over(partition by asn, account order by 1) --比较消费金额
23 from card)
24 where (intev <= consumtime + 1 / (24 * 60) * 10 or intev is null) ---两次消费在10分钟以内
25 group by account, asn
26 having count(*) >= 2 -- 消费金额相同
27 / ACCOUNT ASN
---------- ----
19 0001SQL>
我重新完整的说下我的问题
首先表结构如下
ID AMOUNT(消费金额) BALANCE(余额) CARDORGY(类型) OCCURTIME(时间) CARDNUM(卡号)
类型,有消费,充值,07代表消费。
我想查找,任意10分钟之内,找到同一张卡,消费金额相同的信息(就是同一张卡在十分钟之内消费了相同的金额)
可能之前我是想用一条sql语句就能得出结果,看来还不行
因为这个功能,我想用java来实现的
比如先查出消费金额相同的卡信息出来,再对这个结果集进行操作
各位大哥再给个思路
id amount carduser occurtime
1 -1000 48 2008-8-1 10:57:55
2 -1000 48 2008-8-1 9:10:33
3 -1000 47 2008-8-5 9:10:44
4 -1000 47 2008-8-5 9:11:43我想要的就是
3 -1000 47 2008-8-5 9:10:44
4 -1000 47 2008-8-5 9:11:43
同一张卡十分钟内,消费了相同的金额 -1000表示消费了1000元
现在呢,比如说我得到的结果集是原始数据行,怎么操作这个,添加循环啊,判断啊,得到我想要的那个
where
t1.carduser=t2.carduser and t1.amount=t2.amount and
abs(t1.occurtime- t2.occurtime)<1/144
and t1.id<>t2.id意思是说t1和t2是同一个表,限制条件是卡号相同、金额相同、主键不同(使得去掉同一条记录的可能)但时间相差绝对值小于十分钟。
得出的结果只要一个表的就可以t1.*
with card as(
select 1 id, -1000 amount, 48 carduser, to_date('2008-08-01 10:57:55','yyyy-mm-dd hh24:mi:ss') occurtime from dual union
select 2 id, -1000 amount, 48 carduser, to_date('2008-08-01 09:10:33','yyyy-mm-dd hh24:mi:ss') occurtime from dual union
select 3 id, -1000 amount, 47 carduser, to_date('2008-08-05 09:10:44','yyyy-mm-dd hh24:mi:ss') occurtime from dual union
select 4 id, -1000 amount, 47 carduser, to_date('2008-08-05 09:11:43','yyyy-mm-dd hh24:mi:ss') occurtime from dual)
select distinct b.id, b.amount, b.carduser, b.occurtime
from (select card.*,
lead(occurtime, 1) over(partition by carduser, amount order by occurtime) leadtime
from card) t,
card b
where t.leadtime - t.occurtime < 1 / 144
and t.carduser = b.carduser
and t.amount = b.amount
and (t.occurtime = b.occurtime or t.leadtime = b.occurtime)
order by b.occurtime
可是有个奇怪的问题,同样的sql语句,在.jsp页面显示的的时间没有时、分、秒,在pl/sql里面却可以看见
时间转字符串的时候设置好正确的format格式
SimpleDateFormat dateformat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
out.println(dateformat.format(rs.getTime("occurtime")));
}
表里面的数据是2008-8-1 11:30:22
jsp页面显示 1970-01-01 11:30:22
为什么呢