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 求助大牛指点...
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 求助大牛指点...
from district d
left outer join task t
on d.districtType = t.districtType
group by d.districtType;
from
(
select t1.dis, task.task_id from
(select 'Shanghai' dis from dual
union all
select 'Beijing' dis from dual
union all
select 'Hongkong' dis from dual) t1
left outer join task t2 on t1.dis=t2.dis) t3
group by t3.dis
/
thx~
Create or replace get_task_Count(
districtType_ varchar2
)
return number
is
temp_ number;
begin
select count(*) into temp_
from task
where districtType=districtType_;
return temp_;
end get_task_Count;select districtType ,get_task_Count(districtType) from
( select 'Shanghai' districtType from dual
union all
select 'Beijing' districtType from dual
union all
select 'Hongkong' districtType from dual) t1