//数据
A
id name
1 test1
2 test2
3 test3
B
id name code
11 test1 '001'
12 test1 '002'
13 test2 '004'
//结果
a.id b.code
1 '001'
2 '004'
3 null
//方法一:先左连接,在去掉重复的数据
with ta as(
select 1 id,'test1' name from dual union all
select 2,'test2' from dual union all
select 3,'test3' from dual)
,tb as(
select 11 id,'test1' name,'001' code from dual union all
select 12,'test1','002' from dual union all
select 13,'test2','004' from dual)
select tc.id,nvl(tc.code,'null') code
from (
select ta.id,ta.name,tb.code
from ta left join tb
on ta.name=tb.name) tc
where not exists(
select 1
from tb
where tb.name=tc.name
and tb.code < tc.code)
//方法二:先去掉重复的数据,在左连接
select ta.id,b.code
from ta left join
(select *
from tb b1
where not exists(
select *
from tb b2
where b1.name=b2.name
and b1.code > b2.code)) b
on ta.name=b.name
//方法三:左连接,按照name分组,为每一小组的一行返回一个行号,
//然后去行号为1的行
select id,code
from (
select row_number() over (partition by ta.id order by ta.id) rn
,ta.id,tb.code
from ta,tb
where ta.name=tb.name(+))
where rn=1
- - - - - - - - - - - - --- - - - -- - - - - - - -
转贴这篇是因为今天我用到了left join里面的on 和where,原来用的时候只知道用on,没带过where 。更不知道它们的区别。
今天遇到一个求某月所有天数的统计结果,如果某日的结果是0也需要显示出来,即:
日期 交易次数 交易金额
2009-4-01 1 10
2009-4-02 2 20
2009-4-03 0 0
2009-4-04 5 50
....
一开始我用的左连接,用on做为两表关联条件,用where作为过滤条件,但是发现0的数据根本不显示,后来把where关键字去掉,把过滤条件都放到on里,问题解决,网上一搜,找到了答案:
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用left jion时,on和where条件的区别如下:
1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
假设有两张表:
表1 tab1:
id size
1 10
2 20
3 30
表2 tab2:
size name
10 AAA
20 BBB
20 CCC
两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)
第一条SQL的过程:
1、中间表
on条件:
tab1.size = tab2.size
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)
2、再对中间表过滤
where 条件:
tab2.name=’AAA’
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
第二条SQL的过程:
1、中间表
on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)
其实以上结果的关键原因就是left join,right join,full join的特殊性,不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的特性的并集。 而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。
-- - - - - - - - - - - - - --
项目中遇到这样的情况,A表中有省市区 三个字段, 省中默认有数据, 如果市中有数据 则显示市中 数据, 省市区中都有数据,则显示区中数据,由于 这三个字段中存的是城市编码, 需要到另外一张表 B 做关联, 和 B 中的number 字段关联,表B 中有text 字段, 显示城市名称
sql:
select t....... 省略
from A t
left join B ta on
case
when t.区is not null then t.int_lan_code
when t.市 not null then t.int_city_code
when t.省 is not null then t.int_org_code
end = ta.text
where t.cust_agrmt_code is not null