MYSQL - 如何从基于聚合列的表中获取非聚合列 [英] MYSQL - How to get non aggregate columns from a table based on an aggregate column

问题描述

我需要基于聚合函数的行中的列.

如果我有一个 id 的活动列表，我可以获取每个 id 的最后一个活动的列表，列出 id、last_date 和相应的活动

<前>如果不存在，则创建表`activity_log`(`activity_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY，`id` int(255) 非空，`date_time` 时间戳 NOT NULL DEFAULT CURRENT_TIMESTAMP，`user` varchar(35) 非空，`activity` varchar(50) NOT NULL)

SELECT id, activity, max(activity_id) as ma FROM activity_log按 id 分组

返回 id 和正确的最后日期，但活动不是对应于 max(activity_id) 的活动

<前>数据示例id 活动日期_时间1 棒球 2011-8-11 足球 2011-8-92 网球 2011-7-32 曲棍球 2011-8-9回报1 棒球 2011-8-9 (我想看足球)2 网球 2011-8-9 (我想看曲棍球)

谢谢

解决方案

尝试一些类似这样的事情:

选择al.id, al.activity, al.activity_id来自活动日志Join (Select max(aa.activity_id) as ma From activity_log aa group by aa.id) As al2在 al2.ma = al2.activity_id;

诀窍是从子查询中共享 id 的每组记录的最大值中获取 activity_id，然后加入这些结果以获得另一个该特定记录的列.

I need columns from a row based on the aggregate function.

if I have a list of activities for an id, can I get a list of the last activity for each id, listing the id, the last_date AND the corresponding activity

CREATE TABLE IF NOT EXISTS `activity_log` (
  `activity_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id` int(255) NOT NULL,
  `date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user` varchar(35) NOT NULL,
  `activity` varchar(50) NOT NULL)

SELECT id, activity, max(activity_id) as ma FROM activity_log group by id

returns the id and correctly the last date, but the activity is not the one that corresponds to the max(activity_id)

data example

    id  activity   date_time
     1  baseball    2011-8-1
     1  football    2011-8-9
     2  tennis      2011-7-3
     2  hockey      2011-8-9

returns 
    1 baseball 2011-8-9 (I'd like to see football)
    2 tennis   2011-8-9 (I'd like to see hockey)

thanks

解决方案

Try something a little more like this:

Select al.id, al.activity, al.activity_id
From activty_log
Join (Select max(aa.activity_id) as ma From activity_log aa group by aa.id) As al2
 On al2.ma = al2.activity_id;

The trick is to get the activity_id from the max of each group of records that share id in a sub-query, then join on those results to get the other columns of that specific record.

这篇关于MYSQL - 如何从基于聚合列的表中获取非聚合列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！