oracle sql查询问题

Question

表结构如下，查询出来的数据有问题，烦请高人指导。num_category 表结构(numberseg是主键)
num_category_id ,numberseg
1016, 1356666101
1016, 1356667num_distinct表结构(numberseg是主键)
numberseg,distinct_id
1356666,　00290029
13566661,　00280028按最大匹配，1356666101号段归属于00280028地区，可是在用下面的sql查询时，却将1356666101归属于00290029了，用下面这条sql查询时有问题，
select distinct t1.num_category_id, t1.numberseg from
(
 select num_category_id, numberseg from  num_category where num_category_id=1016)t1, 
(select tnd.numberseg from num_distinct tnd.distinct_id = '00290029') t2
 where
t2.numberseg = substr(t1.numberseg,0,length(t2.numberseg))
查出的结果如下，本应该是查不出来的。num_category_id，numberseg
1016, 1356666101用的数据库是oracle，望达人指导。在此先谢！

Answer 1

tnd 怎么来的?(select tnd.numberseg from num_distinct tnd.distinct_id = '00290029') t2是不是from 后面少了个where ?

Answer 2

问题出现在 t2.numberseg = substr(t1.numberseg,0,length(t2.numberseg)) 因为你用函数substr时是以t2.numberseg的长度来取的,所以当改变distinct_id =条件时,1356666和13566661都取得出来,你最好改一下下面的where匹配条件吧..  
t2.numberseg = substr(t1.numberseg,0,length(t2.numberseg))
具体原因参考下面的SQL结果比较:
SQL> select distinct
  2         t1.num_category_id,
  3         t1.numberseg,
  4         substr(t1.numberseg, 0, length(t2.numberseg)),
  5         t2.numberseg
  6    from (select num_category_id, numberseg
  7            from (
  8                  select 1016 as num_category_id,1356666101 as numberseg from dual
  9                  union all
 10                  select 1016 as num_category_id,1356667 as numberseg from dual
 11                 )num_category
 12           where num_category_id = 1016
 13         ) t1,
 14         (select numberseg
 15            from (
 16                  select 1356666 as numberseg,'00290029' as distinct_id from dual
 17                  union all
 18                  select 13566661 as numberseg,'00280028' as distinct_id from dual
 19                 )num_distinct
 20           where distinct_id = '00280028'
 21         ) t2
 22   where t2.numberseg = substr(t1.numberseg, 0, length(t2.numberseg));NUM_CATEGORY_ID  NUMBERSEG SUBSTR(T1.NUMBERSEG,0,LENGTH(T                                                    NUMBERSEG
--------------- ---------- -------------------------------------------------------------------------------- ----------
           1016 1356666101 13566661                                                                           13566661SQL> 
SQL> select distinct
  2         t1.num_category_id,
  3         t1.numberseg,
  4         substr(t1.numberseg, 0, length(t2.numberseg)),
  5         t2.numberseg
  6    from (select num_category_id, numberseg
  7            from (
  8                  select 1016 as num_category_id,1356666101 as numberseg from dual
  9                  union all
 10                  select 1016 as num_category_id,1356667 as numberseg from dual
 11                 )num_category
 12           where num_category_id = 1016
 13         ) t1,
 14         (select numberseg
 15            from (
 16                  select 1356666 as numberseg,'00290029' as distinct_id from dual
 17                  union all
 18                  select 13566661 as numberseg,'00280028' as distinct_id from dual
 19                 )num_distinct
 20           where distinct_id = '00290029'
 21         ) t2
 22   where t2.numberseg = substr(t1.numberseg, 0, length(t2.numberseg));NUM_CATEGORY_ID  NUMBERSEG SUBSTR(T1.NUMBERSEG,0,LENGTH(T                                                    NUMBERSEG
--------------- ---------- -------------------------------------------------------------------------------- ----------
           1016 1356666101 1356666                                                                             1356666
SQL> 

Answer 3

谢谢mantisXF，
是from   后面少了个where。
应该是这个样子
select   distinct   t1.num_category_id,   t1.numberseg   from 
( 
  select   num_category_id,   numberseg   from     num_category   where   num_category_id=1016)t1,   
(select   tnd.numberseg   from   num_distinct tnd where  tnd.distinct_id   =   '00290029')   t2 
  where 
t2.numberseg   =   substr(t1.numberseg,0,length(t2.numberseg)) 不过问题还是没能解决，希望再指导下。谢谢

Answer 4

select   distinct   t1.num_category_id,   t1.numberseg   from 
( 
  select   num_category_id,   numberseg   from     num_category   where   num_category_id=1016)t1,   
(select   tnd.numberseg   from   num_distinct   tnd.distinct_id   =   '00290029')   t2 
  where 
t2.numberseg   =   substr(t1.numberseg,0,length(t2.numberseg)) 

t2.numberseg   =   substr(t1.numberseg,0,length(t2.numberseg)) 这个关联条件：substr(t1.numberseg,0,length(t2.numberseg))  取的是t1.numberseg这个字段的第0位到length(t2.numberseg)这一位的结果,length(t2.numberseg) = 7 ，所以就可以关联上啊。