我正在尝试使用 MySQL CASE 语句从产品表中选择产品。
我有这个:
SELECT art.a,art.b,art.c
FROM products AS art
WHERE art.inventory != "0"
# filter by param_reference_iln (seller ID = vARCHAR(13) )
CASE
WHEN param_reference_iln = '3333333333333' THEN AND art.iln = '1111111111111' OR art.iln = '2222222222222'
WHEN param_reference_iln = '8888888888888' THEN AND art.iln = '7777777777777'
ELSE AND art.iln NOT IN ('1111111111111','2222222222222','7777777777777')
END CASE;
我已经尝试了两种 CASE 变体,但我无法让它们工作。
问题:
我做错了什么?
我不确定您可以在 WHERE 子句中使用 CASE。无论如何,你应该在这里使用严格的布尔语法:
SELECT art.a,art.b,art.c
FROM products AS art
WHERE art.inventory != "0"
AND (
(param_reference_iln = '3333333333333' AND (art.iln = '1111111111111' OR art.iln = '2222222222222'))
OR
(param_reference_iln = '8888888888888' AND art.iln = '7777777777777')
OR
art.iln NOT IN ('1111111111111','2222222222222','7777777777777')
);
不能使用 CASE
动态构造 where 子句,方法是让它返回条件语句(如 THEN AND ...
并期望AND
应用于前面的条件)
您需要使用香草内联逻辑;
WHERE art.inventory != "0"
AND (
(param_reference_iln = '3333333333333' AND art.iln IN ('1111111111111', '2222222222222'))
OR
(param_reference_iln = '8888888888888' AND art.iln = '7777777777777')
OR
(art.iln NOT IN ('1111111111111','2222222222222','7777777777777'))
)
它不是用于选择,而是用于存储过程。向下滚动您发布的页面,您将看到