以下都是以mysql为基础书写的一些sql语句
1,统计当月的数量数据:
eg: SELECT COUNT(*) AS value from table where month(case_time)=month(now()) 查询caseTime 为当月的数量 MONTH函数返回当前日期的月份,如果月份为null,则返回null,月份为0则返回0.
2,统计当月的总钱数数据:
eg:SELECT "集团总计" AS `name` , ROUND(SUM(confirm_money)) AS value from table where month(case_time)=month(now()) ROUND函数用于数据的四舍五入,它有两种形式:
1、ROUND(x,d) ,x指要处理的数,d是指保留几位小数。2、ROUND(x) ,其实就是round(x,0),也就是默认d为0;
3,统计超期的数据:
eg:SELECT "超30天未结算" AS `name` ,count(1) AS value from table where month(case_time)= month(now()) AND datediff(now(),service_date)>30 AND confirm_date is null
DATEDIFF函数返回两个日期的天数。
4.当月的另一种比较
eg:SELECT "当月回厂当月结算" AS `name` ,count(1) AS value FROM table WHERE month(case_time)= month(now()) AND DATE_FORMAT(service_date, '%Y-%m') = DATE_FORMAT(confirm_date, '%Y-%m')
DATE_FORMAT(date,format);date 为时间参数,format为指定格式。格式有很多种,具体参考https://blog.csdn.net/moakun/article/details/82290387
5,去重后统计
eg:SELECT "注册用户数" AS `name` ,count(DISTINCT phone_number) AS value FROM table WHERE phone_number != ''
统计用户数量,去重后统计
6,将子查询作为分母
eg:SELECT DATE_FORMAT(c.create_time, '%Y/%m/%d %T') AS x ,COUNT(company_id)/(SELECT COUNT(*) FROM warn_info WHERE company_id !='') AS y, "1" AS s FROM `table` w LEFT JOIN table c ON w.company_id = c.id GROUP BY company_id ORDER BY y DESC LIMIT 0,4
将一个子查询作为分母