报表数据查询ＳＱＬ

最新推荐文章于 2024-06-19 10:59:12 发布

原创最新推荐文章于 2024-06-19 10:59:12 发布 · 716 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#报表 #date #basic

SELECT zonecode, code, count(8)
FROM (select (CASE
                 WHEN 1 < 3 THEN
                  rpad(SUBSTR(r.curr_address, 1, 2 * (1 + 1)), '10', '0')
                 WHEN SUBSTR(r.curr_address, 7, 2) = '00' THEN
                  SUBSTR(r.curr_address, 1, 6) || '&' ||
                  SUBSTR(r.curr_address, 8, 2) || 'A'
                 ELSE
                  r.curr_address || 'A'
               END) AS zonecode,
               p.sex || '001' as code
          from rehr_hivetc_screening_result t,
               rehr_personal_basic_info     p,
               rehr_basic_info              r
         where r.curr_address like '53%'
           and t.first_sift = '1'
           and t.record_no = r.record_no
           and t.record_no = p.record_no
           and r.create_date >= to_date('2011-01-01', 'yyyy-mm-dd')
           and r.create_date <= to_date('2011-12-01', 'yyyy-MM-dd'))
GROUP BY zonecode, code
union
SELECT zonecode, code, count(8)
FROM (select (CASE
                 WHEN 1 < 3 THEN
                  rpad(SUBSTR(r.curr_address, 1, 2 * (1 + 1)), '10', '0')
                 WHEN SUBSTR(r.curr_address, 7, 2) = '00' THEN
                  SUBSTR(r.curr_address, 1, 6) || '&' ||
                  SUBSTR(r.curr_address, 8, 2) || 'A'
                 ELSE
                  r.curr_address || 'A'
               END) AS zonecode,
               p.sex || '002' as code
          from rehr_hivetc_screening_result t,
               rehr_personal_basic_info     p,
               rehr_basic_info              r
         where r.curr_address like '53%'
           and t.sec_sift = '1'
           and t.record_no = r.record_no
           and t.record_no = p.record_no
           and r.create_date >= to_date('2011-01-01', 'yyyy-mm-dd')
           and r.create_date <= to_date('2011-12-01', 'yyyy-MM-dd'))
GROUP BY zonecode, code
union
SELECT zonecode, code, count(8)
FROM (select (CASE
                 WHEN 1 < 3 THEN
                  rpad(SUBSTR(r.curr_address, 1, 2 * (1 + 1)), '10', '0')
                 WHEN SUBSTR(r.curr_address, 7, 2) = '00' THEN
                  SUBSTR(r.curr_address, 1, 6) || '&' ||
                  SUBSTR(r.curr_address, 8, 2) || 'A'
                 ELSE
                  r.curr_address || 'A'
               END) AS zonecode,
               p.sex || '003' as code
          from rehr_hivetc_screening_result t,
               rehr_personal_basic_info     p,
               rehr_basic_info              r
         where r.curr_address like '53%'
           and t.wb_confirmation = '1'
           and t.record_no = r.record_no
           and t.record_no = p.record_no
           and r.create_date >= to_date('2011-01-01', 'yyyy-mm-dd')
           and r.create_date <= to_date('2011-12-01', 'yyyy-MM-dd'))
GROUP BY zonecode, code
union
SELECT zonecode, code, count(8)
FROM (select (CASE
                 WHEN 1 < 3 THEN
                  rpad(SUBSTR(r.curr_address, 1, 2 * (1 + 1)), '10', '0')
                 WHEN SUBSTR(r.curr_address, 7, 2) = '00' THEN
                  SUBSTR(r.curr_address, 1, 6) || '&' ||
                  SUBSTR(r.curr_address, 8, 2) || 'A'
                 ELSE
                  r.curr_address || 'A'
               END) AS zonecode,
               p.sex || '004' as code
          from rehr_hivetc_screening_result t,
               rehr_personal_basic_info     p,
               rehr_basic_info              r
         where r.curr_address like '53%'
           and t.FIRST_SIFT in ('1', '2')
           and t.record_no = r.record_no
           and t.record_no = p.record_no
           and r.create_date >= to_date('2011-01-01', 'yyyy-mm-dd')
           and r.create_date <= to_date('2011-12-01', 'yyyy-MM-dd'))
GROUP BY zonecode, code
order by zonecode, code