求教 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

[luefei]

。。。这个格式。。好蛋疼。。

[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

[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 这样的格式 讲究下吧，各位大牛求指导。

[herojaxy]

这排版...没看懂啥意思

[luefei]

@herojaxy 额 数据格式三列 日期 appid 调用量， 结果 发出去后 日期那一列就合进去了。第一次发问。不知道怎么发图片。尝试了两次 这个格式都没调整对。

[bugsnail]

表名，字段名，必须
数据一行就可以了，只是为了形式
关开格式的问题，不单单只能用空格的，你可以把空格替换成--再发上来都可以.....

最后，自行 google V2EX 发图 ....

[HanSonJ]

select count(appid) from table where time > 4days group by appid limit 2 order by count(appid) desc
这样取出最大两个的总数,大概就这意思，不知道符不符合

[luefei]

@bugsnail 谢谢。我重新整理下

[luefei]

@HanSonJ 这样是没有调用总量的。调用总量 还要求和。

[luefei]

@bugsnail
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

[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 你还是直接图片随便上传一个地方，贴图片地址吧

[iyaozhen]

取 4 天？每天的调用量最大的 2 个？

[popok]

select sum(count) from 表名 group by appid where logtime>2015-10-20 and logtime<2015-10-24 limit 0,2

我猜是这样，没测试过

[popok]

select sum(count) from 表名 where logtime>2015-10-20 and logtime<2015-10-24 group by appid order by sum(count) desc limit 0,2

[luefei]

@popok 恩恩。我看下重发个问题吧。这个第一次提问，搞砸了。。

[popok]

@luefei 你的问题描述其实很简单，直接一个表头就说明白了，不需要截图了

[popok]

select appid,sum(count) from 表名 where logtime>2015-10-20 and logtime<2015-10-24 group by appid order by 2 desc limit 0,2

这个应该就是结果了

[bugsnail]

结构不知道是不是这样...

sql:

```
select login_date,appid,count
from t_name
where UNIX_TIMESTAMP('2015-10-22') BETWEEN UNIX_TIMESTAMP(CURDATE())-3600*24*4 and UNIX_TIMESTAMP(CURDATE())
group by login_date,appid
order by login_date,count desc
```
那个 22 号改为当前日期可以查看过去 4 天的，那个分组前两条不会了，请大神....

[luefei]

@popok 恩恩。算是比较复杂了。我写出来的语句 太复杂 效率太低 用不了。。所以还是请教帮忙，技术水平有限。 select a.* from (select logTime, appid, sum(count) as dailycount from table where datediff(current_date(), logTime) < 4 group by logTime, appid order by logTime, dailycount desc) as a where 2 > (select count(*) from (select logTime, appid, sum(count) as b from table where datediff(current_date(), logTime) group by logTime, appid order by logTime, dailycount desc) as b where a.logTime = b.logTime and b.dailycount > a.dailycount);

[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 去掉就看明白了