Mysql按if条件排序 - 错误的顺序(Mysql order by if condition - wrong order)
我有一张简单的桌子:
CREATE TABLE `dev_a4a`.`test` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `type` VARCHAR(45) NOT NULL , `priority` INT NOT NULL , PRIMARY KEY (`id`) );以下行:
id|type |priority 1 |long |8 2 |long |3 3 |short|9 4 |short|1我想获得按条件排序的行:
SELECT (RAND() * priority) as prio, type, priority FROM test ORDER BY (IF(type = 'short', '2', prio)) DESC, id DESC结果我获得了不按if条件排序的行。 每次看起来都是随机的。 这是可能的结果之一:
prio | type | priority '0.05013570194145264', 'long', '8' '2.9015473750434326', 'long', '3' '0.320064320527077', 'short', '1' '7.598900996706356', 'short', '9'我究竟做错了什么?
预期结果:
prio | type | priority '2.9015473750434326', 'long', '3' <- order by prio '7.598900996706356', 'short', '9' <- order by common value 2 '0.320064320527077', 'short', '1' <- order by common value 2 '0.05013570194145264', 'long', '8' <- order by prioI got a simple table:
CREATE TABLE `dev_a4a`.`test` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `type` VARCHAR(45) NOT NULL , `priority` INT NOT NULL , PRIMARY KEY (`id`) );with following rows:
id|type |priority 1 |long |8 2 |long |3 3 |short|9 4 |short|1I want to get rows ordered by condition:
SELECT (RAND() * priority) as prio, type, priority FROM test ORDER BY (IF(type = 'short', '2', prio)) DESC, id DESCAs a result I got rows not ordered by if condition. Every time it looks like it's random. Here's one of possible results:
prio | type | priority '0.05013570194145264', 'long', '8' '2.9015473750434326', 'long', '3' '0.320064320527077', 'short', '1' '7.598900996706356', 'short', '9'What am I doing wrong?
Expected result:
prio | type | priority '2.9015473750434326', 'long', '3' <- order by prio '7.598900996706356', 'short', '9' <- order by common value 2 '0.320064320527077', 'short', '1' <- order by common value 2 '0.05013570194145264', 'long', '8' <- order by prio最满意答案
那这个呢
SELECT (RAND() * priority) as prio, type, priority ,CASE `type` WHEN 'short' THEN 2 WHEN 'long' THEN 1 END AS t FROM test ORDER BY 3 DESC, id DESCOk I resolved that issue:
SELECT CASE WHEN type = 'short' THEN 2 ELSE RAND() * priority END AS prio, type, priority FROM test ORDER BY prio DESC, id DESC更多推荐
发布评论