小弟现在在项目中遇到一个问题,描述如下:
一张表,表orderstat结构是:
statdate ordersort ordersource dealperson orderamount orderaccount
日期 订单类别 订单来源 订单处理人 订单金额 订单数量表没有主键,statdate,ordersort,ordersource,dealperson做了唯一约束现在要获得的是:各个处理人所处理的各来源的每类订单当日的订单金额,订单数量,以及前一日的订单金额,订单数量。我写的sql如下:
select a.stat_date,
b.stat_date previousdate,
a.ordersort,
a.ordersource,
a.dealperson,
a.orderamount,
b.orderamount previousamount,
a.orderaccount,
b.orderaccount
from orderstat a, orderstat b
where a. ordersort = b. ordersort(+)
and a. ordersource = b. ordersource(+)
and a. dealperson = b. dealperson(+)
and to_date(a.stat_date,'yyyy-mm-dd')-1 = to_date(b.stat_date,'yyyy-mm-dd')(+)
order by stat_date;查询的时候报错,错在 to_date(a.stat_date,'yyyy-mm-dd')-1 = to_date(b.stat_date,'yyyy-mm-dd')(+)中使用了(+).
请问各位要如何才能查出所有符合的记录。
一张表,表orderstat结构是:
statdate ordersort ordersource dealperson orderamount orderaccount
日期 订单类别 订单来源 订单处理人 订单金额 订单数量表没有主键,statdate,ordersort,ordersource,dealperson做了唯一约束现在要获得的是:各个处理人所处理的各来源的每类订单当日的订单金额,订单数量,以及前一日的订单金额,订单数量。我写的sql如下:
select a.stat_date,
b.stat_date previousdate,
a.ordersort,
a.ordersource,
a.dealperson,
a.orderamount,
b.orderamount previousamount,
a.orderaccount,
b.orderaccount
from orderstat a, orderstat b
where a. ordersort = b. ordersort(+)
and a. ordersource = b. ordersource(+)
and a. dealperson = b. dealperson(+)
and to_date(a.stat_date,'yyyy-mm-dd')-1 = to_date(b.stat_date,'yyyy-mm-dd')(+)
order by stat_date;查询的时候报错,错在 to_date(a.stat_date,'yyyy-mm-dd')-1 = to_date(b.stat_date,'yyyy-mm-dd')(+)中使用了(+).
请问各位要如何才能查出所有符合的记录。
from orderstat
where statdate >= (sysdate-1) and statdate <= sysdate
group by(orderperson,ordersort,statdate);
from orderstat a,
(
select b1.*,to_date(b1.stat_date,'yyyy-mm-dd') stat_date1 from orderstat b1
) bwhere ...
and to_date(a.stat_date,'yyyy-mm-dd')-1 =b.stat_date1(+)
----------------------------------------------------------------
或者 from orderstat
left join orderstat b
on( ... and to_date(a.stat_date,'yyyy-mm-dd')-1 = to_date(b.stat_date,'yyyy-mm-dd'))
from orderstat
where trunc(statdate) = :dt or trunc(statdate) = :dt - 1
group by trunc(statdate) ,dealperson,ordersort
order by 1,2
SELECT stat_date, stat_date - 1 pre_stat_date, ordersort, ordersource,
dealperson, amt,
LAG (amt, 1, 0) OVER (PARTITION BY ordersort, ordersource, dealperson ORDER BY stat_date)
pre_amt,
qty,
LAG (qty, 1, 0) OVER (PARTITION BY ordersort, ordersource, dealperson ORDER BY stat_date)
pre_amt
FROM (SELECT TRUNC (a.stat_date) stat_date, a.ordersort, a.ordersource,
a.dealperson, SUM (a.orderamount) amt,
SUM (a.orderaccount) qty
FROM orderstat a
GROUP BY TRUNC (a.stat_date), a.ordersort, a.ordersource,
a.dealperson)
SELECT stat_date, stat_date - 1 pre_stat_date, ordersort, ordersource,
dealperson, amt,
LAG (amt, 1, 0) OVER (PARTITION BY ordersort, ordersource, dealperson ORDER BY stat_date)
pre_amt,
qty,
LAG (qty, 1, 0) OVER (PARTITION BY ordersort, ordersource, dealperson ORDER BY stat_date)
pre_qty
FROM (SELECT TRUNC (a.stat_date) stat_date, a.ordersort, a.ordersource,
a.dealperson, SUM (a.orderamount) amt,
SUM (a.orderaccount) qty
FROM orderstat a
GROUP BY TRUNC (a.stat_date), a.ordersort, a.ordersource,
a.dealperson)
select a.stat_date,
b.stat_date previousdate,
a.ordersort,
a.ordersource,
a.dealperson,
a.orderamount,
b.orderamount previousamount,
a.orderaccount,
b.orderaccount
from orderstat a, orderstat b
where a. ordersort = b. ordersort(+)
and a. ordersource = b. ordersource(+)
and a. dealperson = b. dealperson(+)
and to_date(a.stat_date,'yyyy-mm-dd')-1 = to_date(b.stat_date(+),'yyyy-mm-dd')
order by stat_date;
原来的语句在日期条件上报错:missing expression。为何要把外链接运算符写在字段后面,而不是to_date函数后