有一个消费记录表,表中的字段如下
会员卡号(m_no) 消费金额(amount) 消费日期(p_day)
100015 159.30 2007 06 17 18:50
100598 1125.60 2007 06 15 09:50
````````````````````````````````````````````````````````
````````````````````````````````````````````````````````
表中有10万条记录
如何通过最快的方法查找出5月份中，一天内消费金额大于2000元的会员卡号呢？
------解决方案--------------------
select m_no from table
where to_char(p_day, 'yyyymm ')= '200705 '
group by m_no,to_char(p_day, 'dd ') having count(amount)> 2000
不知速度怎样
------解决方案--------------------
把COUNT改成SUM就行了.
select m_no from table
where to_char(p_day, 'yyyymm ')= '200705 '
group by m_no,to_char(p_day, 'dd ') having SUM(amount)> 2000