多个表怎样合并分组查询，并按照日期排序？

Question

目前四个不同的表 单独查询求和每天的数据
请教如何合并查询四组不同数据每一天的和 首列按日期排序只取年月日

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

Answer 1

里外都要三次查询，组一起好了

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.日期;

Answer 2

WITH a 
     AS (SELECT Date_format(createtime, '%Y-%m-%d') AS 日期, 
                Count(*)                            AS 注册人次 
         FROM   tokenlog 
         WHERE  createtime BETWEEN '2017-05-01' AND '2017-05-31'), 
     b 
     AS (SELECT Date_format(logtime, '%Y-%m-%d') AS 日期, 
                Count(*)                         AS 场次 
         FROM   sumelog 
         WHERE  logtime BETWEEN '2017-05-01' AND '2017-05-31'), 
     c 
     AS (SELECT Date_format(logtime, '%Y-%m-%d') AS 日期, 
                Sum(sumelog.consume)             AS 消耗金币 
         FROM   sumelog 
         WHERE  logtime BETWEEN '2017-05-01' AND '2017-05-31'), 
     d 
     AS (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') 
SELECT 日期, 
       注册人次, 
       场次, 
       消耗金币, 
       参加活动人次 
FROM   a 
       INNER JOIN b 
               ON a.日期 = b.日期 
       INNER JOIN c 
               ON a.日期 = b.日期 
GROUP  BY 日期 
ORDER  BY 日期 ASC 

Answer 3

-- mysql分组排序:取每组时间最大的记录
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id int(11) DEFAULT NULL,
type varchar(255) DEFAULT NULL,
time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO test VALUES ('1', '1', '2017-06-26 11:19:21');
INSERT INTO test VALUES ('2', '1', '2017-06-28 11:19:21');
INSERT INTO test VALUES ('3', '1', '2017-06-27 11:19:23');
INSERT INTO test VALUES ('4', '2', '2017-06-29 11:27:17');

-- ----------------------------
-- 方式1
-- ----------------------------

SELECT
    t1.*
FROM
    test t1
INNER JOIN (
    SELECT
        type,
        MAX(time) time
    FROM
        test
    GROUP BY
        type
) t2 ON t1.type = t2.type
AND t1.time = t2.time
ORDER BY
    t2.time desc;

-- ----------------------------
-- 方式2,在mysql5.7以后版本不行
-- ----------------------------

SELECT
    *
FROM
    (
        SELECT
            *
        FROM
            test
        ORDER BY
            time DESC
    ) t
GROUP BY
    t.type;

-- ----------------------------
-- 查看mysql版本
-- ----------------------------

SELECT
    version();

-- ----------------------------
-- 最后补充一句,我们取的是每组中时间最大的记录
-- ----------------------------

Answer 4

mysql 注意是在不改表结构和类型的情况下