mysql - How to merge group queries on multiple tables and sort them by date?
为情所困
为情所困 2017-05-16 13:28:13
0
1
1097

Currently, four different tables are queried separately to sum up the data of each day.
Please tell me how to merge and query the sum of each day in four different sets of data. The first column is sorted by date and only takes the year, month and day.

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

Requires the final display result to be as follows
Date Registered Number of Events Consumption of Gold Coins Number of Participants
2017-05-01 8  2 2  8
2017-05-02 4 1 1 1  4
2017-05-03 16 8 8 16
2017-05-04 4 1 1 4
2017-05-05 20 10 10 20

为情所困
为情所困

reply all(1)
巴扎黑

mysql Note that the table structure and type are not changed

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template