时间戳

首页 > 解决方案 > 将两个查询合并到一个查询中

mysql - 将两个查询合并到一个查询中

问题描述

我有这个图表代码并且工作得很好。如何将这两个单独的查询合并为一个查询？

$db->query()PS：如果你问为什么在这里，我会使用 mysqli 类。

我的代码：

//Query one
    $qs=$db->query('SELECT 
     (DATE_FORMAT(regtime, "%M")) AS luna, 
      COUNT(id) AS devizieri FROM edev_useri WHERE groupid = 6 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
      GROUP BY (DATE_FORMAT(regtime, "%M")) 
      ORDER BY luna ASC');   
       
//Query two
     $qz=$db->query('SELECT 
     (DATE_FORMAT(regtime, "%M")) AS bluna, 
      COUNT(id) AS beneficiar FROM edev_useri WHERE groupid = 7 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
      GROUP BY (DATE_FORMAT(regtime, "%M")) 
      ORDER BY bluna ASC');

标签： mysql

解决方案

这是一个解决方案。

SELECT t0.*, t1.devizieri, t2.beneficiar FROM
(SELECT 
 (DATE_FORMAT(regtime, "%M")) AS luna
    FROM edev_useri
  WHERE (groupid = 6 OR groupid = 7) AND YEAR(regtime) = YEAR(CURRENT_DATE) 
  GROUP BY (DATE_FORMAT(regtime, "%M")) 
  ORDER BY luna ASC ) AS t0
LEFT JOIN
(SELECT 
 (DATE_FORMAT(regtime, "%M")) AS luna, 
  COUNT(id) AS devizieri FROM edev_useri WHERE groupid = 6 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
  GROUP BY (DATE_FORMAT(regtime, "%M")) 
  ORDER BY luna ASC ) AS t1
ON t0.luna = t1.luna
LEFT JOIN
(SELECT 
    (DATE_FORMAT(regtime, "%M")) AS luna, 
    COUNT(id) AS beneficiar FROM edev_useri WHERE groupid = 7 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
    GROUP BY (DATE_FORMAT(regtime, "%M")) 
    ORDER BY luna ASC) AS t2
ON t0.luna = t2.luna

推荐阅读