水果表 Fruit
id name
1 香蕉
2 葡萄
3 苹果存库表 Repertory
id Fruit_id cur_Num//当前数量
1 1 10
2 2 5我用下面语句查询,想得到各种水果的数量
select name,cur_num from fruit f, repertory r where f.id = r.fruit_id
结果只出现了2条记录,结果为:
name cur_num
香蕉 10
葡萄 5
还有苹果未查出来,因为这时库存中还没有苹果。我想要实现的结果集为:
name cur_num
香蕉 10
葡萄 5
苹果 0
id name
1 香蕉
2 葡萄
3 苹果存库表 Repertory
id Fruit_id cur_Num//当前数量
1 1 10
2 2 5我用下面语句查询,想得到各种水果的数量
select name,cur_num from fruit f, repertory r where f.id = r.fruit_id
结果只出现了2条记录,结果为:
name cur_num
香蕉 10
葡萄 5
还有苹果未查出来,因为这时库存中还没有苹果。我想要实现的结果集为:
name cur_num
香蕉 10
葡萄 5
苹果 0
select 1 id,'香蕉' name from dual
union all
select 2 id,'葡萄' name from dual
union all
select 3 id,'苹果' name from dual
),repertory as(
select 1 id,1 fruit_id,10 cur_num from dual
union all
select 2 id,2 fruit_id,5 cur_num from dual
)
select name,nvl(cur_num,0) from fruit f, repertory r where f.id = r.fruit_id(+)
就是左连接+若空置0
name cur_num
香蕉 10
葡萄 5
苹果
所以要用一个函数 nvl(cur_num,0)
select name,nvl(cur_num,0) from fruit f left join repertory r on f.id = r.fruit_id
比如 增加一个仓库号,查询在某个仓库中水果的数量:
存库表 Repertory
id Fruit_id cur_Num xxx //仓库号
1 1 10 1
2 2 5 1
3 3 5 1
4 1 10 2
5 2 5 2
得用
select name,nvl(cur_num,0) from fruit f left join repertory r on f.id = r.fruit_id where r.xxx(+)=2(+)不能少,少了就出不来。