如何检查同一ID在不同行中是否具有不同的值? [英] How can I check if the same ID has different values in different rows?

问题描述

我有下表:

+----+----------+-------------------+
| ID | ID_CICLO | id_caratteristica |
+----+----------+-------------------+
| 1  | 72       | 2                 |
+----+----------+-------------------+
| 2  | 72       | 3                 |
+----+----------+-------------------+
| 3  | 73       | 2                 |
+----+----------+-------------------+
| 4  | 73       | 4                 |
+----+----------+-------------------+
| 9  | 3        | 2                 |
+----+----------+-------------------+
| 10 | 3        | 4                 |
+----+----------+-------------------+

我想提取所有id_caratteristica = 2并且也等于4的ID.

And I want to extract all IDs that have an id_caratteristica = 2 and also equal to 4.

这是我尝试的方法，但这是错误的，因为And运算符仅在同一行上工作.

This is what I tried, but it is wrong because the And operator only works on the same row.

SELECT * FROM caratteristiche_ciclo 
WHERE id_caratteristica = 2 
  and id_caratteristica = 4

结果应为ID_CICLO {3, 73}. 我该如何实现?

The result should be ID_CICLO {3, 73}. How can I achieve that?

推荐答案

您可以使用基于条件聚合的过滤来解决此问题.首先，获取所有ID其中的id_caratteristica IN(2,4).

You can approach this problem using conditional aggregation based filtering. Firstly, get all the ID where id_caratteristica IN(2,4).

然后，您可以使用HAVING子句过滤出具有两个id_caratteristica值的ID:

Then, you can use HAVING clause to filter out the ID having both the id_caratteristica values:

SELECT id  
FROM caratteristiche_ciclo  
WHERE id_caratteristica IN (2,4) 
GROUP BY id 
HAVING COUNT(DISTINCT id_caratteristica) = 2

为了获得良好的性能，您可以定义以下覆盖索引:(id, id_caratteristica)

For good performance, you can define the following Covering Index: (id, id_caratteristica)

这篇关于如何检查同一ID在不同行中是否具有不同的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！