mysql - 多个表怎样合并分组查询,并按照日期排序?
本文介绍了mysql - 多个表怎样合并分组查询,并按照日期排序?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
问 题
目前四个不同的表 单独查询求和每天的数据
请教如何合并查询四组不同数据每一天的和 首列按日期排序只取年月日
SELECT
DATE_FORMAT(createTime, '%Y-%m-%d') AS 日期,
count(*) AS 注册人次
FROM
tokenlog
WHERE createTime BETWEEN '2017-05-01' AND '2017-05-31'
GROUP BY
DATE_FORMAT(createTime, '%Y-%m-%d')
ORDER BY
createTime
===============================================
SELECT
DATE_FORMAT(logTime, '%Y-%m-%d') AS 日期,
count(*) AS 场次
FROM
sumelog
WHERE logTime BETWEEN '2017-05-01' AND '2017-05-31'
GROUP BY
DATE_FORMAT(logTime, '%Y-%m-%d')
ORDER BY
logTime
===============================================
SELECT
DATE_FORMAT(logTime, '%Y-%m-%d') AS 日期,
Sum(sumelog.consume) AS 消耗金币
FROM
sumelog
WHERE logTime BETWEEN '2017-05-01' AND '2017-05-31'
GROUP BY
DATE_FORMAT(logTime, '%Y-%m-%d')
ORDER BY
logTime
===============================================
SELECT
DATE_FORMAT(endTime, '%Y-%m-%d') AS 日期,
count(DISTINCT userId) AS 参加活动人次
FROM
game_u
WHERE endTime BETWEEN '2017-05-01' AND '2017-05-31'
GROUP BY
DATE_FORMAT(endTime, '%Y-%m-%d')
ORDER BY
endTime
要求最终显示结果如下
日期 注册人次 场次 消耗金币 参加活动人次
2017-05-01 8 2 2 8
2017-05-02 4 1 1 4
2017-05-03 16 8 8 16
2017-05-04 4 1 1 4
2017-05-05 20 10 10 20
解决方案
里外都要三次查询,组一起好了
select a.日期,a.注册人次,b.场次,b.消耗金币,c.参加活动人次 from
(SELECT
DATE_FORMAT(createTime, '%Y-%m-%d') AS 日期,
count(*) AS 注册人次
FROM tokenlog
WHERE createTime BETWEEN '2017-05-01' AND '2017-05-31'
GROUP BY DATE_FORMAT(createTime, '%Y-%m-%d') ) a join
(SELECT
DATE_FORMAT(logTime, '%Y-%m-%d') AS 日期,
count(*) AS 场次,
Sum(sumelog.consume) AS 消耗金币
FROM sumelog
WHERE logTime BETWEEN '2017-05-01' AND '2017-05-31'
GROUP BY DATE_FORMAT(logTime, '%Y-%m-%d')) b on a.日期=b.日期 join
(SELECT
DATE_FORMAT(endTime, '%Y-%m-%d') AS 日期,
count(DISTINCT userId) AS 参加活动人次
FROM game_u
WHERE endTime BETWEEN '2017-05-01' AND '2017-05-31'
GROUP BY DATE_FORMAT(endTime, '%Y-%m-%d')) c on a.日期=c.日期
ORDER BY a.日期;
这篇关于mysql - 多个表怎样合并分组查询,并按照日期排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文