`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
)
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