Oracle多表查询

Question

oracle

Answer 1

select rlr3.barcode,
       b1.title,
       rlr3.barcode,
       rlr3.place_id,
       rlr3.book_circulate_type_id,
       rlr3.callno,
       rlr3.single_price,
       rlr3.lend_date,
       rlr3.return_date
  from (select rlr2.name,
               rlr2.barcode,
               c1.biblio_id,
               c1.barcode,
               c1.place_id,
               c1.book_circulate_type_id,
               c1.callno,
               c1.single_price,
               rlr2.lend_date,
               rlr2.return_date
          from (select r1.name,
                       r1.barcode,
                       'title',
                       rlr1.collection_barcode,
                       'place',
                       'type',
                       'callno',
                       'price',
                       rlr1.lend_date,
                       rlr1.return_date
                  from (select *
                          from reader_lend_record rlr
                         where school_id = 104
                           and create_date >=
                               to_date('2013-09-01', 'yyyy-mm-dd')
                           and create_date <=
                               to_date('2014-01-31', 'yyyy-mm-dd')) rlr1
                  left join (select * from reader r where r.school_id = 104) r1
                    on r1.id = rlr1.reader_id) rlr2
          left join (select * from collection c where c.school_id = 104) c1
            on rlr2.collection_barcode = c1.barcode) rlr3
  left join (select * from biblio b where school_id = 104) b1
    on b1.id = rlr3.biblio_id
 比如这个是我写的 功能是可以实现 但是好长阿

Answer 2

关联表更多了运行速度好慢要3秒多3万数据都不到T T
select rlr5.reader_name,
       rlr5.reader_barcode,
       rlr5.coll_name,
       rlr5.coll_barcode,
       rlr5.place_id,
       rlr5.book_circulate_type_id,
       rlr5.callno,
       rlr5.single_price,
       rlr5.lend_date,
       rlr5.return_date,
       rlr5.name readertype_name,
       sd1.name school_dept_name
  from (select rlr4.reader_name,
               rlr4.reader_barcode,
               rlr4.coll_name,
               rlr4.coll_barcode,
               rlr4.place_id,
               rlr4.book_circulate_type_id,
               rlr4.callno,
               rlr4.single_price,
               rlr4.lend_date,
               rlr4.return_date,
               rt1.name,
               rlr4.school_dept_id
          from (select rlr3.name                   reader_name,
                       rlr3.reader_barcode,
                       b1.title                    coll_name,
                       rlr3.coll_barcode,
                       rlr3.place_id,
                       rlr3.book_circulate_type_id,
                       rlr3.callno,
                       rlr3.single_price,
                       rlr3.lend_date,
                       rlr3.return_date,
                       rlr3.reader_type_code,
                       rlr3.school_dept_id
                  from (select rlr2.name,
                               rlr2.barcode              reader_barcode,
                               c1.biblio_id,
                               c1.barcode                coll_barcode,
                               c1.place_id,
                               c1.book_circulate_type_id,
                               c1.callno,
                               c1.single_price,
                               rlr2.lend_date,
                               rlr2.return_date,
                               rlr2.reader_type_code,
                               rlr2.school_dept_id
                          from (select r1.name,
                                       r1.barcode,
                                       'title',
                                       rlr1.collection_barcode,
                                       'place',
                                       'type',
                                       'callno',
                                       'price',
                                       rlr1.lend_date,
                                       rlr1.return_date,
                                       r1.reader_type_code,
                                       r1.school_dept_id
                                  from (select *
                                          from reader_lend_record rlr
                                         where school_id = 104
                                           and create_date >=
                                               to_date('2013-09-01',
                                                       'yyyy-mm-dd')
                                           and create_date <=
                                               to_date('2014-01-31',
                                                       'yyyy-mm-dd')) rlr1
                                  left join (select *
                                              from reader r
                                             where r.school_id = 104) r1
                                    on r1.id = rlr1.reader_id) rlr2
                          left join (select *
                                      from collection c
                                     where c.school_id = 104
                                       and c.state = 1) c1
                            on rlr2.collection_barcode = c1.barcode) rlr3
                  left join (select * from biblio b where school_id = 104) b1
                    on b1.id = rlr3.biblio_id) rlr4
          left join (select *
                      from reader_type rt
                     where school_id = 104
                       and state = 1) rt1
            on rt1.code = rlr4.reader_type_code) rlr5
  left join (select *
               from school_dept sd
              where sd.school_id = 104
                and state = 1

Answer 3

最终的结果，就是reader_lend_record 取两个字段，r1取一个字段，c1取4个字段，b1取一个字段。
因为不了解你们的业务，感觉你们得实现逻辑是有问题的。。
建议使用
1. with as调理清晰一点。
2.  取值少的使用子查询
3. 左右关联使用(+)方式

Answer 4

把查询的sql写好，放在视图里面，以后查询视图就好了。