gpt4 book ai didi

mySQL 没有给出应有的结果

转载 作者:行者123 更新时间:2023-11-29 12:32:25 27 4
gpt4 key购买 nike

我一直在为一项作业创建数据库,并且有一个需要实现的查询。查询需要提供具有两个以上更新补丁的所有项目的项目 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 子句

按项目ID分组

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

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

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com