国外做旅游攻略的网站,深圳网站建设叶林,网站整体建设方案,做液压的公司网站一.字符串截取
left: 从左往右截取字符 right: 从右往左截取字符
如截取4个字符: SELECT left( column_name, 4 ) from table SELECT right( column_name, 4 ) from table 二.条件统计
COUNT(CASE WHEN column_name ‘value’ THEN 1 END) AS count_name
如截统计值1,值2的…一.字符串截取
left: 从左往右截取字符 right: 从右往左截取字符
如截取4个字符: SELECT left( column_name, 4 ) from table SELECT right( column_name, 4 ) from table 二.条件统计
COUNT(CASE WHEN column_name ‘value’ THEN 1 END) AS count_name
如截统计值1,值2的数量: SELECT COUNT(CASE WHEN column_name ‘value1’ THEN 1 END) AS count_name1, COUNT(CASE WHEN column_name ‘value2’ THEN 1 END) AS count_name2, COUNT(*) AS total from table 三.时间转字符
to_char( column_name, ‘YYYY年MM月’ ) 如: select to_char( column_name, ‘YYYY年MM月’ ) from table 四.时间格式化
:Date 时间强制转换,把时间戳转换为日期 如: select create_time:Date as create_date from table date_trunc(‘year’, now()) 获取本年1月1日0时0分时间 date_trunc(‘month’, now()) 获取本月1日0时0分时间 date_trunc(‘day’, now()) 获取今天0时0分时间 如查询今年创建的数据: SELECT * FROM table WHERE create_time date_trunc(‘year’, now()) 五,条件返回值
if(column_namevalue, value_when_true, value_when_false) 当某列值等于value时返回value_when_true, 否则返回value_when_false 如查询column_name为1就返回1, 其他值都返回0 SELECT if(column_name1, 1, 0) as column_name from table 如条件求和,当值大于value才累加 SELECT sum(if(column_namevalue, column_name, 0) )as column_name from table 六.返回第一个非空值
COALESCE ( column_name, value1,value2 ) 如某列为空则赋值为0,否则为它本身 SELECT COALESCE (column_name, 0 ) from table
