统计各个条件下的数据

select
shop_id,sum(amount) 总金额,
sum(case when status<99 then amount else 0 end) as 已使用,
sum(case when status=99 then amount else 0 end) as 已冻结 
from orders 
group by shop_id

统计每日,每月,每年的数据

SELECT
	YEAR( FROM_UNIXTIME(created_at) ) AS 年,
	MONTH ( FROM_UNIXTIME(created_at) ) AS 月,
	DAY ( FROM_UNIXTIME(created_at )) AS 日,
	COUNT( 1 ) AS 数量,
	sum( amount ) AS 销售金额 
FROM
	orders
GROUP BY
	年,月,日

可能数据库能存储的字段不是时间戳而是日期的话 可能直接调用 year、month、day获取 支持对动态获取的字段做排序

除了 年月日 还可以取 周 WEEk 季 QUARTER

某列去重

select COUNT(shop_id),COUNT(distinct shop_id),COUNT(distinct user_id) from orders

按年统计1月到12个月的销量

SELECT 
 YEAR(FROM_UNIXTIME(created_at)  ) AS '年',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 1 THEN amount ELSE 0 END ) AS '一月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 2 THEN amount ELSE 0 END ) AS '二月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 3 THEN amount ELSE 0 END ) AS '三月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 4 THEN amount ELSE 0 END ) AS '四月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 5 THEN amount ELSE 0 END ) AS '五月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 6 THEN amount ELSE 0 END ) AS '六月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 7 THEN amount ELSE 0 END ) AS '七月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 8 THEN amount ELSE 0 END ) AS '八月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 9 THEN amount ELSE 0 END ) AS '九月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 10 THEN amount ELSE 0 END ) AS '十月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 11 THEN amount ELSE 0 END ) AS '十一月',
	SUM( CASE WHEN MONTH ( FROM_UNIXTIME(created_at) )= 12 THEN amount ELSE 0 END ) AS '十二月' 
FROM
	orders 
GROUP BY
	YEAR (FROM_UNIXTIME(created_at))

数据库函数 字符串替换

repalce(指定字段,需要替换的内容,替换后的内容)

获取

SELECT id,zone_id,name,replace(icon,".jpg",".org") as newicon from shop_tags WHERE icon like '%jpg' and zone_id in(46969,46970,46971,46972,46973,46974,46975,46976,46977,46978)

更新

UPDATE shop_tags set icon = replace(icon,"jpg","org") WHERE icon like '%jpg' and zone_id in(46969,46970,46971,46972,46973,46974,46975,46976,46977,46978)