查询ORDER BY从另一个SELECT返回的行数 [英] Query to ORDER BY the number of rows returned from another SELECT

问题描述

我正在努力把注意力集中在SQL上，我需要一些帮助来弄清楚如何在PostgreSQL 9.3中执行以下查询.

I'm trying to wrap my head around SQL and I need some help figuring out how to do the following query in PostgreSQL 9.3.

我有一个 users 表和一个 friends 表，该表在多行中列出了用户ID和朋友的用户ID.

I have a users table, and a friends table that lists user IDs and the user IDs of friends in multiple rows.

我想查询用户表，并按用户ID共有的共同朋友的数量进行ORDER BY.

I would like to query the user table, and ORDER BY the number of mutual friends in common to a user ID.

因此，朋友表如下所示:

user_id | friend_user_id
1       | 4
1       | 5
2       | 10
3       | 7

依此类推，因此用户1将4和5列为朋友，用户2将10列为朋友，因此我想按friend_user_id中用户1的最高计数来排序user_id中的结果选择.

And so on, so user 1 lists 4 and 5 as friends, and user 2 lists 10 as a friend, so I want to sort by the highest count of user 1 in friend_user_id for the result of user_id in the select.

推荐答案

Postgres方法:

The Postgres way to do this:

SELECT *
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS friends
   FROM   friends
   ) f USING (user_id)
ORDER  BY f.friends DESC NULLS LAST, user_id  -- as tiebreaker

• 关键字AS只是表别名的噪音.但是不要从列别名中忽略它.关于省略AS关键字" 的手册:

  • The keyword AS is just noise for table aliases. But don't omit it from column aliases. The manual on "Omitting the AS Key Word":

    在FROM项目中，标准和PostgreSQL都允许省略AS 在别名之前，该别名是未保留的关键字.但这不切实际 由于语法上的歧义而将其用于输出列名称.

    In FROM items, both the standard and PostgreSQL allow AS to be omitted before an alias that is an unreserved keyword. But this is impractical for output column names, because of syntactic ambiguities.

    强调粗体.

    ISNULL()是MySQL或SQL Server的自定义扩展. Postgres使用SQL标准函数 COALESCE() .但是您在这里都不需要.请使用NULLS LAST子句，它更快，更干净.参见:

    ISNULL() is a custom extension of MySQL or SQL Server. Postgres uses the SQL-standard function COALESCE(). But you don't need either here. Use the NULLS LAST clause instead, which is faster and cleaner. See:

    • PostgreSQL按日期时间asc排序，是否为null?

    多个用户将拥有相同数量的朋友.这些同伴将被任意排序.重复执行可能会产生不同的排序顺序，这通常是不希望的.将更多表达式添加为ORDER BY作为平局.最终，主键解决了所有剩余的歧义.

    Multiple users will have the same number of friends. These peers would be sorted arbitrarily. Repeated execution might yield different sort order, which is typically not desirable. Add more expressions to ORDER BY as tiebreaker. Ultimately, the primary key resolves any remaining ambiguity.

    如果两个表共享相同的列名user_id(应该如此)，则可以在join子句中使用语法快捷方式USING.另一个标准的SQL功能.受欢迎的副作用:user_id在SELECT *的输出中仅列出一次，而不是与ON联接时.许多客户端甚至都不会在输出中接受重复的列名.

    If the two tables share the same column name user_id (like they should) you can use the syntax shortcut USING in the join clause. Another standard SQL feature. Welcome side effect: user_id is only listed once in the output for SELECT *, as opposed to when joining with ON. Many clients wouldn't even accept duplicate column names in the output.

    这篇关于查询ORDER BY从另一个SELECT返回的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！