选择所有具有至少一个功能列表的行 [英] Select all rows that have at least a list of features

问题描述

我有EXPERIMENTAL_RUNS（runId），每个都有与它们相关联的任何数量的传感器（sensorId）。考虑到这一点，我有一个RS表加入这两个：

I have EXPERIMENTAL_RUNS (runId), each of which have any number of SENSORS (sensorId) associated with them. With that in mind, I have an RS table to join the two:

==========
RS
==========
runId, sensorId

因此，如果runId = 1的运行传感器具有sensorId = 1，sensorId = 6，sensorId = 8，则RS表中将有3个条目：
（runId = 1，sensorId = 1）
（runId = 1，sensorId = 6）
（runId = 1，sensorId = 8）

Thus if the run with runId=1 had sensors with sensorId=1, sensorId=6, sensorId=8 in it, there would be 3 entries in the RS table: (runId=1, sensorId=1) (runId=1, sensorId=6) (runId=1, sensorId=8)

所有具有传感器的EXPERIMENTAL_RUNS {11,13,15}？从我所看到的，我似乎想要的是一个嵌套哈希加入...这是怎么回事？

Is this really how I would return all EXPERIMENTAL_RUNS that have sensors {11,13,15}? From what I've read, what I seem to want is a nested hash join... Is this what's going to happen?

SELECT a.runId
FROM rs a, rs b, rs c
WHERE
a.runId=b.runId AND
b.runId=c.runId AND
a.sensorId=11 AND
a.sensorId=13 AND
b.sensorId=15

为了澄清，我想只返回具有传感器11和13和15的EXPERIMENTAL_RUNS。

To clarify, I want to return only the EXPERIMENTAL_RUNS that have sensors 11 AND 13 AND 15.

推荐答案

假设 runId，sensorId 在 rs 表中是唯一的，这将会找到 runId s所有3 sensorId s：

Assuming runId, sensorId are unique in the rs table, this will find the runIds that have all 3 sensorIds:

SELECT runId, COUNT(c) ct
FROM rs
WHERE sensorId IN (11, 13, 15)
GROUP BY runId
HAVING ct = 3

这篇关于选择所有具有至少一个功能列表的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！