MySQL统计相关日期和case

原文链接: http://www.cppblog.com/tx7do/archive/2017/07/19/215119.html

select DATE_SUB(curdate(),INTERVAL 0 DAY) ;

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';

统计本周内数据
select subdate(curdate(),if(date_format(curdate(),’%w’)=0,7,date_format(curdate(),’%w’))-1)

select * from $table where time > ( select date_sub(curdate(),interval 1 week) ) and time<now();

本年第一天：

SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);

SELECT CONCAT(firstname, ' ', Subject) AS StudentDetail FROM kalacloud_student;

SELECT
`id` '用户ID',
`name` '用户名称',
(
CASE `state`
WHEN 1 THEN '正常'
WHEN 2 THEN '不正常'
ELSE NULL
END
) '用户状态'
FROM
`user`

 

select
COUNT(distinct case when p.lab like '%L%' then
(case when p.bar_code like '%T%' then p.bar_code else null end)
else null end ) countBarCode
from t_plan p