mysql - 如何在不触发 "Mixing of GROUP columns [...] with no GROUP columns is illegal if there is no GROUP BY clause"的情况下获取最小列值？

Question

我有一个带有时间戳字段“bar”的表“foo”。如何仅获取查询的最旧时间戳，例如: SELECT foo.bar from foo？我尝试执行以下操作: SELECT MIN(foo.bar) from foo 但因此错误而失败

第 1 行出现错误 1140 (42000):如果没有 GROUP BY 子句，则将 GROUP 列 (MIN(),MAX(),COUNT(),...) 与没有 GROUP 列的混合是非法的

好的，所以我的查询比这复杂得多，这就是为什么我很难处理它。这是带有 MIN(a.timestamp) 的查询:

select distinct a.user_id as 'User ID',
       a.project_id as 'Remix Project Id',
       prjs.based_on_pid as 'Original Project ID',
       (case when f.reasons is NULL then 'N' else 'Y' end)
         as 'Flagged Y or N',
       f.reasons, f.timestamp, MIN(a.timestamp) 
from view_stats a
     join (select id, based_on_pid, user_id
                    from projects p) prjs on
     (a.project_id = prjs.id)
     left outer join flaggers f on
     (    f.project_id = a.project_id
      and f.user_id = a.user_id)
where a.project_id in
(select distinct b.id
   from projects b
  where b.based_on_pid in
                ( select distinct c.id
                    from projects c
                   where c.user_id = a.user_id
                )
)
order by f.reasons desc, a.user_id, a.project_id;

任何帮助将不胜感激。

view_stats 表:

+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment | 
| user_id    | int(10) unsigned | NO   | MUL | 0                 |                | 
| project_id | int(10) unsigned | NO   | MUL | 0                 |                | 
| ipaddress  | bigint(20)       | YES  | MUL | NULL              |                | 
| timestamp  | timestamp        | NO   |     | CURRENT_TIMESTAMP |                | 
+------------+------------------+------+-----+-------------------+----------------+

Answer 1

如果您要使用聚合函数(例如 min()、max()、avg() 等)，您需要告诉数据库它需要获取 min() 的具体内容。

transaction    date
one            8/4/09
one            8/5/09
one            8/6/09
two            8/1/09
two            8/3/09
three          8/4/09

我假设您想要以下内容。

transaction    date
one            8/4/09
two            8/1/09
three          8/4/09

然后，您可以使用以下查询...注意 group by 子句，它告诉数据库如何对数据进行分组并获取某些内容的 min()。

select
    transaction,
    min(date)
from
    table
group by
    transaction