在mysql表内连接两个选择查询[重复]

Question

我有这张桌子

id empid  reaction        date_t
1  emp090 not_interested  2017-09-16
2  emp090 not_interested  2017-09-16 

我想根据empid和date_t来计算反应的数量。我试过这个查询。

SELECT c.COUNT(reaction) as interested,c.empid FROM `cases` c 
WHERE c.reaction="interested" and c.empid="EMP12654" 
  AND c.date_t BETWEEN  "2017-09-15"  AND "2017-09-18" 
INNER JOIN 
( SELECT cases.empid COUNT(reaction) not interested FROM `cases` 
  WHERE cases.reaction="not_interested" and cases.empid="EMP12654" 
    AND cases.date_t BETWEEN  "2017-09-15"  AND "2017-09-18" ) 
AS alpha on alpha.empid=c.empid;

谁能告诉我如何正确地做到这一点？

Answer 1

查询中的错误与以下几个错误有关：where连接子句之间的顺序错误，内部连接子选择缺少表名，子查询中列名之间缺少逗号，on子句缺失

但如果你只想要对date_t和经验感兴趣的计数

您可以使用group by，例如：

  SELECT  
         c.empid
       , c.date_t
       , c.COUNT(*) as interested
  FROM `cases` c 
  WHERE  WHERE c.reaction="interested" 
  AND c.empid="EMP12654" 
  AND c.date_t BETWEEN  "2017-09-15"  AND "2017-09-18"
  GROUP BY  c.empid, c.date_t

Answer 2

干得好。

SELECT empid, count(reaction) as count, date_t
FROM cases
WHERE reaction="interested" AND empid="EMP12654" 
AND date_t BETWEEN "2017-09-15" AND "2017-09-18" 
GROUP BY empid, date_t;