mysql - MySQL没有给出应有的结果

我一直在为任务创建数据库，并且有一个需要实现的查询。
查询需要提供具有两个以上更新补丁的所有项目的项目ID。
我正在使用查询：

SELECT projectID
FROM Transaction
HAVING COUNT(Transaction.status = ‘patch’) > 2

但是我从中得到了projectID 1，尽管仅有两个以上补丁的项目是9。

我正在使用桌子：

CREATE TABLE Transaction
(
    transactionID SMALLINT NOT NULL,
    ProjectID SMALLINT NOT NULL,
    PRIMARY KEY(transactionID, projectID),
    FOREIGN KEY(projectID) REFERENCES Project(projectID),
    userID SMALLINT NOT NULL,
    FOREIGN KEY(userID) REFERENCES Developer(userID),
    date DATE NOT NULL,
    status ENUM('upload','update','patch') NOT NULL
);

这是我表中的数据：

INSERT INTO Transaction
(transactionID, projectID, userID, date, status) values

(1,1,2,'2013-12-29','upload'),
(2,7,8,'2014-05-12','update'),
(3,9,20,'2013-07-15','patch'),
(4,12,8,'2013-08-04','upload'),
(5,10,26,'2014-11-06','update'),
(6,9,26,'2013-07-04','patch'),
(7,20,26,'2012-12-15','upload'),
(8,13,14,'2013-02-16','update'),
(9,12,8,'2014-07-11','patch'),
(10,19,20,'2013-08-14','upload'),
(11,19,20,2013-02-13,'update'),
(12,14,20,'2013-11-10','patch'),
(13,18,14,'2012-12-26','upload'),
(14,1,2,'2014-07-14','update'),
(15,12,8,'2013-04-11','patch'),
(16,3,14,'2013-09-16','upload'),
(17,4,20,'2013-09-02','update'),
(18,9,20,'2014-09-18','patch'),
(19,8,14,'2013-03-24','upload'),
(20,17,8,'2014-10-20','update');

有谁知道为什么这不起作用？

最佳答案

您缺少group by子句

按projectid分组

SELECT projectID
   FROM Transaction
   where Transaction.status = 'patch'
   group by projectid
   HAVING COUNT(*) > 2

关于mysql - MySQL没有给出应有的结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27279502/

projectId

mysql - MySQL没有给出应有的结果