如何在 Postgres 中使用带有通配符的组

我在Postgres中遇到此代码的问题。我想获取用户表中的所有字段，但只能按 id 分组。这在MySQL和SQLite上效果很好，但是在谷歌搜索之后，我发现这种行为不是标准的一部分。

SELECT u.*, p.*, count(o.id) AS order_count
FROM shop_order AS o
LEFT JOIN user AS u ON o.user_id = u.id
LEFT JOIN userprofile AS p ON p.user_id = u.id
WHERE o.shop_id = <shop_id>
GROUP BY u.id

（请注意，<shop_id>是以编程方式传递的，因此您可以轻松地将其替换为任何整数。

这是我收到的错误

column "u.id" must appear in the GROUP BY clause or be used in an aggregate function

我是SQL新手，所以如果这很明显，请保持温和:)

不幸的是，

我认为如果不列出 user 和 userprofile 中的所有列，您就无法做到这一点。（通配符的使用通常被认为是您仅用于简单的即席查询的事情，我想这是原因之一！ MySQL和其他数据库有一个"方便"的功能，如果一列既不在GROUP BY子句中，也不在聚合函数中，那么每个组中该列的任何值都可以显示在结果中。 PostgreSQL有点严格。

但通常，表中只有几列足以进行分组。有很多方法可以重写它。也许你可以做的是：

SELECT u.*, p.*, (select count(o.id) FROM shop_order AS o WHERE o.user_id = u.id AND o.shop_id = <shop_id>) AS order_count
FROM user AS u
LEFT JOIN userprofile AS p ON p.user_id = u.id

您可以使用

row_number将结果限制为每个用户一行：

select  *
from    (
        select  row_number() over (partition by u.id) as rn
        ,       *
        join    user u
        on      sub.user_id = u.id
        join    shop_order so
        on      so.user_id = u.id
        left join
                userprofile up
        on      up.user_id = u.id
        where   so.shop_id = <shop_id>
        ) as SubQueryAlias
where   rn = 1

子查询是必需的，因为 Postgres 不允许像 where 子句中的 row_number 这样的窗口函数。

您正在尝试要求数据库对一些非常不具体的东西进行计数您想计算订单，但随后您想显示所有详细信息，如果用户配置文件包含每个用户的多行并且这些行不同，您将为每个 u.id 获得多行，并且您指定您只需要一个这就是为什么数据库说"走开，告诉我你真正想要什么"

你的Postgres版本是什么？如果您使用的是 Postgres 9.0 及更低版本，则您的代码构造将不会运行。例如，这是不允许的：

SELECT
    p.id,
    p.firstname, p.lastname, p.address, -- these auxiliary fields is helpful to program users
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id

必须在 9.0 及更低版本上执行此操作：

SELECT
    p.id,
    p.firstname, p.lastname, p.address, -- these auxiliary fields is helpful to program users
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id
    ,p.firstname, p.lastname, p.address; -- these ancillary fields aids the RDBMS on preventing
    -- programmers from accidentally committing the same mistake as MySQL programmers.

如果您使用的是 Postgres 9.1，即使 SELECT 子句中有许多字段，您当然只能在 GROUP BY 中使用一个字段，只要该 GROUPed 字段是主键。因此，这将运行：

SELECT
    p.id,
    p.firstname, p.lastname, p.address, -- these auxiliary fields is helpful to program users
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id 
-- note that there's no need to repeat the SELECTed fields on GROUP BY clause

Postgres 9.1 可以促进对主键的功能依赖，因此只允许在 GROUP BY 子句中包含主键

SQL 小提琴示例：http://sqlfiddle.com/#!1/3b857/3

你使用的是哪个版本的PostgreSQL？我相信这是 9.1 之前的一个。

从版本 9.1 开始，PostgreSQL 还支持您正在寻找的功能，以防您GROUP BY主键列。所以也许升级在这里是一个不错的选择。

相关内容

最新更新

热门标签：