请帮助我进行 SQL 查询。这是带有数据的测试表:
CREATE TABLE "Cats"
(
"CatId" SERIAL PRIMARY KEY,
"Name" character varying NOT NULL
);
CREATE TABLE "Measures"
(
"MeasureId" SERIAL PRIMARY KEY,
"CatId" integer NOT NULL REFERENCES "Cats",
"Weight" double precision NOT NULL,
"MeasureDay" integer NOT NULL
);
INSERT INTO "Cats" ("Name") VALUES
('A'), ('B'), ('C')
;
INSERT INTO "Measures" ("CatId", "Weight", "MeasureDay") VALUES
(1, 5.0, 1),
(1, 5.3, 2),
(1, 6.1, 5),
(2, 3.2, 1),
(2, 3.5, 2),
(2, 3.8, 3),
(2, 4.0, 4),
(2, 4.0, 5),
(3, 6.6, 1),
(3, 6.9, 2),
(3, 7.0, 3),
(3, 6.9, 4)
;
如何选择具有所有 5 天度量的 CatId(MeasureDay 取 (1, 2, 3, 4, 5) 中的所有值)?在此测试数据上,查询应返回 2,因为只有 CatId = 2 的 Cat 具有所有天数(1、2、3、4、5)的度量。
我假设我应该使用 GROUP BY "CatId" 和 HAVING 子句,但是在 HAVING 中应该使用什么样的查询?
尝试这样使用 group by
select CatId
from Measures
where MeasureDay in (1, 2, 3, 4, 5)
group by CatId
having count(distinct MeasureDay) = 5;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句