select m.spbs
-- ,sum(m.xssl)
,(select sum(xssl)
from jcdms_lsxsqdb
where spbs = m.spbs
and
trunc(xssj) <= to_date('2003-05-15','yyyy-mm-dd')
)
,max(sm)
from jcdms_lsxsqdb m,bm_jbxxk j
where trunc(xssj) >= to_date('2003-05-10','yyyy-mm-dd')
and trunc(xssj) <= to_date('2003-05-15','yyyy-mm-dd')
and m.spbs = j.spbs
and rownum <= 5
group by m.spbs
order by sum(m.xssl) desc
-- ,sum(m.xssl)
,(select sum(xssl)
from jcdms_lsxsqdb
where spbs = m.spbs
and
trunc(xssj) <= to_date('2003-05-15','yyyy-mm-dd')
)
,max(sm)
from jcdms_lsxsqdb m,bm_jbxxk j
where trunc(xssj) >= to_date('2003-05-10','yyyy-mm-dd')
and trunc(xssj) <= to_date('2003-05-15','yyyy-mm-dd')
and m.spbs = j.spbs
and rownum <= 5
group by m.spbs
order by sum(m.xssl) desc
改成如下语句就OK了:select * From (
select m.spbs,sum(m.xssl),sm from jcdms_lsxsqdb m,bm_jbxxk j where trunc(xssj)>=to_date('2003-05-10','yyyy-mm-dd') and trunc(xssj)<=to_date('2003-05-15','yyyy-mm-dd') and m.spbs=j.spbs order by sum(xssl) desc
) temp Where rownum<=5
trunc(xssj)>=to_date('2003-05-10','yyyy-mm-dd')看不懂,自相矛盾
from BM_JBXXK a,
(selec c.SPBS, sum(c.XSSL) XSL
from JCDMS_LSXSQDB c
where rownum <= 5
and trunc(xssj)>=to_date('2003-05-10','yyyy-mm-dd')
and trunc(xssj)<=to_date('2003-05-15','yyyy-mm-dd')
group by SPBS
order by sum(c.XSSL) desc
)b
where a.SPBS = b.SPBS
如果出现了调试不能通过的现象,那么是字符敲错了。我机器上没有Oracle。
你给我说,我可以帮你找出错误的地方。
,max(sm)
,(select sum(xssl)
from jcdms_lsxsqdb
where spbs = m.spbs
and
trunc(xssj) <= to_date('2003-05-15','yyyy-mm-dd')
)
from jcdms_lsxsqdb m,bm_jbxxk j
where m.spbs = j.spbs
and
m.spbs in (select SPBS
from (select SPBS
from JCDMS_LSXSQDB
where rownum <= 5
and trunc(xssj) >= to_date('2003-05-10','yyyy-mm-dd')
and trunc(xssj) <= to_date('2003-05-15','yyyy-mm-dd')
group by SPBS
order by sum(XSSL) desc
) T
)
group by m.spbs
hxfjb(New人) :select a.spbs
,max(a.SM)
,(select sum(xssl)
from jcdms_lsxsqdb
where spbs = a.spbs
and
trunc(xssj) <= to_date('2003-05-15','yyyy-mm-dd')
)
from BM_JBXXK a,
(select c.SPBS, sum(c.XSSL) XSL
from JCDMS_LSXSQDB c
where rownum <= 5
and trunc(xssj)>=to_date('2003-05-10','yyyy-mm-dd')
and trunc(xssj)<=to_date('2003-05-15','yyyy-mm-dd')
group by SPBS
order by sum(c.XSSL) desc
)b
where a.SPBS = b.SPBS
group by a.spbs
(select sm from bm_jbxxk where spbs=jcdms_lsxsqdb.spbs) as sm
from jcdms_lsxsqdb
where trunc(xssj)>=to_date('2003-05-10','yyyy-mm-dd') and trunc(xssj)<=to_date('2003-05-15','yyyy-mm-dd')
Group by spbs order by xssl desc)a where rownum<=5 order by xssl desc
你快测试!
你干嘛呢?
(SELECT J.SPBS, B.SM, J.XSSL*B.DJ AS XSJE
FROM JCDMS_LSXSQDB J LEFT BM_JBXXK B
ON J.SPBS=B.SPBS
WHERE XS_DATE>=DATEADD(DAY,-7,GETDATE())) MyView
GROUP BY SPBS, SM
ORDER BY SPBS, SM DESC其中XS_DATE为销售日期,你的表中没写。
不好意思,贴子上的问题已经解决,是坛上的一位高手,东西也已经送给他了。
以后还要请你注意,不要到处乱承诺,在一个地方许了,就应该在那个地方等待。
这里的高手不会在乎你的那点东东,但我看了不服气!