mysql - MYSQL查询可将两个表的count(*)相除，并按月连接数年

我想编写一个SQL查询来划分两个表的计数，然后将它们联接起来以显示关于月份和年份的月份。

我编写了一个查询来对两个表的计数进行除法，但是我不确定如何组合两个表的日期字段，从而出现了月数与除法计数。

 select
    (a.count_one / b.count_two) as final_count,
    a.Months,
    b.Months
from
    (
        select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
        from first_table
        GROUP BY str_to_date(concat(date_format(`first_table `.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
    ) a,
    (
        select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
        from second_table
        GROUP BY str_to_date(concat(date_format(`second_table`.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
    ) b

期望的输出：

**Month and Year**    **Final_Count**
January 2016              126
February 2016             123
March 2016                 45
....                      ....
...                       ....
...                       ....

最佳答案

您需要加入条件：

select
    (a.count_one / b.count_two) as final_count,
    a.Months
from
    (
        select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
        from first_table
        GROUP BY Months)
    ) a,
    (
        select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
        from second_table
        GROUP BY Montns)
    ) b
WHERE a.Months = b.Months

最好学习编写ANSI JOIN：

select
    (a.count_one / b.count_two) as final_count,
    a.Months
from
    (
        select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
        from first_table
        GROUP BY Months)
    ) a
INNER JOIN
    (
        select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
        from second_table
        GROUP BY Months)
    ) b
ON a.Months = b.Months