select rownum as rn, m.\"Name\" as good, count(1) as tcount,sum(wv.\"Net\") as tnet, e.\"Name\" as goodfrom,e2.\"Name\" as goodto from \"PIKA\".\"WeightVoucher\" wv, \"PIKA\".\"Enterprise\" e, \"PIKA\".\"Enterprise\" e2, \"PIKA\".\"LogisticsRoute\" l, \"PIKA\".\"Material\" m where wv.\"Logistics\"=l.\"Identity\" and l.\"BusinessProvider\"=e.\"Identity\" and l.\"BusinessConsumer\"=e2.\"Identity\" and wv.\"MaterialGroup\"=m.\"Identity\" and wv.\"ConveyanceGroup\"='Railway' and wv.\"CreateDate\">=to_date('2011-8-9 0:00:00','yyyy-mm-dd hh24:mi:ss') and wv.\"CreateDate\"<to_date('2011-8-9 23:59:00','yyyy-mm-dd hh24:mi:ss') group by good, goodfrom, goodto最后提示分组错误,我的想法就是要在做四个表的跨表查询,但是提示分组错误,现在现场,急
这什么写法?字段名这么奇怪吗?
这个是在c#里面写的,我可能没有说明白,实际上字段名是m."Name" as good,
group by m.Name, e.Name, e2.Name试试!
select a.rn,a.good,count(1) as tcount,sum(a.tnet) as tnet,a.goodfrom,a.goodto from (
select rownum as rn, m.\"Name\" as good, wv.\"Net\" as tnet, e.\"Name\" as goodfrom,e2.\"Name\" as goodto from \"PIKA\".\"WeightVoucher\" wv, \"PIKA\".\"Enterprise\" e, \"PIKA\".\"Enterprise\" e2, \"PIKA\".\"LogisticsRoute\" l, \"PIKA\".\"Material\" m where wv.\"Logistics\"=l.\"Identity\" and l.\"BusinessProvider\"=e.\"Identity\" and l.\"BusinessConsumer\"=e2.\"Identity\" and wv.\"MaterialGroup\"=m.\"Identity\" and wv.\"ConveyanceGroup\"='Railway' and wv.\"CreateDate\">=to_date('2011-8-9 0:00:00','yyyy-mm-dd hh24:mi:ss') and wv.\"CreateDate\"<to_date('2011-8-9 23:59:00','yyyy-mm-dd hh24:mi:ss')
) as a
group by rn,good, goodfrom, goodto
语句提示SQL语句未正确结束,急啊
我现在就是把语句输出以后放到oracle数据库里面的
rownum as rn,
sum(ttnet) as tnet,
good,
goodfrom,
goodto
count(1) as tcount
from
(select m."Name" as good,
e."Name" as goodfrom,e2."Name" as goodto ,
wv."PonderationAmount" as ttnet from "PIKA"."WeightVoucher" wv, "PIKA"."Enterprise" e, "PIKA"."Enterprise" e2, "PIKA"."LogisticsRoute" l, "PIKA"."Material" m where wv."Logistics"=l."Identity" and l."BusinessProvider"=e."Identity" and l."BusinessConsumer"=e2."Identity" and wv."MaterialGroup"=m."Identity" and wv."ConveyanceGroup"='Railway' and wv."CreateDate">=to_date('2011-8-9 0:00:00','yyyy-mm-dd hh24:mi:ss') and wv."CreateDate"<to_date('2011-8-9 23:59:00','yyyy-mm-dd hh24:mi:ss')
) ttgroup by good,goodfrom,goodto
现在提示Group by 错误
rownum as rn,tnet,
good,
goodfrom,
goodto ,
tcount from (
select
sum(ttnet) as tnet,
good,
goodfrom,
goodto ,
count(1) as tcount
from
(select m."Name" as good,
e."Name" as goodfrom,e2."Name" as goodto ,
wv."PonderationAmount" as ttnet from "PIKA"."WeightVoucher" wv, "PIKA"."Enterprise" e, "PIKA"."Enterprise" e2, "PIKA"."LogisticsRoute" l, "PIKA"."Material" m where wv."Logistics"=l."Identity" and l."BusinessProvider"=e."Identity" and l."BusinessConsumer"=e2."Identity" and wv."MaterialGroup"=m."Identity" and wv."ConveyanceGroup"='Railway' and wv."CreateDate">=to_date('2011-8-9 0:00:00','yyyy-mm-dd hh24:mi:ss') and wv."CreateDate"<to_date('2011-8-9 23:59:00','yyyy-mm-dd hh24:mi:ss')
) tt
group by good,goodfrom,goodto ) tt2
真是要命了!估计执行的效率就没法看了!
用户有一个表:WeightVoucher(A表),要从这个表里面计算净重的汇总,但是里面的一些字段是代码形式的要从几个从表里面取值:A表-》净重 物流编码 物料编码B表物料表-》 物料编码 取名称C表物流表-》通过物流编码取 发货单位代码 和 收货单位代码D表单位代码表-》通过单位代码取单位名称
最后的结果是序号 物料名称 净重合计 发货单位名称 收货单位名称
1 钢卷 18000.00 济南 北京就是这样一个类似的表,如果要是大家做会怎么做呢?
是oracle,不过就感觉这个人写的这个东西 是很不标准的!m.就是起的这个表的别名!