oracle 连接

//数据
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

 

 

 

- - - - - - -   -         - -      -                 - --- - - - -- - - - - - - -

Oracle的left join中on和where的区别

分类： oracle数据库 2011-05-11 11:38 14人阅读 评论(0) 收藏 举报

转贴这篇是因为今天我用到了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中，返回的结果集是相同的。

 

-- - - - - - - - - - - - - --

 

 

oracle left join case when then

2012-04-26 22:49 8人阅读 评论(0) 收藏 举报

项目中遇到这样的情况，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