示例存储过程:
DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN yr_order tinyint(1))
BEGIN
SELECT *
FROM offices
WHERE country LIKE 'A';
ORDER BY
CASE yr_order
WHEN 1 THEN updated
WHEN 2 THEN likes
ELSE 1 END
ASC
END //
DELIMITER ;
yr_order
是一个存储过程参数。当ASC
放在条件之后时,查询很好:
CASE yr_order
WHEN 1 THEN updated
WHEN 2 THEN likes
ELSE 1 END
ASC
我可以像这样为每个条件设置不同的排序顺序吗:
CASE yr_order
WHEN 1 THEN updated ASC
WHEN 2 THEN likes ASC
WHEN 3 THEN updated DESC
WHEN 4 THEN likes DESC
ELSE 1 END
我遇到了语法错误。有没有办法对每个条件使用不同的排序?
不,你不能,但你可以做这样的事情:
CASE yr_order
WHEN 1 THEN updated
WHEN 2 THEN likes
WHEN 3 THEN -updated
WHEN 4 THEN -likes
ELSE 1 END
ASC
假设更新和喜欢是数字。如果不是,您可以执行以下操作:
ORDER BY
CASE yr_order
WHEN 1 THEN updated
WHEN 2 THEN likes
ELSE 1 END
ASC,
CASE yr_order
WHEN 3 THEN updated
WHEN 4 THEN likes
ELSE 1 END
DESC