一张图片可能有10个结果(2个分类,8个检测)。我希望限制作用于图像而不是结果。因此,我期望得到10行,代表1张图像。
以下是我的查询。问题在于结果行的重复和组合。我期望每个结果都有一行,而不是合并检测和分类。我需要使用 UNION ALL 或其他方法吗?
CREATE TABLE images (
id VARCHAR(40) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE image_results_classification (
image_id VARCHAR(40) NOT NULL,
c_confidence REAL NOT NULL,
FOREIGN KEY (image_id) REFERENCES images(id)
);
CREATE TABLE image_results_detection (
image_id VARCHAR(40) NOT NULL,
d_confidence REAL NOT NULL,
FOREIGN KEY (image_id) REFERENCES images(id)
);
INSERT INTO images (id) VALUES ('123');
INSERT INTO images (id) VALUES ('456');
INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('123', 0.9);
INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('123', 0.8);
INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('456', 0.7);
INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('123', 0.1);
INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('123', 0.2);
INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('456', 0.3);
这个模式是为了简化问题而创造的:两个结果表上有更多行,并且它们也不同(不仅仅是置信度)。
我想在我的应用层最终得到的类型是:
Map[图像,(List [分类结果],List [检测结果])]
也就是说,需要图像和所有结果。 结果集中带有null也可以。 可能像这样:
id c_confidence d_confidence
123 0.9 NULL
123 0.8 NULL
123 NULL 0.1
123 NULL 0.2
456 0.7 NULL
456 NULL 0.3
这是来自DB Fiddle的查询:
SELECT *
FROM images INNER JOIN
(SELECT id FROM images LIMIT 10 OFFSET 0
) AS i
ON (images.id = i.id) OUTER LEFT JOIN
image_results_classification c
ON (images.id = c.image_id) OUTER LEFT JOIN
image_results_detection d
ON (images.id = d.image_id);
https://www.db-fiddle.com/f/tuDxwY7kQGfEvZSzaajESG/0
编辑:我有一个附加要求,需要对结果进行过滤,并能够限制和偏移图像。
我想执行以下查询:
给我所有的图片和它们的所有结果,其中c_confidence > 0.5。即,如果一张图片的c_confidence为0.4,则应包括该图片(但不包括它的任何结果)。如果它的c_confidence为0.6,则返回所有结果(包括image_results_detection)。
我已更新我的fiddle以反映这一点:https://www.db-fiddle.com/f/tuDxwY7kQGfEvZSzaajESG/1
在fiddle中,我希望没有结果返回,因为该图像没有具有置信度>0.8的image_results_classification。