根据子表中的多个列组合选择父行

Question

给定以下表格：

父级：

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

属性：

+----+-----------+------+-------+
| id | parent_id | name | value |
+----+-----------+------+-------+
|  1 |         1 | aaa  |     5 |
|  2 |         1 | bbb  |     2 |
|  3 |         1 | ccc  |     9 |
|  4 |         2 | aaa  |     8 |
|  5 |         2 | ccc  |     9 |
|  6 |         3 | bbb  |    10 |
|  7 |         3 | ddd  |     1 |
|  8 |         3 | ccc  |     9 |
+----+-----------+------+-------+

一般来说，查询的形式如下：如果：，则选择父ID

* Have a property called 'xxx' with value 'yyy'                                         <---|
* and/or                                                                                    |
* Have a property called 'xxx' with value NOT equal 'yyy' OR do not have 'xxx' proprty. <---|
* and/or                                                                                    |
* Repeat -----------------------------------------------------------------------------------|

例如，查询可能如下所示：如果：，则选择父ID

• 有一个名为"bbb"的属性，它的值！=2或没有"bbb"属性
• (和(
• 有一个名为"ccc"的属性，它的值==9

在上面的例子中，查询的结果是：

• 2(没有"bbb"属性，具有"ccc"==9(
• 3(具有一个值为！=2的"bbb"属性，并且具有一个"ccc"==9(

目前，我正在做这样的事情：

select * from parent where
-- use "not in" and invert check for "NOT equal or no property queries"
id not in (select parent_id from property where name = 'bbb' and value = 2)
and id in (select parent_id from property where name = 'ccc' and value = 9)

我想知道是否有更好的方法，因为在where子句中可能会增长到过多的select。

Answer 1

您可以连接表并使用条件聚合：

select p.id
from parent p inner join property t
on t.parent_id = p.id
group by p.id
having sum(t.name = 'bbb' and t.value = 2) = 0
and sum(t.name = 'ccc' and t.value = 9) > 0

这并不一定比您的解决方案在性能方面更好，但我认为它更具可读性和可扩展性，因此您可以添加更多条件

还有一个EXISTS/NOT EXISTS:的解决方案

select p.* from parent p
where not exists (select 1 from  property t where t.parent_id = p.id and t.name = 'bbb' and t.value = 2)
and exists (select 1 from  property t where t.parent_id = p.id and t.name = 'ccc' and t.value = 9)

请参阅演示
结果：

> | id |
> | -: |
> |  2 |
> |  3 |

Answer 2

Forpas的答案是正确的。然而，它可以通过两种方式进行简化和改进。

首先，查询不需要join。其次，SQLite支持元组表示法来简化逻辑。因此，逻辑可以表示为：

select parent_id
from property p
group by parent_id
having sum( (name, value) = ('ccc', 9) ) > 0 and
sum( (name, value) = ('bbb', 2) ) = 0;

这是一个SQL Fiddle。