case用法

SELECT
id,
service_id,
order_sn,
yuyue_date,
yuyue_time,
(
CASE
WHEN ( yuyue_date != '' && yuyue_time != '' ) THEN
UNIX_TIMESTAMP( CONCAT( yuyue_date, ' ', yuyue_time ) )
WHEN ( yuyue_date = '' && yuyue_time != '' ) THEN
addtime
WHEN ( yuyue_date = '' && yuyue_time = '' ) THEN
0
END
) AS yuyue_finnal
FROM
`order_info`
WHERE
token = '7673d6eb1ec6122c'
AND uid = 5789729
AND (
CASE
WHEN ( yuyue_date != '' && yuyue_time != '' ) THEN
UNIX_TIMESTAMP( CONCAT( yuyue_date, ' ', yuyue_time ) )
WHEN ( yuyue_date = '' && yuyue_time != '' ) THEN
addtime
WHEN ( yuyue_date = '' && yuyue_time = '' ) THEN
0
END
) BETWEEN 1611072000
AND 1611504000

 

 

 

 

 

--------------------------------------------------------------

 

 

SELECT
sum(case when x.have_renew = 1 then 1 else 0 end) AS 未续费未到期,
sum(case when x.have_renew = 2 then 1 else 0 end) AS 已续费未到期,
sum(case when x.have_renew = 3 then 1 else 0 end) AS 曾经续费已到期,
sum(case when x.have_renew = 4 then 1 else 0 end) AS 未续费已到期
FROM
(SELECT
MAX(end_time) AS end_time,

CASE
WHEN (COUNT(id) > 1 && MAX(end_time) > 1608277794) THEN 1
WHEN (COUNT(id) = 1 && MAX(end_time) > 1608277794) THEN 2
WHEN (COUNT(id) > 1 && MAX(end_time) < 1608277794) THEN 3
WHEN (COUNT(id) = 1 && MAX(end_time) < 1608277794) THEN 4
END
AS have_renew
FROM
addons_pay
WHERE
module = 'Shop'
AND pay_type != 3
GROUP BY
public_id) as x