SQL：如何在SQL查询中保存订单？ [英] SQL: How to save order in sql query?

问题描述

我有PostgreSQL数据库，并尝试打印所有用户（人）。

I have PostgreSQL database and I try to print all my users (Person).

执行此查询时

-- show owners 
-- sorted by maximum cars amount
SELECT p.id
  FROM car c JOIN person p ON c.person_id = p.id
  GROUP BY p.id
  ORDER BY COUNT(p.name) ASC;

我让所有车主按汽车数量排序

I get all owners sorted by cars amount

输出： 3 2 4 1

当我尝试链接所有者ID时，所有订单都出错了。 / p>

And all order goes wrong when I try to link owner id.

SELECT *
FROM person p
WHERE p.id IN (
  SELECT p.id
  FROM car c JOIN person p ON c.person_id = p.id
  GROUP BY p.id
  ORDER BY COUNT(p.name) ASC);

输出： 1 2 3 4 和其他数据

您看到的是订单错了。所以这是我的问题，如何保存订单？

You see than order is wrong. So here is my question how can I save that order?

推荐答案

解开混乱的局面。先汇总，然后再加入：

Untangle the mess. Aggregate first, join later:

SELECT p.*
FROM   person p
JOIN  (
   SELECT person_id, count(*) AS ct
   FROM   car
   GROUP  BY person_id
   ) c ON c.person_id = p.id
ORDER  BY c.cnt;

无需两次加入 person 。如果您计算最多或所有行，这应该是最快的。

对于少量选择，相关子查询更快：

No need to join to person twice. This should be fastest if you count most or all rows.
For a small selection, correlated subqueries are faster:

SELECT p.*
FROM   person p
ORDER  BY (SELECT count(*) FROM car c WHERE c.person_id = p.id)
WHERE  p.id BETWEEN 10 AND 20;  -- some very selective predicate

至于您的原始谓词： IN 需要<右边的strong> set ，元素的顺序被忽略，因此 ORDER BY 在子菜单中毫无意义。

As for your original: IN takes a set on the right hand, order of elements is ignored, so ORDER BY is pointless in the subuery.

这篇关于SQL：如何在SQL查询中保存订单？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！