oracel的distinct,求解释

时间:2022-03-13 16:29:04

      今天把MySQL的数据库换成oracle的,在做的过程中碰到一个查询语句的问题很奇怪

在MySQL里面有条语句是这么写的能查到结果

select DISTINCT STU_INFO.*
from STU_INFO , TB_STU_LANG_MAPPING language   
where  student_no = language.language_stu_no    
and (language.parameter_id = 'C9101'  or  language.parameter_id = 'C9102'     )   
and (stu_status_id = 'B503' ) order by enrolDate desc ;

然后放到oracle下,就报如下错误

oracel的distinct,求解释

我的stu_info表中有个字段类型是blob,网上说blob类型的字段在oracle中是不能使用distinct关键字的,可是我用

select DISTINCT STU_INFO.* from STU_INFO;

单独查询stu_info表的话并没有给我报上面那个错误,后来多次测试发现只有该表与其他表有连接就会报这个错,单表查询的话就完全没问题,求解释。。。我用distinct只是想把stu_info中的字段全部显示出来,而language表中的字段不显示

后来换了一种方法实现该操作

select *
from STU_INFO
WHERE stu_status_id = 'B503'
  AND student_no IN
    ( SELECT language_stu_no
      FROM TB_STU_LANG_MAPPING
      WHERE parameter_id = 'C9101'
        OR parameter_id = 'C9102'
        and language_stu_no IN
        (
          SELECT  agreement
          from tb_stu_tech_mapping
          where parameter_id = 'B301'
                or parameter_id = 'B302'
        )
      )
ORDER BY enrolDate desc ;