mysql - 替代 ORDER BY 子查询

Question

来自 http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_orderby2 的数据库，例如(单击“运行SQL”)，我想在来自美国的所有客户列表中首先列出具有大于80的最大CustomerID的客户。所以我用

SELECT * FROM Customers
WHERE Country = 'USA'
ORDER BY CustomerID = (SELECT MAX(CustomerID) FROM Customers 
    WHERE CustomerID > 80 AND Country = 'USA') DESC, PostalCode;

但这不是我使用的真实查询。如果查询的 SELECT...FROM...WHERE... 部分更复杂，那么什么是更优雅的查询？

我试图修改的实际查询是

SELECT post.postid, post.visible, post.userid, post.parentid, post.vote_count
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid = $threadid
AND post.visible IN (1" . (!empty($deljoin) ? ", 2" : "") . 
    ($show['approvepost'] ? ", 0" : "") . ")
ORDER BY post.postid = {$threadinfo['firstpostid']} DESC, post.vote_count > 5 DESC, 
    post.dateline $postorder

我试图用大于 5 的最大 post.vote_count 替换 post.vote_count > 5 DESC 部分。所以我使用:

SELECT post.postid, post.visible, post.userid, post.parentid, post.vote_count
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid = $threadid
AND post.visible IN (1" . (!empty($deljoin) ? ", 2" : "") . 
    ($show['approvepost'] ? ", 0" : "") . ")
ORDER BY post.postid = {$threadinfo['firstpostid']} DESC, post.vote_count = (
    SELECT MAX(post.vote_count)
    FROM " . TABLE_PREFIX . "post AS post
    WHERE post.threadid = $threadid
    AND post.visible IN (1" . (!empty($deljoin) ? ", 2" : "") . 
        ($show['approvepost'] ? ", 0" : "") . ")
    AND post.vote_count > 5
)
DESC, post.dateline $postorder

一切都很好。但是你可以想象一个更复杂的查询，也许使用 INNER JOIN，其 SELECT...FROM...WHERE...等都必须在子查询中重复。

所以我怀疑，我的问题是，您能否对查询结果进行排序，以便第一个项目(在这些结果中)具有字段的最大值，而其余的则以其他方式排序，而无需重写整个在子查询中查询？

Answer 1

MySQL 不支持 CTE，这本来是简化查询的完美解决方案。它们可以通过一个 View 来模拟:

CREATE VIEW c AS (SELECT Customer.* FROM Customer WHERE Country = "USA");
SELECT c.* FROM c ORDER BY CustomerID = (SELECT MAX(CustomerID) FROM c) DESC;
DROP VIEW c;

在您的情况下，这将给出:

CREATE VIEW p AS (
    SELECT post.postid, post.visible, post.userid, post.parentid, post.vote_count
    FROM " . TABLE_PREFIX . "post AS post
    WHERE post.threadid = $threadid
    AND post.visible IN (1" . (!empty($deljoin) ? ", 2" : "") . 
        ($show['approvepost'] ? ", 0" : "") . ")
);
SELECT p.* FROM p
ORDER BY p.postid = {$threadinfo['firstpostid']} DESC, p.vote_count = (
    SELECT MAX(p.vote_count)
    FROM p
    WHERE p.vote_count > 5
)
DESC, post.dateline $postorder;
DROP VIEW p;