我现在有三个表,一个是客户积分档案表yx_jfdab记录了每一位客户各月的积分,一个是客户兑奖积分累加表(yx_jfdab)记录每一个客户每一次兑奖后的用掉的累加积分,其中XFJF为消费积分,ZWJF为在网积分,JLJF为奖励各分,yd为已兑的拼声。三客户档案表(yx_yhdab)记录客户基本信息,我现在要从总积分>500的记录中(从积分高低排序)取出前任意名,请问这条sql语句该怎么写啊
我写的SQL语句如下,通过ROWNUM>50取出的好像不是总积分按排序后的前50名,虽然能取出50名也按从高到低排序,但是好像不是所有记录中总积分最高的在最前,而是当前显示的前50条中的记录的最高分最前,请问该怎么办?同时请问该语句该怎么优化
非常感谢
select rownum ,a.dhhm dhhm,c.khmc khmc,a.zxfjf-nvl(b.ydxfjf,0) dqxfjf,a.zzwjf-nvl(b.ydzwjf,0) dqzwjf,a.zjljf-nvl(b.ydjljf,0) dqjljf
,a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0) zjf,nvl(b.ljdf,0) ljdf,nvl(b.ljjzjf,0) ljjzjf from
(select dhhm,sum(xfjf) zxfjf,sum(zwjf) zzwjf,sum(jljf) zjlj from yx_jfdab group by dhhm)A,
(select dhhm,ydxfjf,ydzwjf,ydjljf,ljdf,ljjzjf from yx_jfdjljb)B,
(select dhhm,khmc,blzd8 from jfxt.yx_yhdab)C
where a.dhhm=b.dhhm(+) and a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0)>500
and a.dhhm=c.dhhm and rownum<50 order by zjf desc;
我写的SQL语句如下,通过ROWNUM>50取出的好像不是总积分按排序后的前50名,虽然能取出50名也按从高到低排序,但是好像不是所有记录中总积分最高的在最前,而是当前显示的前50条中的记录的最高分最前,请问该怎么办?同时请问该语句该怎么优化
非常感谢
select rownum ,a.dhhm dhhm,c.khmc khmc,a.zxfjf-nvl(b.ydxfjf,0) dqxfjf,a.zzwjf-nvl(b.ydzwjf,0) dqzwjf,a.zjljf-nvl(b.ydjljf,0) dqjljf
,a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0) zjf,nvl(b.ljdf,0) ljdf,nvl(b.ljjzjf,0) ljjzjf from
(select dhhm,sum(xfjf) zxfjf,sum(zwjf) zzwjf,sum(jljf) zjlj from yx_jfdab group by dhhm)A,
(select dhhm,ydxfjf,ydzwjf,ydjljf,ljdf,ljjzjf from yx_jfdjljb)B,
(select dhhm,khmc,blzd8 from jfxt.yx_yhdab)C
where a.dhhm=b.dhhm(+) and a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0)>500
and a.dhhm=c.dhhm and rownum<50 order by zjf desc;
解决方案 »
- 怎样不通过改变字体大小 改变dbgrid的行间距
- DELPHI控件的顯示問題!!!
- [求修改输入法的代码]谢谢,很简单,高分,急~!
- tcp连接不太稳定的问题。
- 如何实现在ComboBox的下拉列表状态下,在相关item位置右击鼠标,弹出菜单,以便选择菜单功能对相关item进行操作?
- DBEdit的奇怪问题
- 使用fastreport如何使打印机打印完成后不要向上翻滚纸张而马上停下????
- 急!急!急!在delphi中如何判断另外一个应用程序是否运行?
- 大家讨论:3层结构的技术更适合在广域网还是局域网?
- 各位兄弟,你们有EdLib这个For Delphi6的控件在那里下载
- 请帮我看一下,这段代码什么意思?
- 请问Activform在IE中为何不显示内容?Ie的安全性调为最低都不行!!!
select top n * from 表名 where 条件值 ;其中n为一整数
select first 10* from wt_xxxx
就是取前面的10条记录
不知道ORACLE怎么样
MSSQL Server是在SELECT语句中加入“first”关键字;
DB2是在SELECT语句中加入“TOP”关键字;
Oracle忘记了,是有一关键字可处理的。
怎么写了,举个简单的例子行吗,我在PB下用SELECT TOP 50调试不过
,a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0) zjf,nvl(b.ljdf,0) ljdf,nvl(b.ljjzjf,0) ljjzjf from
(select dhhm,sum(xfjf) zxfjf,sum(zwjf) zzwjf,sum(jljf) zjlj from yx_jfdab group by dhhm)A,
(select dhhm,ydxfjf,ydzwjf,ydjljf,ljdf,ljjzjf from yx_jfdjljb)B,
(select dhhm,khmc,blzd8 from jfxt.yx_yhdab)C
where a.dhhm=b.dhhm(+) and a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0)>500
and a.dhhm=c.dhhm and rownum<50 order by zjf desc;可以吗?
为什么在sqlplus和PB中都调试不过啊select top 50 rownum * from 表名
也不行
要取排在前50名到底该怎么写啊,帮帮我吧
where qty>=20
order by qty asc
( 由高到低排序查询) where rownum<=50
如:
select * from
(
select rownum ,a.dhhm dhhm,c.khmc khmc,a.zxfjf-nvl(b.ydxfjf,0) dqxfjf,a.zzwjf-nvl(b.ydzwjf,0) dqzwjf,a.zjljf-nvl(b.ydjljf,0) dqjljf
,a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0) zjf,nvl(b.ljdf,0) ljdf,nvl(b.ljjzjf,0) ljjzjf from
(select dhhm,sum(xfjf) zxfjf,sum(zwjf) zzwjf,sum(jljf) zjlj from yx_jfdab group by dhhm)A,
(select dhhm,ydxfjf,ydzwjf,ydjljf,ljdf,ljjzjf from yx_jfdjljb)B,
(select dhhm,khmc,blzd8 from jfxt.yx_yhdab)C
where a.dhhm=b.dhhm(+) and a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0)>500
and a.dhhm=c.dhhm order by zjf desc )where rownum<=50
( 由高到低排序查询) where rownum<=50这样做是正确的!
,a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0) zjf,nvl(b.ljdf,0) ljdf,nvl(b.ljjzjf,0) ljjzjf from
(select dhhm,sum(xfjf) zxfjf,sum(zwjf) zzwjf,sum(jljf) zjlj,rownum rown from yx_jfdab group by dhhm)A,
(select dhhm,ydxfjf,ydzwjf,ydjljf,ljdf,ljjzjf from yx_jfdjljb)B,
(select dhhm,khmc,blzd8 from jfxt.yx_yhdab)C
where a.dhhm=b.dhhm(+) and a.zxfjf-nvl(b.ydxfjf,0)+a.zzwjf-nvl(b.ydzwjf,0)+a.zjljf-nvl(b.ydjljf,0)+nvl(b.ljjzjf,0)>500
and a.dhhm=c.dhhm order by zjf desc) a where a.rown <50
from (select * from table1 a where a.num > 500 order by a.num) tt
where rownum < 50
你要把你的查询结果排序后包起来重新查询一次
我用的是ORACLE数据库
select * from 视图 where rownum<=50
我用过,可以的(oracle)