L
lf43
Unregistered / Unconfirmed
GUEST, unregistred user!
我现在有三个表,一个是客户积分档案表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;