我不确定mysql是如何工作的,有人能解释这个查询有什么问题吗?
CASE WHEN SELECT COUNT(*) from websites where website_id = 171 and master = 2 > 0
SELECT timestamp from websites where website_id = 171 and master = 2
ELSE SELECT NOW() as timestamp
查看MySQL控制流函数
案例……WHEN需要一个THEN
关键字来工作
您可能需要:
CASE
WHEN (SELECT COUNT(*) from websites where website_id = 171 and master = 2) > 0
THEN (SELECT timestamp from websites where website_id = 171 and master = 2)
ELSE NOW()
END as timestamp
如果只有两种可能,那么你最好使用If:
IF((SELECT count(*) from websites where website_id = 171 and master = 2) > 0,
(SELECT timestamp from websites where website_id = 171 and master = 2),
NOW()) AS TIMESTAMP
或者,您可以使用IFNULL并跳过count(*)
IFNULL((SELECT timestamp from websites where website_id = 171 and master = 2),
NOW()) AS TIMESTAMP
我想你可以这样重写你的查询:
SELECT COALESCE(timestamp, NOW()) from websites where website_id = 171 and master = 2
当使用嵌入选择来获取值时,您需要将选择括在括号中。同时,CASE
需要用END
闭合。
试试这个:
CASE
WHEN (SELECT COUNT(*) from websites where website_id = 171 and master = 2) > 0
THEN (SELECT timestamp from websites where website_id = 171 and master = 2)
ELSE (SELECT NOW())
END as timestamp