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
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