1，一个表先将01,02,03类型编码转换文字，在做统计
select CASE IMPLEMENT_PROPETY WHEN '01' then '法定机关' WHEN '02' then '授权组织' WHEN '03' then '受委托组织' ELSE '其他' END MMMMMM from test.pre_service
最终sql结果如下
SELECT temp.MMMMMM WW,COUNT(*) as Total from (select CASE IMPLEMENT_PROPETY WHEN '01' then '法定机关' WHEN '02' then '授权组织' WHEN '03' then '受委托组织' ELSE '其他' END MMMMMM from test.pre_service ) temp group by temp.MMMMMM
//代码原型 select temp.type as type, count(type) as Totalfrom(select case 字段 WHEN '1' then '苹果'WHEN '2' then '香蕉'WHEN '3' then '西瓜' end as typefrom table ) temp where type is not null group by temp.type
2.
//1 ---select IMPLEMENT_PROPETY , COUNT(*) Num from test.pre_service GROUP BY IMPLEMENT_PROPETY-- SELECT dept_name ,COUNT(*) AAAA from test.pre_service_snapshot GROUP BY dept_name-- SELECT dept_name, INFO_TYPE , COUNT(*) Num from test.pre_service_snapshot GROUP BY dept_name,INFO_TYPE-- SELECT dept_name,SERVICE_TYPE ,COUNT(*) Num from test.pre_service_snapshot where INFO_TYPE='即办件' group by dept_name,SERVICE_TYPE-- select DEPT_NAME ,COUNT(*) numBer from test.pre_service GROUP BY DEPT_NAME-- select IMPLEMENT_PROPETY , COUNT(*) Num from test.pre_service GROUP BY IMPLEMENT_PROPETY-- select IMPLEMENT_PROPETY , COUNT(*) Num from test.pre_service GROUP BY IMPLEMENT_PROPETY-- select DATE_FORMAT(PROMISEE_TIME,'%Y-%m') date ,count(projid) num ,HANDLESTATE from test.pre_apasinfo -- group by DATE_FORMAT(PROMISEE_TIME,'%Y-%m'),HANDLESTATE-- -- select hander_deptname , COUNT(*) Accept_Number FROM test.pre_transact GROUP BY hander_deptname-- -- select DATE_FORMAT(TRANSACT_TIME,'%Y-%m') date ,count(projid) num ,hander_deptname from test.pre_transact -- group by DATE_FORMAT(TRANSACT_TIME,'%Y-%m'),hander_deptname---SELECT SUM(*) from pre_serv-- select transact_result hander_deptname , COUNT(*) Accept_Number ,hander_deptname FROM test.pre_transact GROUP BY transact_result-- select transact_result transact_result , COUNT(*) hander_Number FROM test.pre_transact GROUP BY transact_result-- select OBJECT_NAME name_institution , COUNT(*) Complain_Number FROM test.pre_complain GROUP BY OBJECT_NAME---select hander_deptname , COUNT(*) Accept_Number FROM test.pre_transact GROUP BY hander_deptname
3.计算两个年月日相减得的天数
select END_TIME,START_TIME, datediff(END_TIME, START_TIME) from pre_node
4.删除表中的所有数据
Delete from pre_complain3 where 1=1
5.多列统计.如对的数量,不对的数量,总的数量
SELECT DISTINCT (SELECT COUNT(*) Today FROM Node_UDFInfo where UDF='0') Today, (SELECT COUNT(*) Tol FROM Node_UDFInfo) Tol FROM Node_UDFInfo
-- 统计-- SELECT DISTINCT (SELECT COUNT(*) Today FROM Node_UDFInfo where UDF='0') Today, (SELECT COUNT(*) Tol FROM Node_UDFInfo) Tol FROM Node_UDFInfo-- SELECT * from Node_UDFInfo WHERE * in (-- SELECT * from Node_UDFInfo where UDF='0');-- -- SELECT name , count(name)name ,PHASE_NAME from Node_UDFInfo WHERE UDF='0' AND PHASE_NAME='办结阶段' GROUP BY name -- -- SELECT PHASE_NAME,COUNT(*) from Node_UDFInfo GROUP BY PHASE_NAME
oracle 只打印300行数据
select * from tm_bus_passenger_updown_his LIMIT 300;
查询的结果是导入的值
如果两表字段相同，则可以直接这样用。
insert into table_a select * from table_b一般使用下面形式 insert /*+append*/ into TK**05 nologging select * from ****_HOUR3
如果两表字段不同，a表需要b中的某几个字段即可，则可以如下使用： insert into table_a(field_a1,field_a2,field_a3) select field_b1,field_b2,field_b3 from table_b还可以加上where条件
//添加测试数据 两个表的字段一样INSERT INTO test03 (city_no , dept_no , fila_no ,group_no , mach_no ,line_no , bus_no , is_up_down , label_no , up_passenger , down_passenger , site_time , ins_time ,is_trans , mark ,station_full_rate ,total_people_num ) ( select * from TM_BUS_PASSENGER_UPDOWN_PRE5 where rownum<=300 )
create table test04 asselect city_no cityno,dept_no deptno,fila_no filano,group_no groupno,mach_no machno,line_no lineno,bus_no busno,is_up_down isupdown,label_no labelno,up_passenger uppassenger,down_passenger downpassenger,site_time sitetime,ins_time instime,is_trans istrans,mark,station_full_rate stationfullrate,total_people_num totalpeoplenum,lpad(bus_no,6,0)||reverse(to_char(site_time,'yyyymmddhh24miss')) rowkeyfrom tm_bus_passenger_updown_hiswhere site_time
orcale设置等长
create or replace view v_tm_bus_passenger_updown asselect city_no cityno,dept_no deptno,fila_no filano,group_no groupno,mach_no machno,line_no lineno,bus_no busno,is_up_down isupdown,label_no labelno,up_passenger uppassenger,down_passenger downpassenger,site_time sitetime,ins_time instime,is_trans istrans,mark,station_full_rate stationfullrate,total_people_num totalpeoplenum,lpad(bus_no,6,0)||reverse(to_char(site_time,'yyyymmddhh24miss')) rowkeyfrom tm_bus_passenger_updown_hiswhere site_time