UC知道oracle 的一道问题
来源：百度知道 编辑：UC知道 时间：2024/06/06 04:18:02
假设现在我有两个table： district 和 task
create table district (
districtType char(3) primary key check (districtType in ('Shanghi', 'Beijing', 'Hong Kong')
);
create table task (
task_id number(3) primary key,
districtType char(3) not null check(districtType in ('Shanghai', 'Beijing', 'HongKong')
);
我想得到上海，北京，香港各个地方的task总数，于是:
select d.districtType, count(t.tatk_id)
from district d
left outer join task t
group by d.districtType;
可是这样子只能出现table district里面出现过城市的task总数。如果现在district和task里面没有任何数据，我怎样才能让结果依然是：
Shanghai 0
Beijng 0
Hong Kong 0
求助大牛指点...回答的好有追分啊啊啊啊
create table district (
districtType char(3) primary key check (districtType in ('Shanghi', 'Beijing', 'Hong Kong')
);
create table task (
task_id number(3) primary key,
districtType char(3) not null check(districtType in ('Shanghai', 'Beijing', 'HongKong')
);
我想得到上海，北京，香港各个地方的task总数，于是:
select d.districtType, count(t.tatk_id)
from district d
left outer join task t
group by d.districtType;
可是这样子只能出现table district里面出现过城市的task总数。如果现在district和task里面没有任何数据，我怎样才能让结果依然是：
Shanghai 0
Beijng 0
Hong Kong 0
求助大牛指点...回答的好有追分啊啊啊啊
select
shanghai||' '||decode(districtT,'shanghai',count(t.tatk_id),0),
Beijng||' '||decode(districtT,'Beijng',count(t.tatk_id),0),
Hong||' '||decode(districtT,'Hong',count(t.tatk_id),0)
from district d
left outer join task t
group by d.districtType;
--
以上，希望对你有所帮助。
select d.districtType, count(*)
from district d
left outer join task t
group by d.districtType;
试试