SQL 拆分时间并分组

发布网友发布时间：2022-04-11 14:28

共2个回答

热心网友时间：2022-04-11 15:57

--假设你的投诉时间字段是date类型
--统计18年累计到当月的投诉量
select
投诉产品,
sum(case when to_char(投诉时间,'yyyymm')='201801' then 1 else 0 end) "一月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201802' then 1 else 0 end) "二月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201803' then 1 else 0 end) "三月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201804' then 1 else 0 end) "四月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201805' then 1 else 0 end) "五月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201806' then 1 else 0 end) "六月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201807' then 1 else 0 end) "七月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201808' then 1 else 0 end) "八月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201809' then 1 else 0 end) "九月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201810' then 1 else 0 end) "十月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201811' then 1 else 0 end) "十一月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201812' then 1 else 0 end) "十二月"
from 投诉表
group by 投诉产品;
--统计每月投诉量
select
投诉产品,
sum(case when to_char(投诉时间,'yyyymm')='201801' then 1 else 0 end) "一月",
sum(case when to_char(投诉时间,'yyyymm')='201802' then 1 else 0 end) "二月",
sum(case when to_char(投诉时间,'yyyymm')='201803' then 1 else 0 end) "三月",
sum(case when to_char(投诉时间,'yyyymm')='201804' then 1 else 0 end) "四月",
sum(case when to_char(投诉时间,'yyyymm')='201805' then 1 else 0 end) "五月",
sum(case when to_char(投诉时间,'yyyymm')='201806' then 1 else 0 end) "六月",
sum(case when to_char(投诉时间,'yyyymm')='201807' then 1 else 0 end) "七月",
sum(case when to_char(投诉时间,'yyyymm')='201808' then 1 else 0 end) "八月",
sum(case when to_char(投诉时间,'yyyymm')='201809' then 1 else 0 end) "九月",
sum(case when to_char(投诉时间,'yyyymm')='201810' then 1 else 0 end) "十月",
sum(case when to_char(投诉时间,'yyyymm')='201811' then 1 else 0 end) "十一月",
sum(case when to_char(投诉时间,'yyyymm')='201812' then 1 else 0 end) "十二月"
from 投诉表
group by 投诉产品;

热心网友时间：2022-04-11 17:15

group by 月份，什么的，你可以看看sql中group by 的用法，具体的你看看就应该知道了。追问看图1，表里面只有完整时间，不是月份

追答截取字符串取值啊，非得纠结。有什么条件可以获取就按这个条件来截取有用信息，