purchase_del表记录销户数据
包括字段username ,date,purchase_name
关键子是username+date
每个用户有多个销户记录,
现在要删除其中每个用户最新的两条记录(即date离现在最近的两条记录),
怎么写sql
包括字段username ,date,purchase_name
关键子是username+date
每个用户有多个销户记录,
现在要删除其中每个用户最新的两条记录(即date离现在最近的两条记录),
怎么写sql
解决方案 »
- 菜鸟求助
- 高分求教Oracle笔试题目 -- 英文Math&Sentence Structure部分--看大家英文数学功底了
- 请教各位,如何用VC将.dat格式的数据文件(二进制)导入Oracle中?!谢谢!
- 请教一个View的写法?
- oracle中job不运行
- ORA-00936:缺失表达式
- 我装的是ORACLE 8I 被我关闭了一下。再启动时就不行了.只是已启动,但不是打开状态,.急
- 我安装的是ORACLE 8。16企业版,用GHOST刻过后,该做如何配置更改,谢谢!详细步骤?
- oracle外部文件的操作
- 如何用sql取出多个小组中年级最大的学生的姓名?
- 问一个关于imp exp的问题
- 重新安装ora9i出现问题 急!!!!
delete purchase_del tb0 where exists
(
select 1 (
select rownum as sn,tb2.username,tb2.date from purchase_del tb2 where
tb2.username = tb2.username order by date desc
) tb1
where tb1.username = tb0.username
and tb1.date = tb0.date
and tb1.sn < 3
)
SQL> select * from purchase;USERNAME TDATE PURCHASE_NAME
------------------------------------------------------------ ----------- ------------a 01/01/2007 a1
a 02/01/2007 a1
a 03/01/2007 a1
b 02/01/2007 b1
b 01/01/2007 b1
b 03/01/2007 b16 rows selected查询每个类别最新的前两条:
SQL> select * from (
2 select username,tdate,row_number()over(partition by username order by tdate desc) as idx from purchase
3 ) a where a.idx<=2;USERNAME TDATE IDX
------------------------------------------------------------ ----------- ----------
a 03/01/2007 1
a 02/01/2007 2
b 03/01/2007 1
b 02/01/2007 2
感觉阿代的sql不太对,tb2.username = tb2.username 是多余的,而且这样只会删掉两条记录,而不是每个用户的最新两条
”视爱情为奢侈品“是高手啊,sql经验证是正确的,这是查询每个类别最新的前两条,小弟还是不清楚删除的sql怎样写
请赐教
和delete的原理是一样的。lz你要理解,你的问题最核心的是rownum这个oracle的伪列
(select number from(select date ,instance,status,number from Test as a
where date<>
(select max(date) from Test
where a.number=number))
and rownum<=100
如果有
删除就容易多了
delete purchase_del tb0 where exists
(
select 1 (
select rownum as sn,tb2.username,tb2.date from purchase_del tb2 where
tb2.username = tb0.username order by date desc
) tb1
where tb1.username = tb0.username
and tb1.date = tb0.date
and tb1.sn < 3
)
到底错误出在哪里拉
(select min( to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')) as distwo ,srq_cust as srq_custmintwo
from SRQ_MSTR where srq_id not in (select srq_id from srq_mstr,
(select min( to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')) as dis ,srq_cust as srq_custmin
from SRQ_MSTR group by srq_cust) where srq_cust=srq_custmin and to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')=dis) group by srq_cust)
where srq_cust=srq_custmintwo and to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')=distwo
union
select * from srq_mstr,
(select min( to_char(sysdate,'yyyymmdd')-to_char
(srq_date,'yyyymmdd')) as dis ,srq_cust as srq_custmin
from SRQ_MSTR group by srq_cust) where srq_cust=srq_custmin
and to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')=dis笨办法 上面表结构:srq_mstr(srq_id,srq_cust,srq_date) srq_id 在表中唯一
(select min( to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')) as distwo ,srq_cust as srq_custmintwo
from SRQ_MSTR where srq_id not in (select srq_id from srq_mstr,
(select min( to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')) as dis ,srq_cust as srq_custmin
from SRQ_MSTR group by srq_cust) where srq_cust=srq_custmin and to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')=dis) group by srq_cust)
where srq_cust=srq_custmintwo and to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')=distwo
union
select * from srq_mstr,
(select min( to_char(sysdate,'yyyymmdd')-to_char
(srq_date,'yyyymmdd')) as dis ,srq_cust as srq_custmin
from SRQ_MSTR group by srq_cust) where srq_cust=srq_custmin
and to_char(sysdate,'yyyymmdd')-to_char(srq_date,'yyyymmdd')=dis)