需求:
有两个表tbOrder(A)和tbPay(B)分别是订单表和支付表
A表主要字段:recordID(记录ID),orderMoney(订单金额),orderDate(订单时间)
B表主要字段:recordID(记录ID),orderID(A表中的记录ID),payMoney(支付金额) ,payDate(支付时间)现在要按天出一个汇总
订单人次,订单总金额,支付人次,支付总金额
有两个表tbOrder(A)和tbPay(B)分别是订单表和支付表
A表主要字段:recordID(记录ID),orderMoney(订单金额),orderDate(订单时间)
B表主要字段:recordID(记录ID),orderID(A表中的记录ID),payMoney(支付金额) ,payDate(支付时间)现在要按天出一个汇总
订单人次,订单总金额,支付人次,支付总金额
from a,b where a.recordid=b.orderid and to_char(a.orderdate,'yyyy-mm-dd')='2008-12-12' and to_char(b.paydate,'yyyy-mm-dd')='2008-12-12'
你的按天,这个天日期是 orderDate(订单时间) 呢 还是payDate(支付时间) 呢?
from tborder a,tbpay b
where a.recordid=b.orderid(+)
group by trunc(orderdate)按订单日期统计
from(
select trunc(orderdate) 日期,count(a.recordid) 订单人次,sum(ordermoney) 订单总金额,0 支付人次,0 支付总金额
from tborder a
group by trunc(orderdate)
union all
select trunc(paydate) 日期,0 订单人次,0 订单总金额,count(b.recordid) 支付人次,sum(paymoney) 支付总金额
from tbpay b
group by trunc(paydate))
group by 日期
select count(c.ddrc), sum(c.orderMoney), count(c.zfrc), sum(c.payMoney)
from (select a.orderMoney, --订单金额
trunc(a.orderDate) orderDate, --订单时间
b.payMoney, --支付金额
trunc(b.payDate) payDate, --支付时间
1 ddrc, --订单人次
1 zfrc --支付人次
from tbOrder a, tbPay b
where a.recordID = b.orderid) c
group by orderDate --订单时间