获取具有多个条件的行

拉胡尔（Rahul Radhakrishnan）

下面是我的Postgres表：

表：

+------+-----------------+---------+
| sku  | properties      | value   |
|------+-----------------+---------|
| 1    | Family_ID       | 21      |
| 1    | Class_ID        | 21      |
| 2    | Family_ID       | 20      |
| 2    | Class_ID        | 21      |
| 3    | Family_ID       | 21      |
| 3    | Class_ID        | 21      |
+------+-----------------+---------+

如何查询我是否想获取数据，其中的Family_ID和Class_ID是21。

期望的返回值：

+------+-----------------+---------+
| sku  | properties      | value   |
|------+-----------------+---------|
| 1    | Family_ID       | 21      |
| 1    | Class_ID        | 21      |
| 3    | Family_ID       | 21      |
| 3    | Class_ID        | 21      |
+------+-----------------+---------+

如何查询是否要在Family_IDis20和Class_IDis中获取数据21。

期望的返回值：

+------+-----------------+---------+
| sku  | properties      | value   |
|------+-----------------+---------|
| 2    | Family_ID       | 20      |
| 2    | Class_ID        | 21      |
+------+-----------------+---------+

福帕斯

该查询：

select sku
from tablename
group by sku
having 
  max(case when properties = 'Family_ID' then value end) = 21
  and
  max(case when properties = 'Class_ID' then value end) = 21

返回所有sku满足条件的，您可以将其与运算符一起使用，IN如下所示：

select * from tablename
where sku in (
  select sku
  from tablename
  group by sku
  having 
    max(case when properties = 'Family_ID' then value end) = 21
    and
    max(case when properties = 'Class_ID' then value end) = 21
)

您还可以使用MAX（）窗口函数：

select t.sku, t.properties, t.value
from (
  select *,
    max(case when properties = 'Family_ID' then value end) over (partition by sku) family_id,
    max(case when properties = 'Class_ID' then value end) over (partition by sku) class_id
  from tablename  
) t  
where t.family_id = 21 and t.class_id = 21

参见演示。
结果：

> sku | properties | value
> --: | :--------- | ----:
>   1 | Family_ID  |    21
>   1 | Class_ID   |    21
>   3 | Family_ID  |    21
>   3 | Class_ID   |    21

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。