一道SQL题

原题:大池子博客

给定一个access_time表，它记录了用户每个月访问网站的次数，包括三个域：用户、时间、次数。注意表中可能包含用户在1月份的多条记录。
要求查询用户、月份、月累计、总共累计四项的列表。

drop table if exists access_time;
CREATE TABLE access_time (
    visitor VARCHAR(20),
    month INT,
    access_count INT
);
insert into access_time values
('A',1,5),
('A',1,15),
('B',1,5),
('A',2,3),
('A',1,8),
('B',2,10),
('A',2,4);

SELECT 
    visitor,
    month,
    SUM(access_count) AS month_sum,
    (SELECT 
            SUM(access_count)
        FROM
            access_time
        WHERE
            visitor = o.visitor AND month <= o.month) AS all_sum
FROM
    access_time AS o
GROUP BY visitor , month
ORDER BY month ASC, month_sum DESC, all_sum DESC; 

转载于:https://www.cnblogs.com/weiyinfu/p/6786507.html