php - 将 1 列 mysql 结果添加到另一个 mysql 结果的结果中

Question

目前我有 2 个不同的 mysql 查询:

查询 1

SELECT monthname( calendar.datefield ) AS date,
    year(calendar.datefield) as year, calendar.datefield, COUNT(all_griefs_tbl.actioned_status ) AS total_griefs,
    all_griefs_tbl.actioned_status, all_griefs_tbl.game
FROM all_griefs_tbl 
RIGHT JOIN calendar 
  ON ( DATE(all_griefs_tbl.actioned_date ) = calendar.datefield )
    AND all_griefs_tbl.actioned_status = 'accepted'
WHERE calendar.datefield
  BETWEEN DATE_ADD(CURDATE(), INTERVAL -12 MONTH)  AND CURDATE()
GROUP BY year( calendar.datefield ) DESC , month( calendar.datefield ) DESC

查询 2

SELECT monthname( calendar.datefield ) AS date, 
    year(calendar.datefield ) AS year, calendar.datefield,
    COUNT(all_griefs_tbl.actioned_status ) AS total_submitted,
    all_griefs_tbl.actioned_status, all_griefs_tbl.game 
FROM all_griefs_tbl 
RIGHT JOIN calendar 
  ON ( DATE( all_griefs_tbl.date ) = calendar.datefield ) 
WHERE calendar.datefield BETWEEN DATE_ADD( CURDATE( ) , INTERVAL -12 MONTH ) AND CURDATE( ) 
GROUP BY year( calendar.datefield ) DESC , month( calendar.datefield ) DESC

现在它们之间的区别在于查询 1 我计算的是每月接受的悲伤数，而在查询 2 中我计算的是每月提交的记录数 - 计算不同的列

我想做的是 a) 将其放入单个查询中或 b) 能够将结果合并到 1 个表中。

我希望输出如下:

    Month         Year          Total Griefs  Total Submitted
    ------------  ------------  ------------  ------------  
    April         2012          14            2      
    March         2012          0             8
    February      2012          0             6
    January       2012          0             13
    December      2011          0             7
    November      2011          0             10
    October       2011          0             0
    September     2011          0             0
    August        2011          0             6
    July          2011          0             3
    June          2011          0             2
    May           2011          0             0
    April         2011          0             0

这可能吗，还是我完全找错了树？

谢谢!

Answer 1

您可以在一个查询中执行此操作:

SELECT 
    monthname( calendar.datefield ) AS date,
    year(calendar.datefield) as year, 
    calendar.datefield,
    SUM(IF(all_griefs_tbl.actioned_status = 'accepted', 1, 0)) AS total_griefs,
    COUNT(all_griefs_tbl.actioned_status ) AS total_submitted,
    all_griefs_tbl.actioned_status, 
    all_griefs_tbl.game 
FROM 
    all_griefs_tbl 
RIGHT JOIN 
    calendar ON ( DATE(all_griefs_tbl.actioned_date ) = calendar.datefield ) 
WHERE 
    calendar.datefield BETWEEN DATE_ADD(CURDATE(), INTERVAL -12 MONTH)  AND CURDATE() 
GROUP BY year( calendar.datefield ) DESC , 
    month( calendar.datefield ) DESC

编辑:这有点不正统，但它(很可能)会为您提供您想要的东西:

SELECT
    month(date_add(curdate(), interval seq.mm months)),
    year(date_add(curdate(), interval seq.mm months)),
    (SELECT count(1)
       FROM all_griefs_tbl
      WHERE actioned_status = 'accepted'
        AND month(action_date) = month(date_add(curdate(), interval seq.mm months))
        AND year(action_date) = year(date_add(curdate(), interval seq.mm months))) as total_griefs
    (SELECT count(1)
       FROM all_griefs_tbl
        AND month(date) = month(date_add(curdate(), interval seq.mm months))
        AND year(date) = year(date_add(curdate(), interval seq.mm months))) as submitted_griefs
FROM
    (SELECT -12 as mm UNION SELECT -11  as mmUNION SELECT -10  as mmUNION SELECT -9  as mm
     UNION SELECT -8 as mm UNION SELECT -7  as mm UNION SELECT -6 as mm UNION SELECT -5 as mm
     UNION SELECT -4 as mm UNION SELECT -3 as mm UNION SELECT -2 as mm UNION SELECT -1 as mm) seq
ORDER BY
    mm