SELECT a.*,(SELECT SUM(B.amount)
............
SELECT a.*,(SELECT SUM(B.amount)
FROM (SELECT CONVERT(varchar(10), chargetime, 120) AS dt, SUM(amount)
AS amount
FROM cquancun
GROUP BY CONVERT(varchar(10), chargetime, 120)) B
WHERE datediff(dd, B.dt, A.rq) >= 0) AS total
FROM (SELECT CONVERT(varchar(10), chargetime, 120) AS rq, SUM(C.amount)
AS chargeamount
FROM cquancun C
where chargetime>TO_DATE('20040212','YYYYMMDDHH24MISS') and
chargetime<TO_DATE('20040812','YYYYMMDDHH24MISS')
GROUP BY CONVERT(varchar(10), chargetime, 120)) A
order by A.rq;
............
SELECT a.*,(SELECT SUM(B.amount)
FROM (SELECT CONVERT(varchar(10), chargetime, 120) AS dt, SUM(amount)
AS amount
FROM cquancun
GROUP BY CONVERT(varchar(10), chargetime, 120)) B
WHERE datediff(dd, B.dt, A.rq) >= 0) AS total
FROM (SELECT CONVERT(varchar(10), chargetime, 120) AS rq, SUM(C.amount)
AS chargeamount
FROM cquancun C
where chargetime>TO_DATE('20040212','YYYYMMDDHH24MISS') and
chargetime<TO_DATE('20040812','YYYYMMDDHH24MISS')
GROUP BY CONVERT(varchar(10), chargetime, 120)) A
order by A.rq;
其次,对于*,最好放在最后,不然是会出些莫名其妙的错误,或者你手动把字段都列出来
这样的做法容易出问题,最好是写成字段列表的形式,查询的速度也会很快。
FROM (SELECT to_char(chargetime,'yyyymmdd') AS dt, SUM(amount)
AS amount
FROM cquancun
GROUP BY to_char(chargetime,'yyyymmdd')) B
WHERE to_number(A.rq)-to_number(B.dt) >= 0) AS total
FROM (SELECT to_char(chargetime,'yyyymmdd') AS rq, SUM(C.amount)
AS chargeamount
FROM cquancun C
where chargetime>TO_DATE('20040212','YYYYMMDDHH24MISS') and chargetime<TO_DATE('20040812','YYYYMMDDHH24MISS')
GROUP BY to_char(chargetime,'yyyymmdd')) A
order by A.rq;