首页 新闻 搜索 专区 学院

SQL 逗号连接改为join内连接,基表不止一个的情况

0
[待解决问题]

下面逗号分隔的查询sql:

 

select *
  from taskdetail td, tasklist tl, f_account fa, tasksort ts
 where td.flowid = tl.taskprocid
   and td.taskid = tl.taskid
   and td.flowowner = fa.fa_account
   and fa.business_unit not in ('PA005')
   and tl.tasksortid = ts.tasksortid
   and tl.taskstatus = '0'
   and tl.instanceid is null
   and tl.taskdate > sysdate - 90    
   and td.stageid != '105'
   and td.stageid != '1110'
   and td.flowstatus = '0'
   and td.flowenddate is not null
   and td.noresponse != '1'

 

我想改为join查询,如果用td做基表,tl与ts怎么关联上呢,不知道tl的条件应该怎么写?

我写出以下三条sql,那个是正确的,或者怎么改写呢:

select *
  from taskdetail td
  join tasklist tl on td.flowid = tl.taskprocid
                  and td.taskid = tl.taskid
                  and tl.taskstatus = '0'
                  and tl.instanceid is null
                  and tl.taskdate > sysdate - 90
  join f_account fa on td.flowowner = fa.fa_account
                   and fa.business_unit not in ('PA005')
  join tasksort ts on tl.tasksortid = ts.tasksortid
 where td.stageid != '105'
   and td.stageid != '1110'
   and td.flowstatus = '0'
   and td.flowenddate is not null
   and td.noresponse != '1'
select *
  from taskdetail td
  join tasklist tl on td.flowid = tl.taskprocid
                  and td.taskid = tl.taskid
                  and tl.taskstatus = '0'
                  and tl.instanceid is null
                  and tl.taskdate > sysdate - 90
  join f_account fa on td.flowowner = fa.fa_account
                   and fa.business_unit not in ('PA005')
  join tasksort ts on tl.tasksortid = ts.tasksortid
                  and tl.taskstatus = '0'
                  and tl.instanceid is null
                  and tl.taskdate > sysdate - 90
 where td.stageid != '105'
   and td.stageid != '1110'
   and td.flowstatus = '0'
   and td.flowenddate is not null
   and td.noresponse != '1'
   
select *
  from taskdetail td
  join tasklist tl on td.flowid = tl.taskprocid
                  and td.taskid = tl.taskid
  join f_account fa on td.flowowner = fa.fa_account
                   and fa.business_unit not in ('PA005')
  join tasksort ts on tl.tasksortid = ts.tasksortid
 where td.stageid != '105'
   and td.stageid != '1110'
   and td.flowstatus = '0'
   and td.flowenddate is not null
   and td.noresponse != '1'
   and tl.taskstatus = '0'
   and tl.instanceid is null
   and tl.taskdate > sysdate - 90
寂寥的出生在1874的主页 寂寥的出生在1874 | 初学一级 | 园豆:7
提问于:2016-08-18 10:38
< >
分享
所有回答(2)
0

right join tasklist ...

right join f_account..

where 

Eric.luo | 园豆:853 (小虾三级) | 2016-08-18 17:51
0

用内连接试试select * from A inner join B on A.id=B.id

苦逼运维 | 园豆:140 (初学一级) | 2016-08-19 14:48
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册