ORDER BY 字段内的 MySQL 子查询.(无内连接) [英] MySQL subquery inside ORDER BY field. (without Inner Join)

问题描述

有很多与此相关的问题，但都有使用内连接的相同答案，这(我认为)在这里是不可能的.(如果我错了就说)

我现在正在做的是调用两个不同的 mysql 查询来获取结果.它完美运行.

What I am doing now is calling two different mysql query to get result. It works perfectly.

$db->query("SELECT * FROM `meta` WHERE `metakey` = 'category_order'");
$order = $db->fetch_assoc()['metavalue'];  
/*$order = 2,1,12,11,10*/

$db->query("SELECT * FROM `categories` WHERE `parent` = '0' ORDER BY field(ID, $order)");           
$cats = $db->fetch();

现在为了减少查询次数，我尝试了类似的方法，

Now to reduce number of queries I tried something like,

$db->query("SELECT * FROM `categories` WHERE `parent` = '0' ORDER BY field(ID, (SELECT 'metavalue' FROM `meta` WHERE `metakey` = 'category_order'))");

它没有显示任何错误，但它给了我错误的输出.有没有办法做到这一点?

It doesn't show any error, but it gives me wrong output. Is there any way to do this?

编辑

categories 表的结构，

categories table's structure,

_________________
| ID  | name    |
-----------------
| 1   | A       |
| 2   | B       |
| 11  | C       |
| 12  | D       |
| 10  | E       |
-----------------

元表的结构，

______________________________________
| ID | metakey        | metavalue    |
--------------------------------------
| 1  | category_order | 2,1,12,11,10 |
--------------------------------------

推荐答案

我真的看不出有什么方法可以直接使用 ORDER BY FIELD 来做到这一点.

I can't really see a way to do it directly using ORDER BY FIELD.

但是我想知道您是否可以对元表进行 JOIN，然后按 FIND_IN_SET 进行 ORDER BY.

However I wonder if you could do a JOIN against the meta table and then ORDER BY a FIND_IN_SET.

未对此进行测试，但希望能给您提供思路:-

Not tested this, but hopefully will give you the idea:-

SELECT * 
FROM `categories` 
INNER JOIN meta ON metakey = 'category_order' AND FIND_IN_SET(categories.ID,metavalue)
WHERE `parent` = '0' 
ORDER BY FIND_IN_SET(categories.ID,metavalue)

可能不需要 JOIN 上的 FIND_IN_SET

Possibly the FIND_IN_SET on the JOIN isn't necessary

这篇关于ORDER BY 字段内的 MySQL 子查询.(无内连接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！