Question

oracle sql语句 group by 不能用的问题

select t2.area as we,t2.clientyear,t2.clientmonth,t2.goodsno,t2.goodsname,t2.targetamount,sd.quantityfrom drp_salesorder_detail sd left join(select ds.ordersno,extract(year from ds.orderdate) clientyear,extract(month from ds.orderdate) clientmonth,ds.purchasesno,t1.goodsno,t1.area,t1.targetamount,t1.goodsnamefrom DRP_SALESORDER ds left join(select dc.area,dcs.clientyear,dcs.clientmonth,dcs.goodsno,dcs.clientno,dcs.targetamount,dcs.goodsnamefrom drp_client dc left join drp_client_salestarget dcson dcs.clientno = dc.clientno) t1 on ds.purchasesno = t1.clientno) t2 on sd.goodsno = t2.goodsno

这个是可以运行的,如图

但是我想把 sd.quantity求和 就不行了..

语句如下 只修改了 sum(sd.quantity) 和后面的group by语句
select t2.area as we,t2.clientyear,t2.clientmonth,t2.goodsno,t2.goodsname,t2.targetamount,sum(sd.quantity)from drp_salesorder_detail sd left join(select ds.ordersno,extract(year from ds.orderdate) clientyear,extract(month from ds.orderdate) clientmonth,ds.purchasesno,t1.goodsno,t1.area,t1.targetamount,t1.goodsnamefrom DRP_SALESORDER ds left join(select dc.area,dcs.clientyear,dcs.clientmonth,dcs.goodsno,dcs.clientno,dcs.targetamount,dcs.goodsnamefrom drp_client dc left join drp_client_salestarget dcson dcs.clientno = dc.clientno) t1 on ds.purchasesno = t1.clientno) t2 on sd.goodsno = t2.goodsnogroup by we

group by 后面跟什么都不可以!!求高手

Answer 1

select t2.area as we,t2.clientyear,t2.clientmonth,t2.goodsno,t2.goodsname,t2.targetamount,sum(sd.quantity)
from drp_salesorder_detail sd left join
(select ds.ordersno,extract(year from ds.orderdate) clientyear,extract(month from ds.orderdate) clientmonth,ds.purchasesno,t1.goodsno,t1.area,t1.targetamount,t1.goodsname
from DRP_SALESORDER ds left join
(select dc.area,dcs.clientyear,dcs.clientmonth,dcs.goodsno,dcs.clientno,dcs.targetamount,dcs.goodsname
from drp_client dc left join drp_client_salestarget dcs
on dcs.clientno = dc.clientno) t1
on ds.purchasesno = t1.clientno) t2
on sd.goodsno = t2.goodsno
group by t2.area,t2.clientyear,t2.clientmonth,t2.goodsno,t2.goodsname,t2.targetamount

这样就可以了

追问

高手啊...这是为什么呢... 其实我更想利用一个分组 是不是不可能的..

追答

除了进行分组的字段以外的字段必须做聚合函数

追问

实在太感谢了...不过最后还有一个问题.有没有不改变值得聚合函数呢.. 这样就可以磕磕绊绊的实现了..