mysql - 如何将 DESC 添加到此查询？

Question

我正在做一个项目，我正在使用 MYSQL，我只需要让它下降。我该如何执行此操作，因为我执行此操作的常用方法不起作用。

SELECT * FROM posts 
LEFT JOIN members ON posts.user_id = members.user_id 
UNION 
SELECT * FROM posts 
RIGHT JOIN members ON posts.user_id = members.user_id 
WHERE posts.user_id IS NOT NULL

这是我尝试过的

SELECT * FROM posts
  LEFT JOIN members
    ON posts.user_id = members.user_id
UNION
SELECT *
  FROM posts
  RIGHT JOIN members
    ON posts.user_id = members.user_id
  WHERE posts.user_id IS NOT NULL AND
  ORDER BY posts.user_id DESC

Answer 1

如果您需要所有成员有或没有帖子，则反转表关系，以便帖子与成员保持连接:

SELECT * 
FROM members 
INNER JOIN posts ON members.user_id = posts.user_id
ORDER BY members.user_id DESC, posts.id ASC ## I am guessing some column names

如果你有没有帖子的成员和没有成员的帖子，那么你想要相当于“完全外部连接”的东西，这确实需要一个 UNION ......虽然我严重怀疑这里是否需要这个，但为了完整性我把它包括在内:

 SELECT * ## MUST choose the columns!!
 FROM (
    SELECT posts.*, members.* ## MUST choose the columns!!
    FROM members 
    LEFT JOIN posts ON members.user_id = posts.user_id
    UNION
    SELECT  posts.*, members.* ## MUST choose the columns!!
    FROM posts 
    LEFT JOIN members ON posts.user_id = members.user_id 
    ) d
ORDER BY user_id DESC, posts_id ASC ## I am guessing some column names

----

如果您只需要具有关联 user_id 的帖子，那么我建议您试试这个:

SELECT * 
FROM posts 
INNER JOIN members ON posts.user_id = members.user_id 
ORDER BY members.user_id DESC, posts.id ASC ## I am guessing some column names

如果您确实需要没有 user_id 的帖子，那么建议您试试这个:

SELECT * 
FROM posts 
LEFT JOIN members ON posts.user_id = members.user_id 
ORDER BY ISNULL(members.user_id) ASC, members.user_id DESC

初始查询的第二部分不会向最终结果添加更多行。考虑以下测试:

CREATE TABLE members
    (`user_id` int);

INSERT INTO members
    (`user_id`)
VALUES
    (1);

CREATE TABLE posts
    (`id` int, `user_id` int);

INSERT INTO posts
    (`id`, `user_id`)
VALUES
    (1, 1),
    (2, NULL),
    (3, NULL);

SELECT * FROM posts 
LEFT JOIN members ON posts.user_id = members.user_id;

id | user_id | user_id-: | ------: | ------: 1 |       1 |       1 2 |    null |    null 3 |    null |    null

SELECT * FROM posts 
RIGHT JOIN members ON posts.user_id = members.user_id 
WHERE posts.user_id IS NOT NULL;

id | user_id | user_id-: | ------: | ------: 1 |       1 |       1

SELECT * 
FROM posts 
LEFT JOIN members ON posts.user_id = members.user_id 
ORDER BY ISNULL(members.user_id) ASC, members.user_id DESC;

id | user_id | user_id-: | ------: | ------: 1 |       1 |       1 2 |    null |    null 3 |    null |    null

dbfiddle here