php - 根据 LastModified 列比较两个表？

Question

我正在使用此查询按月份和年份检查门票组

SELECT 
    MONTH((lastmodified)) as month, 
    YEAR((lastmodified)) as year,
    COUNT(num_of_tickets) as tickets
    FROM visitors_2013
    GROUP BY 
    MONTH((lastmodified)), 
    YEAR((lastmodified))

这会返回类似的内容

Array
(
    [0] => Array
        (
            [month] => 1
            [year] => 2013
            [tickets] => 1521
        )

    [1] => Array
        (
            [month] => 2
            [year] => 2013
            [tickets] => 513
        )

    [2] => Array
        (
            [month] => 12
            [year] => 2012
            [tickets] => 146
        )

)

我已将其放入 php 函数中，以便我可以获得每年的数据(数据每年存储在单独的表中)。我在 2013 年和 2014 年调用函数 get_ticket_per_year($year) 两次

Array
(
    [0] => Array
        (
            [month] => 1
            [year] => 2013
            [tickets] => 1521
        )

    [1] => Array
        (
            [month] => 2
            [year] => 2013
            [tickets] => 513
        )

    [2] => Array
        (
            [month] => 12
            [year] => 2012
            [tickets] => 146
        )

)

Array
(
    [0] => Array
        (
            [month] => 12
            [year] => 2013
            [tickets] => 26
        )

)

我喜欢通过在图表中绘制每年每月的数据来比较另一个表(visitors_2014)中的月年总票数。为此，我使用 morris js 库。这就是我需要的结果

data: [ {
            m: 'dec',
            2012: 146,
            2013: 26
        }, {
            m: 'jan',
            2013: 1521,
            2014: 
        }],

那么我如何组合 mysql 查询，使其成为一个查询

SELECT 
prevTbl.MONTH((lastmodified)) as prev_month, 
prevTbl.YEAR((lastmodified)) as prev_year,
prevTbl.COUNT(num_of_tickets) as prev_tickets,
currTbl.MONTH((lastmodified)) as curr_month, 
currTbl.YEAR((lastmodified)) as curr_year,
currTbl.COUNT(num_of_tickets) as curr_tickets
FROM visitors_2013 prevTbl, visitors_2014 currTbl 
GROUP BY 
MONTH((lastmodified)), 
YEAR((lastmodified))

Answer 1

加入您的查询:

SELECT * FROM ( [your first query here] ) AS data1
LEFT JOIN ( [your second query here] ) AS data2
ON [your relation between the two query here]

根据您的情况(未测试):

SELECT * FROM (

    SELECT
        `num_of_tickets` AS `tickets2013`,
        MONTH(`lastmodified`) AS `month`
    FROM `visitors_2013`
    GROUP BY 
        MONTH(`lastmodified`)

) AS `data2013`
LEFT JOIN (

    SELECT
        `num_of_tickets` AS `tickets2014`,
        MONTH(`lastmodified`) AS `month`
    FROM `visitors_2014`
    GROUP BY 
        MONTH(`lastmodified`)

) AS `data2014`
ON (
    `data2013`.`month`=`data2014`.`month`
)

为什么不将(经常使用的)数据存储在同一个表中(更简单)？