A表dwbn dwmc
433101 红花433102 白花433103 绿花433104 蓝花
B表zd zb
10000 433101 01000 433102
统计A,B表满足条件为dwbn等于zb的总个数,不满足条件的默认为0结果为:dwmc aa
红花 1白花 1绿花 0蓝花 0
433101 红花433102 白花433103 绿花433104 蓝花
B表zd zb
10000 433101 01000 433102
统计A,B表满足条件为dwbn等于zb的总个数,不满足条件的默认为0结果为:dwmc aa
红花 1白花 1绿花 0蓝花 0
group by dwmc
with A as(
select '433101' dwbn,'红花'dwmc from dual
union all
select '433102', '白花' from dual
union all
select '433103', '绿花' from dual
union all
select '433104', '蓝花' from dual
),
B as(
select '10000' zd, '433101' zb from dual
union all
select '01000', '433102' from dual
)
select dwmc,Count(zd) from a left join b on a.dwbn=b.zb
group by a.dwmcDWMC COUNT(ZD)
-------------------
白花 1
红花 1
绿花 0
蓝花 0
create table dw(
dwbn number,
dwmc varchar2(20)
);
insert into dw(dwbn,dwmc) values(433101,'红花');insert into dw(dwbn,dwmc) values(433102,'白花');
insert into dw(dwbn,dwmc) values(433103,'绿花');
insert into dw(dwbn,dwmc) values(433104,'蓝花');
create table c(
zd number,
zb number
);
insert into c(zd,zb) values (10000,'433101');
insert into c(zd,zb) values (01000,'433102');
select nvl(count(zd) as aa, dwmc from dw left join c on dw.dwbn = c.zb
group by dwmc;