Postgresql 动态统计某一列的某一值出现的次数_AnAnDawn的博客

实例解析：

select to_char(log.date, 'yyyy-MM-dd HH24') as hour,
log.exten, sum(case log.grade when '1' then 1 else 0 end) as "1",
sum(case log.grade when '2' then 1 else 0 end) as "2",
sum(case log.grade when '3' then 1 else 0 end) as "3",
sum(case log.grade when '4' then 1 else 0 end) as "4",
sum(case log.grade when '5' then 1 else 0 end) as "5",
log.direction from iface_satisfaction_investigation as log
where log.date >= '2017-08-03 00:00:00' and log.date < '2017-08-04 00:00:00'
group by hour,log.exten,log.direction order by hour,log.exten,log.direction asc

to_char:用于查询时间格式化， to_char(log.date, 'yyyy-MM-dd HH24')，大致的结果是：2017-08-03 13

sum():毫无疑问是用来计算总和的。

sum(case log.grade when '1' then 1 else 0 end)是计算什么呢

他的意思就是：

计算grade这个列的值为1的时候有多少行，后面的sum(……)就类推了。

其他的也没有什么好讲的了

select class.id,class.name,table.studentNum from class left join --联合student和class表，先查出每个班有多少个学生 (select class.id,count(student.id) as studentNum from class left join student on class.id=student.class_id 实例解析：select to_char(log.date, 'yyyy-MM-dd HH24') as hour,log.exten, sum(case log.grade when '1' then 1 else 0 end) as "1",sum(case log.grade when '2' then 1 else 0 end) as "2",sum(case log.grade when ... 一、数值类型：下面是 PostgreSQL 所支持的数值类型的列表和简单说明：名字存储空间描述范围smallint2 字节小范围整数-32768 到 +32767integer4 字节常用的整数-2147483648 到 +2147483647bigint8 字节大范围的整数-9223372036854775808 到 9223372036854775807decimal变长用户声明精度，精确... PostgreSQL 中统计指定字符或者单词或者字符串在一个长字符串中出现总次数 select array_length(regexp_split_to_array('\u5206\u4EAB\u6570\u636E\u5E93\u524D\u6CBF\uFF0C\u89E3\u6784\u5B9E\u6218\u5E72\u8D27\uFF0C\u63A8\u52A8\u6570\u636E\u5E93\u6280\u672F\u53D8\u9769groupCodedatabasegroupDesc\u6 SELECT * from ( SELECT array_length(regexp_split_to_array(gs.gss,'/\*'),1)-1 as le, array_length(regexp_split_to_... SELECT max("Scenic_Id") FROM "tbScenics" GROUP BY "Scenic_Name", "Scenic_Des", "Scenic_Price", "Scenic_Ticket" 统计某个字符出现的次数，本示例是统计逗号出现的次数：由于照片字段是支持上传多张照片的，上传多张后会自动用逗号隔开，所以思路是数逗号出现的次数达到计数的目的。 select id, length(ca.photo) - length(translate(ca.photo,',',''))+1 as 照片数量 from call as ca 其中translate函数的解释如下：