主表 A(主键id,日期date),子表B(主键id,外键a_id,数量count),一对多,
比如主表有30条数据,本月的1号-到30号,其中1号到22号在表b有若干关联数据,23号后面是没有关联数据的,
列出1-30号的数据,返回如下视图日期,sum(count)
23号以后的sum(count)显示为0
比如主表有30条数据,本月的1号-到30号,其中1号到22号在表b有若干关联数据,23号后面是没有关联数据的,
列出1-30号的数据,返回如下视图日期,sum(count)
23号以后的sum(count)显示为0
FROM A,
(SELECT A.日期DATE AS 日期DATE, SUM(B.COUNT) AS COUNT
FROM A, B
WHERE A.主键ID=B.外键A_ID) B
WHERE A.日期DATE = B.日期DATE(+)
select A.date,count(B.id)
from A,B
where A.id = B.a_id
group by A.date
select 1 as id, 1 as dates from dual union all
select 2 as id, 15 as dates from dual union all
select 3 as id, 21 as dates from dual union all
select 4 as id, 25 as dates from dual union all
select 5 as id, 26 as dates from dual
)
, B as(
select 1 as id, 1 as a_id, 10 as counts from dual union all
select 2 as id, 2 as a_id, 20 as counts from dual union all
select 3 as id, 3 as a_id, 30 as counts from dual union all
select 4 as id, 4 as a_id, 40 as counts from dual union all
select 5 as id, 5 as a_id, 50 as counts from dual
)select a.dates,case when dates<23 then sum(b.counts) else 0 end as counts
from A a,B b where a.id=b.a_id group by a.dates
结果:
dates counts
25 0
1 10
26 0
15 20