select substr(unitcode,0,6),
count(*),
--01--
sum(case substr(jqxzcode,0,4) when '0105' then 1 else 0 end) as sharen,
sum(case substr(jqxzcode,0,4) when '0109' then 1 else 0 end) as qiangjie,
sum(case substr(jqxzcode,0,4) when '0112' then 1 else 0 end) as qiangduo,
sum(case substr(jqxzcode,0,4) when '0110' then 1 else 0 end) as daoqie,
sum(case substr(jqxzcode,0,4) when '0114' then 1 else 0 end) as dubo,
sum(case when(jqxzcode like '01%' and jqxzcode not like '0105%' and jqxzcode not like '0109%' and jqxzcode not like '0112%' and jqxzcode not like '0110%' and jqxzcode not like '0114%') then 1 else 0 end) as qita1,
--02--
sum(case substr(jqxzcode,0,6) when '020108' then 1 else 0 end) as douou,
sum(case substr(jqxzcode,0,4) when '0207' then 1 else 0 end) as qiaozha,
sum(case when (jqxzcode like '02%' and jqxzcode not like '020108%' and jqxzcode not like '0207%') then 1 else 0 end) as qita2,
--qt--
sum(case substr(jqxzcode,0,2) when '08' then 1 else 0 end) as jiufen,
sum(case substr(jqxzcode,0,2) when '05' then 1 else 0 end) as qiuzhu,
sum(case substr(jqxzcode,0,4) when '0602' then 1 when '0603' then 1 else 0 end) as qiuzhu,
sum(case substr(jqxzcode,0,4) when '0601' then 1 else 0 end) as xiansuojubao,
sum(case substr(jqxzcode,0,2) when '03' then 1 else 0 end) as jiaotongshigu,
sum(case substr(jqxzcode,0,2) when '09' then 1 else 0 end) as shehuiliandong,
sum(case substr(jqxzcode,0,2) when '04' then 1 else 0 end) as huozhai,
sum(case when (jqxzcode like '%' and jqxzcode not like '01%' and jqxzcode not like '02%' and jqxzcode not like '03%' and jqxzcode not like '04%' and jqxzcode not like '05%' and jqxzcode not like '0601%' and jqxzcode not like '0602%' and jqxzcode not like '0603%' and jqxzcode not like '08%' and jqxzcode not like '09%' )then 1 else 0 end) as qita3
from jqfxk j
where
j.bjtime>to_date('2015-07-25 16:00:00','yyyy-mm-dd hh24:mi:ss')
and
j.bjtime<to_date('2015-08-25 16:00:00','yyyy-mm-dd hh24:mi:ss')
and
jqxzcode is not null and
unitcode in ('431100000000','431103000000','431102000000','431122000000','431121000000','431123000000','431129000000','431124000000','431128000000','431126000000','431129000000')
group by substr(unitcode,0,6)
以上SQL生成一个下面的表格统计数据:
横栏是unitcode单位
横栏是jqxzcode
select bzdwcode, jqtime,
sum(cnt) as cnt, ---日合计
sum(num0) as num0, --其他类合计
sum(num1) as num1, --抢 劫010501 总数
sum(num2) as num2, --抢 持枪
sum(num3) as num3, --抢 室外 摩托
sum(num3) as num4,--抢 室外 汽车
sum(num3) as num5,--抢 室外 财物
sum(num3) as num6,--抢 入室 财物
sum(num3) as num7,--抢夺 总数
sum(num3) as num8,--抢夺 飞车
sum(num3) as num9,--抢夺 非 飞车
sum(num3) as num10, -- 盗 总数
sum(num3) as num11, --盗 室外 摩托
sum(num3) as num12,--盗 室外 汽车
sum(num3) as num13,---盗 室外 财物
sum(num3) as num14---盗 入室 财物
from (
select substr(jqtime,1,10) jqtime,
case when substr(bzdwcode,1,6) not like '445221%' and
substr(bzdwcode,1,6) not like '445222%' and
substr(bzdwcode,1,6) not like '445224%' and
substr(bzdwcode,1,6) not like '445281%' then '445200' else substr(bzdwcode,1,6) end bzdwcode,
count(*) as cnt,
sum(case when (bzjqxzdm like '01%' or bzjqxzdm like '0414%')
and bzjqxzdm not like '010501%' and bzjqxzdm not like '010504%' and bzjqxzdm not like '010502%' then 1 else 0 end) num0,
sum(case when bzjqxzdm like '010501%' then 1 else 0 end ) num1,
sum(case when bzjqxzdm like '01050150%' or bzjqxzdm like '01050160%'
or bzjqxzdm like '0105016A%' or bzjqxzdm like '0105016B%' then 1 else 0 end ) num2,
sum(case when bzjqxzdm like '0105012A%' then 1 else 0 end ) num3,
sum(case when bzjqxzdm like '0105012B%' or bzjqxzdm like '01050120%' then 1 else 0 end ) num4,
sum(case when bzjqxzdm like '01050102%' then 1 else 0 end ) num5,
sum(case when bzjqxzdm like '01050101%' then 1 else 0 end ) num6,
sum(case when bzjqxzdm like '010504%' then 1 else 0 end) num7,
sum(case when bzjqxzdm like '01050401%' then 1 else 0 end) num8,
sum(case when bzjqxzdm like '010504%' and bzjqxzdm not like '01050401%' then 1 else 0 end) num9,
sum(case when bzjqxzdm like '010502%' then 1 else 0 end) num10,
sum(case when bzjqxzdm like '01050224%' then 1 else 0 end) num11,
sum(case when bzjqxzdm like '01050223%' then 1 else 0 end) num12,
sum(case when bzjqxzdm like '01050240%' then 1 else 0 end) num13,
sum(case when bzjqxzdm like '01050201%' then 1 else 0 end) num14
from
JQCASE t where substr(bzdwcode,1,6) <> '445221' and substr(bzdwcode,1,6) <> '445224' and substr(bzdwcode,1,6) <> '445222' and substr(bzdwcode,1,6) <> '445281' --and jqtime >=? and jqtime <=?
group by substr(bzdwcode,1,6), substr(jqtime,1,10) ) a
group by bzdwcode,jqtime order by jqtime, bzdwcode
相关推荐
mysql 中case when 遇到乱码解决。查询语句中含有case引起中文乱码解决方法
详细讲解了case、when的用法.ASE表达式可以在SQL中实现if-then-else型的逻辑,而不必使用PL/SQL。CASE的工作方式与DECODE()类似,但应该使用CASE,因为它与ANSI兼容。
然后有一种语法让我眼前一亮,case when then else end 当满足CASE设定的条件时,就可以执行then语句。由于我要做的分组查询统计,是要罗列每一种情况,而且根据输入的“管理员编号”不同返回不同结果,结果记录的...
Oracle sql文总结(case when,查看执行计画,文本操作,查看锁表及解锁,游标)
Oracle Sql性能优化 解宝喆 1、选择最有效率的表名顺序(只在基于规则的优化器中有效): ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在...
oracle 存储过程,实现列表数据行转置成列显示table效果;用游标的方式实现;
C# 访问Oracle示例+PL/SQL+存储过程+触发器 完整示例 测试可用 --PL/SQL基础1 declare begin dbms_output.('不输出不换行'); dbms_output.put_line('输出并换行'); end; --PL/SQL基础2 declare dig number(20,...
Case When实现行列转换时会出现多条记录,如果不用聚合函数直接进行group by分组,那么检索的是基表里分组字段的第一条记录,如果使用max()函数之后再进行group by分组,那么就会检索每个字段的最大值然后再分组
统计(CASE WHEN等) 第三部分 高级SQL的编写 MERGE INTO 第三部分 高级SQL的编写 JOIN 第三部分 高级SQL的编写 JOIN 第三部分 高级SQL的编写 JOIN 第三部分 高级SQL的编写 JOIN 第三部分 高级SQL的编写 JOIN 第三...
对SQL排序,只要在order by后面加字段就可以了,可以通过加desc或asc来选择降序或升序。但排序规则是默认的,数字、时间、字符串等都有自己默认的排序规则。有时候需要按自己的想法来排序,而不是按字段默认排序规则...
但是在遇到需要组合条件进行统计时,有时却不如case when 或者另建视图好用。 可是有时就想用DECODE,咋办? 这里给大家介绍一下DECODE如何实现组合条件查询,一句SQL查询一张中国式报表。 (本文的示例,在实际业务...
-- when column_id > 100 then 'H' || substrb(to_char(column_id),2,1) -- else '**' -- end ) column_id ,all_c.column_id column_id ,lower(all_c.column_name) column_name ,lower((case all_c.data_type ...
12.1.1 Case When改造 334 12.1.2 Rownum分页改写 337 12.1.3 Hint直接路径改造 338 12.1.4 只取你所需的列 339 12.1.5 避免或者减少递归调用 341 12.1.6 ROWID优化应用 347 12.2 设法避免外因影响 350 ...
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# ...
Web_THBC 为表示层也就是页面(.aspx) BLL_THBC 为业务逻辑层 DAL_THBC 为数据库交互层 (向数据库执行SQL语句) Model_THBC 为实体类 DbHelper 为数据库连接类 统计个专营店的男女数量 代码如下: select dua....
select case when mod(id,2)=1 and id=(select count(*) from seat) then id when mod(id,2)=1 then id+1 else id-1 end id,student from seat order by id ; /* Write your PL/SQL query statement below */ ...
介绍了oracle存储过程中常见的sql写法,游标(隐式,显式),触发器, CASE ... WHEN ... THEN ...ELSE ... END,IF... THEN ... ELSIF ...THEN...ELSE...END IF,记录类型变量定义和使用 ,%type定义变量,%rowtype定义变量...
Use Oracle's SQL and PLSQL APIs for building analytical solutions Acquire knowledge of common and widely-used business statistical analysis techniques Who this book is for IT executives, BI architects...
该SQL可以直接导出oracle用户下的表结构信息,结果包含了表的字段基本信息,主键信息,以及字段备注信息等,经生产测试可用。sql脚本内容如下 --注:当前查询结果只包含 有主键字段的表结构信息 Select A.OWNER ...