【题目要求】
数据库中有一张地区数据统计表,但是并不规则 ,记录类似于,225100:02:3:20160725是一串代码,以:分割,第1位为地区代码,第2位为分类代码,第3位为数量,第4位为日期
地区代码含义225100-上海 225200-江苏 225300-浙江 为可能有某些位不存在,缺位时计算规则如下:
1、地区缺位时不参与统计
2、时间缺位时按20151220来计算
数据如下
现要求输入开始日期和结束日期后,自动统计这个时间段内每个地区和各个分类下的数据情况。日期控件中输入的日期格式要求为yyyy-MM-dd
【数据来源】考试数据库wubmh0及chq3g4表
【效果图参考】
第一步:需要将地区缺位的数据去掉以及将时间缺位的数据补上日期,所以这里SQL语句应该这么写:
select substr(part, 0, instr(part, ':')) as area, substr(part, 8, 2) as category ,substr(part, length(part)-7, 8) as time,substr(part, 11, length(part)-19) as amount,part from
(select 11 as id, substr(part, 0, instr(part, ';')) as part from chq3g4 where id=1
UNION ALL
select 12 as id, substr(part, instr(part, ';')+1) as part from chq3g4 where id=1
UNION ALL
select id,part from chq3g4 where id != 4 and id !=8 and id != 1
UNION ALL
select id,'225300:02:14:20151220' as part from chq3g4 where id = 8)
第一部分就是括号里的SQL语句:
select 11 as id, substr(part, 0, instr(part, ';')) as part from chq3g4 where id=1
UNION ALL
select 12 as id, substr(part, instr(part, ';')+1) as part from chq3g4 where id=1
UNION ALL
select id,part from chq3g4 where id != 4 and id !=8 and id != 1
UNION ALL
select id,'225300:02:14:20151220' as part from chq3g4 where id = 8
select 11 as id, substr(part, 0, instr(part, ';')) as part from chq3g4 where id=1
这一句就是将
上图画线的地方提取出来 ,结果为:
第二局:
select 12 as id, substr(part, instr(part, ';')+1) as part from chq3g4 where id=1
这一句是将
图上画线的地方提取出来,结果为:
第三句:
select id,part from chq3g4 where id != 4 and id !=8 and id != 1
这是将不符合要求的去掉,区域没有,时间没有,第一句有两个数据的。结果为:
第四句:
select id,'225300:02:14:20151220' as part from chq3g4 where id = 8
这一句是将
图上数据丢失的时间补上。结果为:
然后将这四句的结果全部加起来,得到:
现在需要将part字段里的区域,分类,数量,日期都单独用列表示出来。
select substr(part, 0, instr(part, ':')) as area, substr(part, 8, 2) as category ,substr(part, length(part)-7, 8) as time,substr(part, 11, length(part)-19) as amount,part from 前面四个加起来的结果
通过substr函数来截取part的各个部分,最后得到结果
最后一步还需要将category中的02,01,03分别改成分类02,分类01,分类03,
最后的SQL语句为:
结果为:
由于本题的参数的格式是2022-10-21这种格式,所以还需要将time字段格式在调整。需要将
substr(part, length(part)-7, 8)
改成:
(substr(part, length(part)-7, 4) || '-' || substr(part, length(part)-3, 2) || '-' || substr(part, length(part)-1, 2)) as time
预览结果为:
最后将查询参数加到SQL语句上:
下一步:制作模板:
最终效果: