求教 mysql 查询语句。mysql 数据格式如下，表中 app 重复。需求：取出 一段时间内（四天内吧） 每天每个 appid 调用总量（ count 总量）最大的前两个 appid

[luefei]

logtime appid count
100018 1226461
1002495 6165611
2015-10-1 1000255 156164611
100018 4964610
1002495 16461661
1000255 46461168

100018      196494561
            1002495     416461616
            1000255     616113

2015-10-2 100018 16461131
1002495 16461613
1000255 1641351

100018      2646116
            1002495     6116161
            1000255     126461611

2015-10-3 100018 548531
1002495 54136116
1000255 496811

100018      41646161
            1002495     1644131
            1000255     61164611

2015-10-4 100018 443134
1002495 15133
1000255 1641434

[popok]

@luefei 好吧，你这个我看的晕，然后重新看了下主题，好像我理解错了

[tusj]

DROP TABLE IF EXISTS test.app_usage;
CREATE TABLE test.app_usage
(
log_time DATETIME,
app_id BIGINT,
log_count BIGINT
);

DELETE FROM test.app_usage;
INSERT INTO test.app_usage VALUES (NOW(), 123, 100);
INSERT INTO test.app_usage VALUES (NOW(), 456, 200);
INSERT INTO test.app_usage VALUES (NOW(), 789, 300);
INSERT INTO test.app_usage VALUES (NOW(), 124, 100);
INSERT INTO test.app_usage VALUES (NOW(), 234, 200);
COMMIT;

-- SELECT * FROM test.app_usage;
SELECT a.*
FROM (
SELECT concat(DATE_FORMAT(log_time, '%Y%m%d'), app_id) AS group_key, MAX(date_format(log_time, '%Y%m%d')) as log_date, MAX(app_id), SUM(log_count) AS daily_count
FROM test.app_usage
WHERE DATEDIFF(CURRENT_DATE(), log_time) < 4
GROUP BY group_key) AS a
ORDER BY a.daily_count DESC
LIMIT 0, 1;

-- DROP TABLE IF EXISTS test.app_usage;

[luefei]

@popok 恩恩。需求略复杂。。智商捉急 所以请求大牛们帮忙解决。

[luefei]

@bugsnail 谢谢指导

[luefei]

@tusj 谢谢。这个语句 里的 sum(log_count) 是四天的调用量和，不是每天的 appid 求和。

[zeayes]

USE test;
DROP TABLE `test`;
CREATE TABLE `test`(
`logtime` DATETIME NOT NULL,
`appid` VARCHAR(12) NOT NULL,
`count` INT NOT NULL DEFAULT 0
)DEFAULT CHARSET = UTF8;

INSERT INTO `test` VALUES ('2015-10-15', '10001', 100);
INSERT INTO `test` VALUES ('2015-10-15', '10001', 150);
INSERT INTO `test` VALUES ('2015-10-15', '10001', 50);
INSERT INTO `test` VALUES ('2015-10-15', '10003', 70);
INSERT INTO `test` VALUES ('2015-10-15', '10002', 420);
INSERT INTO `test` VALUES ('2015-10-15', '10002', 120);
INSERT INTO `test` VALUES ('2015-10-15', '10002', 190);
INSERT INTO `test` VALUES ('2015-10-16', '10001', 110);
INSERT INTO `test` VALUES ('2015-10-16', '10001', 130);
INSERT INTO `test` VALUES ('2015-10-16', '10001', 90);
INSERT INTO `test` VALUES ('2015-10-15', '10002', 200);
INSERT INTO `test` VALUES ('2015-10-16', '10003', 40);
INSERT INTO `test` VALUES ('2015-10-16', '10003', 80);
INSERT INTO `test` VALUES ('2015-10-16', '10003', 60);

set @num := 0, @logtime := '';
select logtime, appid, dailycount from (select a.logtime, a.appid, a.dailycount, @num := if(@logtime = date(a.logtime), @num + 1, 1) as row_number, @logtime := a.logtime from (select logtime, appid, sum(count) as dailycount from test group by logtime, appid order by logtime desc, dailycount desc) as a) as b where b.row_number < 3;


http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

[tusj]

@luefei 我给的就是按每天，每个 app_id 汇总的啊
不然你觉得 group by 拿来干什么吃的呢？
可能是你被 limit0,1 迷惑了，你把 limit 去掉就看明白了