非聚合列、SUM、MAX 和 GROUP BY

debugcn 发表于 Dev

科菲黑色

我有以下活动表。

CREATE TABLE IF NOT EXISTS activity(  
user_id int(11) unsigned NOT NULL,  
create_date date NOT NULL,  
start_time int(4) NOT NULL,
field_1 int(4),
field_2 int(4),  
field_3 int(4),
PRIMARY KEY (user_id, create_date, start_time)  
);

insert into activity (user_id, create_date,start_time, field_1, field_2, field_3) values  
(1, '2017-03-01', 0, 1, 1, 1),  
(1, '2017-03-01', 30, 2, 2, 2),  
(1, '2017-03-01', 60, 3, 3, 3),  
(1, '2017-03-02', 0, 4, 4, 4),  
(1, '2017-03-02', 30, 5, 5, 5),  
(1, '2017-03-02', 60, 6, 6, 6),  
(1, '2017-03-03', 0, 7, 7, 7),  
(1, '2017-03-03', 30, 8, 8, 8),  
(1, '2017-03-03', 60, 9, 9, 9),    
(2, '2017-03-04', 0, 1, 1, 1),  
(2, '2017-03-04', 30, 2, 2, 2),  
(2, '2017-03-04', 60, 3, 3, 3),  
(2, '2017-03-05', 0, 4, 4, 4),  
(2, '2017-03-05', 30, 5, 5, 5),  
(2, '2017-03-05', 60, 6, 6, 6),  
(2, '2017-03-06', 0, 7, 7, 7),  
(2, '2017-03-06', 30, 8, 8, 8),  
(2, '2017-03-06', 60, 9, 9, 9)

从活动中选择*；

| user_id | create_date | start_time | field_1 | field_2 | field_3 |    
|       1 | 2017-03-01  |          0 |       1 |       1 |       1 |  
|       1 | 2017-03-01  |         30 |       2 |       2 |       2 |  
|       1 | 2017-03-01  |         60 |       3 |       3 |       3 |  
|       1 | 2017-03-02  |          0 |       4 |       4 |       4 |  
|       1 | 2017-03-02  |         30 |       5 |       5 |       5 |  
|       1 | 2017-03-02  |         60 |       6 |       6 |       6 |  
|       1 | 2017-03-03  |          0 |       7 |       7 |       7 |  
|       1 | 2017-03-03  |         30 |       8 |       8 |       8 |  
|       1 | 2017-03-03  |         60 |       9 |       9 |       9 | 
|       2 | 2017-03-04  |          0 |       1 |       1 |       1 |  
|       2 | 2017-03-04  |         30 |       2 |       2 |       2 |  
|       2 | 2017-03-04  |         60 |       3 |       3 |       3 |  
|       2 | 2017-03-05  |          0 |       4 |       4 |       4 |  
|       2 | 2017-03-05  |         30 |       5 |       5 |       5 |  
|       2 | 2017-03-05  |         60 |       6 |       6 |       6 |  
|       2 | 2017-03-06  |          0 |       7 |       7 |       7 |  
|       2 | 2017-03-06  |         30 |       8 |       8 |       8 |  
|       2 | 2017-03-06  |         60 |       9 |       9 |       9 |

我想要的是每个user_id，field_1、field_2、field_3的总和最大的日期。在上面的例子中，查询结果应该是

| user_id | create_date | best_active_time |  
|       1 | 2017-03-03  | 72               |  
|       2 | 2017-03-06  | 72               |

该问题与获取非聚合列 (create_date) 和使用 GROUP BY 子句有关。目前，我有我的解决方案，我只是想知道是否存在更好的解决方案（我觉得它们存在）。

SELECT bb.user_id, bb.create_date, cc.best_active_time FROM   
(  
SELECT user_id, create_date, MAX(active_time) best_active_time FROM  
(SELECT user_id, create_date, (SUM(field_1) + SUM(field_2) + SUM(field_3)) as   active_time  FROM activity GROUP BY user_id, create_date) ACT GROUP BY user_id, create_date  
) bb  
INNER JOIN  
(  
SELECT user_id,  MAX(active_time) best_active_time FROM  
(SELECT user_id, (SUM(field_1) + SUM(field_2) + SUM(field_3)) as active_time    FROM activity GROUP BY user_id, create_date) ACT GROUP BY user_id  
) cc ON bb.user_id = cc.user_id AND bb.best_active_time = cc.best_active_time

Juan Carlos Oropeza 占位符图像

row_number()在mysql中可以使用变量进行模拟。

SQL 演示

SELECT *
FROM (
        SELECT a.*,
               @rn := if(@user = user_id, 
                         @rn + 1 ,
                         if(@user := user_id,1,1)
                        ) as rn
        FROM (        
                SELECT user_id, create_date, SUM(field_1 + field_2 + field_3) as total
                FROM activity a
                GROUP BY user_id, create_date
             ) a
        CROSS JOIN (SELECT @user := 0, @rn := 0) as t
        ORDER BY user_id, total DESC
    ) t
WHERE rn =1

输出：