select a.clientno clientno,
a.jname dianname,
a.dianno dianno,
sum(nvl(c.numbers,0)) zdnum,
SUM(ROUND(NVL(C.NUMBERS * d.PRICE,0),2)) zdmayang,
( SELECT SUM(NVL(G_OUTINFO.NUMBERS,0))
FROM G_OUTINFO,Y_ZDML
WHERE G_OUTINFO.ZDCODE=Y_ZDML.ZDMLNO AND
G_OUTINFO.BUYUNIT=A.CLIENTNO AND
Y_ZDML.PICINO=:as_picino AND
Y_ZDML.MLXX LIKE :as_mlxx) OUTNUM,
( SELECT SUM(ROUND(NVL(G_OUTINFO.NUMBERS * G_BOOKINFO.PRICE,0),2))
FROM G_OUTINFO,G_BOOKINFO,Y_ZDML
WHERE G_OUTINFO.ZDCODE=Y_ZDML.ZDMLNO AND
G_BOOKINFO.BOOKID=G_OUTINFO.BOOKID AND G_OUTINFO.BUYUNIT=A.CLIENTNO AND
Y_ZDML.PICINO=:as_picino AND Y_ZDML.MLXX LIKE :as_mlxx) OUTMAYANG
from g_cinfo a,y_zd c,y_zdml d
where c.buyunit=a.clientno and
d.zdmlno=c.zdmlno and
d.picino=:as_picino and
d.mlxx like :as_mlxx and
a.clientno like :as_buyunit
group by a.clientno,
a.jname,
a.dianno
order by a.dianno
a.jname dianname,
a.dianno dianno,
sum(nvl(c.numbers,0)) zdnum,
SUM(ROUND(NVL(C.NUMBERS * d.PRICE,0),2)) zdmayang,
( SELECT SUM(NVL(G_OUTINFO.NUMBERS,0))
FROM G_OUTINFO,Y_ZDML
WHERE G_OUTINFO.ZDCODE=Y_ZDML.ZDMLNO AND
G_OUTINFO.BUYUNIT=A.CLIENTNO AND
Y_ZDML.PICINO=:as_picino AND
Y_ZDML.MLXX LIKE :as_mlxx) OUTNUM,
( SELECT SUM(ROUND(NVL(G_OUTINFO.NUMBERS * G_BOOKINFO.PRICE,0),2))
FROM G_OUTINFO,G_BOOKINFO,Y_ZDML
WHERE G_OUTINFO.ZDCODE=Y_ZDML.ZDMLNO AND
G_BOOKINFO.BOOKID=G_OUTINFO.BOOKID AND G_OUTINFO.BUYUNIT=A.CLIENTNO AND
Y_ZDML.PICINO=:as_picino AND Y_ZDML.MLXX LIKE :as_mlxx) OUTMAYANG
from g_cinfo a,y_zd c,y_zdml d
where c.buyunit=a.clientno and
d.zdmlno=c.zdmlno and
d.picino=:as_picino and
d.mlxx like :as_mlxx and
a.clientno like :as_buyunit
group by a.clientno,
a.jname,
a.dianno
order by a.dianno
解决方案 »
- 数据库 查询语句
- 请高手说说dedicated server和MTS server各自的特点和区别
- ORACLE DATABASE LINK 问题
- 查询语句写不出来了,达人们帮帮忙,出出招!
- 谁能帮我把一SQL的trigger转换成ORACLE的,小弟在此先谢谢了!!!!!!!!!
- ORA-03001: 未执行的特性 帮忙看看,搞了半天没搞明白.
- 一个for update的问题
- 怎样用asp实现clob字段的增加和读取?
- 诡异的 Oracle 远程连接,同一样的连接代码,Form程序通过,web程序报错
- 求助一下错误原因,oracle输入代码后就出现如下错误
- 怎么设置blob字段的存储的大小???????????????
- 我要作一个类似google的检索工具就像csdn的检索一样,能进行全文检索、中文分词和镜像检索,请高手指点!
a.jname dianname,
a.dianno dianno,
中有的不能满条件
( SELECT SUM(NVL(G_OUTINFO.NUMBERS,0))
FROM G_OUTINFO,Y_ZDML
WHERE G_OUTINFO.ZDCODE=Y_ZDML.ZDMLNO AND
G_OUTINFO.BUYUNIT=A.CLIENTNO AND
Y_ZDML.PICINO=:as_picino AND
Y_ZDML.MLXX LIKE :as_mlxx) OUTNUM
这部分相当于一个表达式,与普通字段一样,它不是分组表达式。虽然在里边有SUM。OUTMAYANG也一样。所以系统会报告非分组表达式。
group by
a.clientno clientno,
a.jname dianname,
a.dianno dianno,
table2.xxx, table3.xxx
FROM
(
SELECT xxx,yyy, SUM(NVL(zzz)) scql
FROM 表名 a
WHERE 条件
GROUP BY xxx,yyy
) table1,
(
SELECT xxx,yyy, SUM(NVL(zzzz)) scql
FROM 表名 a
WHERE 条件
GROUP BY xxx,yyy
) table2,
……………………WHERE
(table1.xxx=table2.xxx) and ……………………
a.jname dianname,
a.dianno dianno,
sum(nvl(c.numbers,0)) zdnum,
SUM(ROUND(NVL(C.NUMBERS * d.PRICE,0),2)) zdmayang,
( SELECT SUM(NVL(G_OUTINFO.NUMBERS,0))
FROM G_OUTINFO,Y_ZDML
WHERE G_OUTINFO.ZDCODE=Y_ZDML.ZDMLNO AND
G_OUTINFO.BUYUNIT=A.CLIENTNO AND
Y_ZDML.PICINO=:as_picino AND
Y_ZDML.MLXX LIKE :as_mlxx) OUTNUM,
( SELECT SUM(ROUND(NVL(G_OUTINFO.NUMBERS * G_BOOKINFO.PRICE,0),2))
FROM G_OUTINFO,G_BOOKINFO,Y_ZDML
WHERE G_OUTINFO.ZDCODE=Y_ZDML.ZDMLNO AND
G_BOOKINFO.BOOKID=G_OUTINFO.BOOKID AND G_OUTINFO.BUYUNIT=A.CLIENTNO AND
Y_ZDML.PICINO=:as_picino AND Y_ZDML.MLXX LIKE :as_mlxx) OUTMAYANG
from g_cinfo a,y_zd c,y_zdml d
where c.buyunit=a.clientno and
d.zdmlno=c.zdmlno and
d.picino=:as_picino and
d.mlxx like :as_mlxx and
a.clientno like :as_buyunit
group by clientno,
jname,
dianno
order by dianno