mysql - 合并两个mysql查询的问题

Question

我有两个查询希望合并为一个。该查询将由 Rails 应用程序中的主干调用。最终结果应该是这样的。提前致谢。

标题展示次数已完成测试1234 500 34

SELECT title, count(*) as impressions FROM `stats`
WHERE `stats`.`user_id` = 2 
AND (stats.event ='play' and videopos = '0') 
AND (date_time between '2014-03-08 00:00:00' and '2014-03-15 23:59:59') 
GROUP BY title;

SELECT title, count(*) as completed FROM `stats`
WHERE `stats`.`user_id` = 2 
AND (stats.event ='completed') 
AND (date_time between '2014-03-08 00:00:00' and '2014-03-15 23:59:59') 
GROUP BY title;

Answer 1

尝试一下:

  SELECT title,(select count(*)  FROM `stats`  WHERE stats.event ='play' and videopos = '0' AND `stats`.`user_id` = 2 AND (date_time between '2014-03-08 00:00:00' and '2014-03-15 23:59:59') ) as impressions ,(select count(*)  FROM `stats`  WHERE stats.event ='completed' AND `stats`.`user_id` = 2 AND (date_time between '2014-03-08 00:00:00' and '2014-03-15 23:59:59') ) as completed
  FROM `stats`
  WHERE `stats`.`user_id` = 2
  AND (date_time between '2014-03-08 00:00:00' and '2014-03-15 23:59:59') 
  GROUP BY title;