SQL:如何在 sql 查询中保存顺序？

Question

我有 PostgreSQL 数据库，我尝试打印我所有的用户(人)。

当我执行这个查询时

-- 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;

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

输出:3 2 4 1

当我尝试链接所有者 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等数据

你看不是顺序错了。那么我的问题是如何保存该订单？

Answer 1

使用 join 代替 subquery。试试这个。

SELECT p.*
FROM   person p
       JOIN (SELECT p.id,
                    Count(p.NAME)cnt
             FROM   car c
                    JOIN person p
                      ON c.person_id = p.id
             GROUP  BY p.id) b
         ON p.id = b.id
ORDER  BY cnt ASC