SQL例子

1）

select d.code,d.names, n.sname from
(select t.code_syndrome code,t.name_syndrome names from csmw_management_syndrome t) d,
(SELECT '01' CODE,'发病数' sNAME FROM dual UNION ALL SELECT '02' CODE,'占缺课总人数百分比'names FROM dual) n
order by code,n.code

 

ps: d和n分别表示一个表，两个表做笛卡尔积。

 

 

2）select distinct(s.date_report) A,s.ILL_STUDENT_NUM B,s.zonecode C  from    csmw_statistics_s_syndrome s

 

 结果为：

               A                B        C      

     1 2011-06-28     16   31011500
     2 2011-06-28     70   31011500
     3 2011-06-29     30   31011500
     4 2011-06-29     60   31011500

 

如果为select distinct(s.date_report) A,s.zonecode C  from    csmw_statistics_s_syndrome s

 

结果为：

           A              C

1 2011-06-28 31011500
2 2011-06-29 31011500

 

如果 select distinct(s.date_report A,s.ILL_STUDENT_NUM B,s.zonecode C）  from    csmw_statistics_s_syndrome s  会报错，distinct只能用于一个字段或所有字段。

 

 

3）一个表为：

   date_report  code   ill_count

1 2011-06-28    01       16
2 2011-06-28    02       16
3 2011-06-28    01       70
4 2011-06-28    02       70
5 2011-06-29    01       30
6 2011-06-29    02       30
7 2011-06-29    01       60
8 2011-06-29    02       60

 

ill_count是每一个日期的生病总人数，那么不同的code的ill_count也是相同的。因此想求每天的ill_count总数，

那么不能写：select date_report,sum(ill_count) from A group by date_report ,这样的ill_count就会算重了，

因此应该写：select date_report,sum(ill_count) from ( select distinct(date_report),ill_count from A) group by date_report

 

4）orcale中没有split函数，需要自己写函数实现，一般写的函数都会返回一个table类型的值回来，所以取的话也要

     这样写：

    select * from table( split('a,b',',') )

    另外replace函数返回的是一个字符串，所以如果想用  a in (replace('01||02','||',',')的话实际上in里面是一个值，而不是想像中的 a in (01,02),所以想实现这样的功能的话应该再加上split函数，结合起来，如下：

   a in (select * from table(split(REPLACE('01||02','||',','),',')))

 

 5）按一个列分组，然后取每个分组的记录数，如select age,count(*) from users group by age

     结果为 a 6 ,b 3 ,c 9 这样，但是如果在根据插入时间来筛选的话，就可能会出现a 5,b 2。c就没有了，现在想要出现

     a 5,b 2,c 0，就要写成：

     select '0' ,age from sari_j_caseinfo where inserttime>2011-01-01  union all
     select to_char(count(*)),age from users where inserttime>2011-01-01  group by age 

 

 

6) 有表 lws_q_bl_result ,字段为

groupresult_id, sample_id, detect_orgcode, monitorsort, hi_detect, hi_date, hi_status, srh_detect, srh_date, srh_status, note, detectmethod, del, state, orgcode, greate, greatedate, changer, changedate, id, send_org, receive_org, auditor, detector, hi_type

 

需求：lws_q_bl_result 表中的 sample_id 应是唯一的，所以每个相同sample_id只保留一条，由于基本上重复的数据均是除了id,greatedate都相同，所以只要把除了id,greatedate两列以外其余列都相同的数据保留第一条，其余的数据的del 字段设为1就可以了。

        

         第一步：取得除了id,greatedate两列以外其余列都相同的数据

     

select id
          from lws_q_bl_result t
         where (nvl(groupresult_id, '#'), nvl(sample_id, '#'),
                nvl(detect_orgcode, '#'), nvl(monitorsort, '#'),
                nvl(hi_detect, '#'),
                nvl(hi_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                nvl(hi_status, '#'), nvl(srh_detect, '#'),
                nvl(srh_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                nvl(srh_status, '#'), nvl(note, '#'), nvl(detectmethod, '#'),
                nvl(del, '#'), nvl(state, '#'), nvl(orgcode, '#'),
                nvl(greate, '#'), nvl(changer, '#'),
                nvl(changedate, to_date('1950-01-01', 'yyyy-mm-dd')),
                nvl(send_org, '#'), nvl(receive_org, '#'), nvl(auditor, '#'),
                nvl(detector, '#'), nvl(hi_type, '#')) in
               (select nvl(groupresult_id, '#'),
                       nvl(sample_id, '#'),             -- 当值是空时用#号代替
                       nvl(detect_orgcode, '#'),
                       nvl(monitorsort, '#'),
                       nvl(hi_detect, '#'),
                       nvl(hi_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                       nvl(hi_status, '#'),
                       nvl(srh_detect, '#'),
                       nvl(srh_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                       nvl(srh_status, '#'),
                       nvl(note, '#'),
                       nvl(detectmethod, '#'),
                       nvl(del, '#'),
                       nvl(state, '#'),
                       nvl(orgcode, '#'),
                       nvl(greate, '#'),
                       nvl(changer, '#'),
                       nvl(changedate, to_date('1950-01-01', 'yyyy-mm-dd')),
                       nvl(send_org, '#'),
                       nvl(receive_org, '#'),
                       nvl(auditor, '#'),
                       nvl(detector, '#'),
                       nvl(hi_type, '#')
                  from lws_q_bl_result
                 where del = 0
                 group by nvl(groupresult_id, '#'),
                          nvl(sample_id, '#'),
                          nvl(detect_orgcode, '#'),
                          nvl(monitorsort, '#'),
                          nvl(hi_detect, '#'),
                          nvl(hi_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                          nvl(hi_status, '#'),
                          nvl(srh_detect, '#'),
                          nvl(srh_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                          nvl(srh_status, '#'),
                          nvl(note, '#'),
                          nvl(detectmethod, '#'),
                          nvl(del, '#'),
                          nvl(state, '#'),
                          nvl(orgcode, '#'),
                          nvl(greate, '#'),
                          nvl(changer, '#'),
                          nvl(changedate, to_date('1950-01-01', 'yyyy-mm-dd')),
                          nvl(send_org, '#'),
                          nvl(receive_org, '#'),
                          nvl(auditor, '#'),
                          nvl(detector, '#'),
                          nvl(hi_type, '#')
                having count(*) > 1)

 

              第二步，找出每组重复数据中的除第一行以外的数据。

 

select id from (select id,rownum as row_num from lws_q_bl_result r where r.sample_id = 'B10226072') where row_num>1

      上面这行语句可以找出sample_id='B10226072' 时除第一行以外的数据，但是它无法做到先分组后再取得每组的除第一条以外的数据。故只能先把每组的第一条先插入到一个表里，再做处理。

     

   

insert into lws_q_bl_result_new(
groupresult_id, 
sample_id, 
detect_orgcode, 
monitorsort, 
hi_detect, 
hi_date, 
hi_status, 
srh_detect, 
srh_date, 
srh_status, 
note, 
detectmethod, 
del, 
state, 
orgcode, 
greate, 
changer, 
changedate, 
send_org, 
receive_org, 
auditor, 
detector, 
hi_type
)
select nvl(groupresult_id, '#'),
               nvl(sample_id, '#'),
               nvl(detect_orgcode, '#'),
               nvl(monitorsort, '#'),
               nvl(hi_detect, '#'),
               nvl(hi_date, to_date('1950-01-01', 'yyyy-mm-dd')),
               nvl(hi_status, '#'),
               nvl(srh_detect, '#'),
               nvl(srh_date, to_date('1950-01-01', 'yyyy-mm-dd')),
               nvl(srh_status, '#'),
               nvl(note, '#'),
               nvl(detectmethod, '#'),
               nvl(del, '#'),
               nvl(state, '#'),
               nvl(orgcode, '#'),
               nvl(greate, '#'),
               nvl(changer, '#'),
               nvl(changedate, to_date('1950-01-01', 'yyyy-mm-dd')),
               nvl(send_org, '#'),
               nvl(receive_org, '#'),
               nvl(auditor, '#'),
               nvl(detector, '#'),
               nvl(hi_type, '#')
          from lws_q_bl_result
         where del = 0
         group by nvl(groupresult_id, '#'),
                  nvl(sample_id, '#'),
                  nvl(detect_orgcode, '#'),
                  nvl(monitorsort, '#'),
                  nvl(hi_detect, '#'),
                  nvl(hi_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                  nvl(hi_status, '#'),
                  nvl(srh_detect, '#'),
                  nvl(srh_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                  nvl(srh_status, '#'),
                  nvl(note, '#'),
                  nvl(detectmethod, '#'),
                  nvl(del, '#'),
                  nvl(state, '#'),
                  nvl(orgcode, '#'),
                  nvl(greate, '#'),
                  nvl(changer, '#'),
                  nvl(changedate, to_date('1950-01-01', 'yyyy-mm-dd')),
                  nvl(send_org, '#'),
                  nvl(receive_org, '#'),
                  nvl(auditor, '#'),
                  nvl(detector, '#'),
                  nvl(hi_type, '#')
        having count(*) > 1

 

    3）给 lws_q_bl_result_new 表的 id,greatedate 两列赋值，id取每组第一条记录。

 

     

update lws_q_bl_result_new t
set (t.id,t.greatedate) =
(select s.id,s.greatedate from lws_q_bl_result s
where 
nvl(s.groupresult_id, '#') = t.groupresult_id
and nvl(s.sample_id, '#') = t.sample_id
and nvl(s.detect_orgcode, '#') =t.detect_orgcode
and nvl(s.monitorsort, '#')=t.monitorsort
and nvl(s.hi_detect, '#')=t.hi_detect
and nvl(s.hi_date, to_date('1950-01-01', 'yyyy-mm-dd'))=t.hi_date
and nvl(s.hi_status, '#')=t.hi_status
and nvl(s.srh_detect, '#')=t.srh_detect
and nvl(s.srh_date, to_date('1950-01-01', 'yyyy-mm-dd'))=t.srh_date
and nvl(s.srh_status, '#')=t.srh_status
and nvl(s.note, '#')=t.note
and nvl(s.detectmethod, '#')=t.detectmethod
and nvl(s.del, '#')=t.del
and nvl(s.state, '#')=t.state
and nvl(s.orgcode, '#')=t.orgcode
and nvl(s.greate, '#')=t.greate
and nvl(s.changer, '#')=t.changer
and nvl(s.changedate, to_date('1950-01-01', 'yyyy-mm-dd'))=t.changedate
and nvl(s.send_org, '#')=t.send_org
and nvl(s.receive_org, '#')=t.receive_org
and nvl(s.auditor, '#')=t.auditor
and nvl(s.detector, '#')=t.detector
and nvl(s.hi_type, '#')=t.hi_type
and rownum=1

)

 

 

    4）把符合条件的数据DEL值设为1

  

   

update lws_q_bl_result
   set del = 1
 where id in
       (select id
          from lws_q_bl_result t
         where (nvl(groupresult_id, '#'), nvl(sample_id, '#'),
                nvl(detect_orgcode, '#'), nvl(monitorsort, '#'),
                nvl(hi_detect, '#'),
                nvl(hi_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                nvl(hi_status, '#'), nvl(srh_detect, '#'),
                nvl(srh_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                nvl(srh_status, '#'), nvl(note, '#'), nvl(detectmethod, '#'),
                nvl(del, '#'), nvl(state, '#'), nvl(orgcode, '#'),
                nvl(greate, '#'), nvl(changer, '#'),
                nvl(changedate, to_date('1950-01-01', 'yyyy-mm-dd')),
                nvl(send_org, '#'), nvl(receive_org, '#'), nvl(auditor, '#'),
                nvl(detector, '#'), nvl(hi_type, '#')) in
               (select nvl(groupresult_id, '#'),
                       nvl(sample_id, '#'),
                       nvl(detect_orgcode, '#'),
                       nvl(monitorsort, '#'),
                       nvl(hi_detect, '#'),
                       nvl(hi_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                       nvl(hi_status, '#'),
                       nvl(srh_detect, '#'),
                       nvl(srh_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                       nvl(srh_status, '#'),
                       nvl(note, '#'),
                       nvl(detectmethod, '#'),
                       nvl(del, '#'),
                       nvl(state, '#'),
                       nvl(orgcode, '#'),
                       nvl(greate, '#'),
                       nvl(changer, '#'),
                       nvl(changedate, to_date('1950-01-01', 'yyyy-mm-dd')),
                       nvl(send_org, '#'),
                       nvl(receive_org, '#'),
                       nvl(auditor, '#'),
                       nvl(detector, '#'),
                       nvl(hi_type, '#')
                  from lws_q_bl_result
                 where del = 0
                 group by nvl(groupresult_id, '#'),
                          nvl(sample_id, '#'),
                          nvl(detect_orgcode, '#'),
                          nvl(monitorsort, '#'),
                          nvl(hi_detect, '#'),
                          nvl(hi_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                          nvl(hi_status, '#'),
                          nvl(srh_detect, '#'),
                          nvl(srh_date, to_date('1950-01-01', 'yyyy-mm-dd')),
                          nvl(srh_status, '#'),
                          nvl(note, '#'),
                          nvl(detectmethod, '#'),
                          nvl(del, '#'),
                          nvl(state, '#'),
                          nvl(orgcode, '#'),
                          nvl(greate, '#'),
                          nvl(changer, '#'),
                          nvl(changedate, to_date('1950-01-01', 'yyyy-mm-dd')),
                          nvl(send_org, '#'),
                          nvl(receive_org, '#'),
                          nvl(auditor, '#'),
                          nvl(detector, '#'),
                          nvl(hi_type, '#')
                having count(*) > 1)
              
           and del = 0
           and id not in (select id from lws_q_bl_result_new))

 

   7）

  有表csmw_collection_hospital，如下

 

ID_SYMPTOM_HOSPITAL	SYMPTOM
ECB513EF-5DCC-441C-A6B4-65AC96FBCF8C	8
C83AB542-6926-42E9-B4A6-9F3C8693331F	8
73D14E69-0D77-42C7-80BF-4B4D56F780BC	8
E5515D94-9F81-48CF-B0AE-5B442C93CEF4	8
356AEE63-8D5F-4095-A3BA-15B31ECEAB4F	8
440AED70-46FF-4BD0-BCFB-329FBBBC4955	07||08
1D7B67D8-AF2C-4844-9036-9A07B6E41169	8
AD4B54B4-1297-4B95-BF0B-8D1E8671E46B	8
00471FFD-F044-4B69-8F2E-CCF0B5B4230F	01||02||08

  

现在要把该表插到另一个表A中, 字段为ID，ID_PARENT（为原表的ID_SYMPTOM_HOSPITAL），SYMPTOM ,其中要把SYMPTOM这样有||线的值拆成几条记录（根据||线的数量）

 

编写函数如下：

 

 

 

create or replace function TEST1 return varchar2 is
  Result varchar2(20);
   a varchar2(20);
begin
  

  for z in (select * from csmw_collection_hospital where rownum<100)   //取前100条记录
  loop
  
  if(instr(z.symptom,'||',1,1)>0)   --  instr函数方法为(字段值，查找的字符串，开始位置（从1开始），取第几次出现的）
  then                                          -- =0 表示没查到
  
  for y in (select column_value as dd from table(select split(z.symptom,'||') symptom from dual))    
  loop          --  不能是select * from table(select split(z.symptom,'||') symptom from dual)) ，因为下面的   
                   --  insert语句要用到"y.",如果没有别名的话就无法写，因此需要给table表的colmun_value取个别名 
  insert into A(id,id_parent,symptom) values(sys_guid(),z.id_symptom_hospital,y.dd);
  end loop;
  
  end if;
  
  end loop;
  commit;
  return(Result);
end TEST1;

 

 

 

 8）假如有列的值为9901||9902||9903 ,如果想查询条件为既是9901又是9903的，那么SQL为如下：

       select * from test  where rowname||'||' like '%9901||%9903||%'  （查询时要在列名前加上||） 

      

       如果是mysql的话，则不能使用 || 这个符号，mysql的拼接用的是concat(rowname,"||")

       PS: 这里9901||9902中间的是分隔符，可以随便换成任何特殊符号如逗号分号