mysql 杂笔

count 加条件

`count(cec.status=0 or null)`  //or null 表示不满足条件时不计算

sum加条件

SUM(IF(refundState = 1,payAmount,0))

汉字排序

select * from user order by convert(name using gbk) desc

字符串转数字

字段(纯数字)+0

同表查询修改报错 You can’t specify target table ‘base_products’ for update in FROM clause

-- 将子查询作为一张表再次查询
	UPDATE base_products
	SET productType = '丛书'
	WHERE
		id IN (
			select t.id from
			(SELECT
				a.id
			FROM
				base_products a
			INNER JOIN base_product_books bp ON a.id = bp.productId
			WHERE
			a.productType = '图书'
			AND
			bp.isSeries = 1) as t
		)

判断空

IFNULL(amount,0)

当前时间

NOW()

五周前

select WEEKOFYEAR(DATE_SUB(CURDATE(), INTERVAL 5 WEEK));
select DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 5 WEEK), '%Y年第%u周');

三十天前

select DATE_SUB(CURDATE(), INTERVAL 30 DAY);

前五周的第一天

select date_sub(DATE_SUB(CURDATE(), INTERVAL 5 WEEK),INTERVAL WEEKDAY(curdate()) + 0 DAY);

生成日期假表

前三十天

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc DAY), '%Y-%m-%d') as datameths
		FROM ( 
					SELECT @xi:=@xi+1 as xc from 
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, 
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) xc2,  
					(SELECT @xi:=0) xc0 
		) xcxc

前五周

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc WEEK), '%Y年第%u周') as datameths
		FROM ( 
					SELECT @xi:=@xi+1 as xc from 
					(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) xc1, 
					(SELECT 1 ) xc2,  
					(SELECT @xi:=0) xc0 
		) xcxc