我有以下SQL查询:
SELECT w.id,w.name,m.subject,m.id
FROM users AS w,i_c AS c,(SELECT _id,u_id,subject FROM i_m WHERE (_id,tmstmp) IN
(SELECT _id,max(tmstmp) FROM i_m GROUP BY _id)) m
WHERE (c.uid_1 = '2' OR c.uid_2 = '2') AND
CASE WHEN c.uid_1 = '2' THEN w.id = c.uid_2
WHEN c.uid_2 = '2' THEN w.id = c.uid_1 END
AND (c.id = m.id) ORDER BY m.tmstmp DESC
它在我的第一台服务器上工作正常但在第二台服务器上给了我
Unexpected Token. (near "c" at position 280)
看了一些类似的问题,但仍未找到解决方案.
似乎问题是CASE.
谁能帮助我?任何帮助将非常感激.提前致谢.
最佳答案
你CASE是错的,CASE是一个表达式,而不是一个语句,你只能在THEN中指定一个值,而不是一个条件.
把它改成这个:
w.id = CASE WHEN c.uid_1 = '2' THEN c.uid_2
WHEN c.uid_2 = '2' THEN c.uid_1
END
像@Jarlh建议的那样,您可以使用可以稍微优化查询的OR:
WHERE ((c.uid_1 = '2' AND w.id = c.uid_2) OR
(c.uid_2 = '2' AND w.id = c.uid_1)) AND