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 tset (t.id,t.greatedate) =(select s.id,s.greatedate from lws_q_bl_result swhere nvl(s.groupresult_id, '#') = t.groupresult_idand nvl(s.sample_id, '#') = t.sample_idand nvl(s.detect_orgcode, '#') =t.detect_orgcodeand nvl(s.monitorsort, '#')=t.monitorsortand nvl(s.hi_detect, '#')=t.hi_detectand nvl(s.hi_date, to_date('1950-01-01', 'yyyy-mm-dd'))=t.hi_dateand nvl(s.hi_status, '#')=t.hi_statusand nvl(s.srh_detect, '#')=t.srh_detectand nvl(s.srh_date, to_date('1950-01-01', 'yyyy-mm-dd'))=t.srh_dateand nvl(s.srh_status, '#')=t.srh_statusand nvl(s.note, '#')=t.noteand nvl(s.detectmethod, '#')=t.detectmethodand nvl(s.del, '#')=t.deland nvl(s.state, '#')=t.stateand nvl(s.orgcode, '#')=t.orgcodeand nvl(s.greate, '#')=t.greateand nvl(s.changer, '#')=t.changerand nvl(s.changedate, to_date('1950-01-01', 'yyyy-mm-dd'))=t.changedateand nvl(s.send_org, '#')=t.send_organd nvl(s.receive_org, '#')=t.receive_organd nvl(s.auditor, '#')=t.auditorand nvl(s.detector, '#')=t.detectorand nvl(s.hi_type, '#')=t.hi_typeand 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，如下

?

??

现在要把该表插到另一个表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||%'? （查询时要在列名前加上||）?

?

?

?

?