从一个表中选择行，其中另一个表中的id基于第二个表id显示结果top和next结果，因为它是+ Mysql - Select rows from a table where id in another second table based on second table ids shows results top as well as next results as it is + Mysql

Select rows from a table where id in another second table based on second table ids shows results top as well as next results as it is + Mysql

我有用户表

用户

id       name          
1        test1         
2        test2
3        test3
4        test4
5        test5
6        test6
7        test7
8        test8
9        test9
10       test10

付款

id  userid  name
1     5     test5
2     3     test3
3     9     test9

我想要这样的结果

id     name
3      test3
5      test5
9      test9
1      test1
2      test2
4      test4  
6      test6
7      test7
8      test8
10     test10

我想无论谁支付的金额应该显示在最前面的顺序。

SELECT u.`id` id, u.`name` NAME 
FROM users u
INNER JOIN payment p
ON u.id=p.userid
UNION
SELECT u.`id` id, u.`name` NAME 
FROM users u

这将产生您想要的结果:

SELECT a.id, a.name, 1 AS RN  
FROM users a
INNER join payments b
ON a.id=b.userid
UNION ALL
SELECT a.id, a.name, 2 AS RN 
FROM users a
LEFT JOIN payments b
ON a.id=b.userid
WHERE b.id IS NULL
ORDER BY RN, a.id

第一个查询只是为payments表中出现的行选择id和name列。

第二个查询获取id和name列中没有出现在payments表中的行。

UNION ALL简单地将结果合并在一起。

注意查询中的1 AS RN和2 AS RN。这是我额外添加的一列，这样结果就可以按照你想要的顺序排列。您可以在处理数据时简单地忽略这一点。

您可以使用LEFT JOIN和ORDER BY:

SELECT user.id, user.name FROM user LEFT JOIN payment ON user.id = payment.userid ORDER BY payment.id DESC, user.id

这将满足您的要求，"我想要谁支付的金额，一个应该显示在最前面的顺序"。但是如果你想要的结果如你给出的结果所示:

SELECT user.id, user.name FROM user LEFT JOIN payment ON user.id = payment.userid ORDER BY CASE WHEN payment.id IS NULL THEN 1 ELSE 0 END , payment.id , user.id