SQL多个在同一个字段上的位置

I have this table

我有这张桌子

FieldA | FieldB
---------------
1        12
1        13
1        14
2        12
2        13
3        12
3        13

I want to obtain FieldA, where FieldB is equal to 12, but also equal to 13, but NOT equal to 14.

我想获得FieldA,其中FieldB等于12,但也等于13,但不等于14。

Expected output:

FieldA
-------
2
3

So far I've come to this:

到目前为止,我来到这里:

SELECT FieldA FROM table
WHERE FieldB IN (12, 13) AND FieldB NOT IN (14)

But it doesn't seem to work, what am I missing ? Also, I would like to do it in a way that it is cross-database.

但它似乎不起作用,我错过了什么?另外,我想以跨数据库的方式这样做。

1 个解决方案

#1

This is an example of a "set-within-sets" subqueries. I like to handle these with group by and having, because this is a very flexible approach. Based on the question in the text:

这是“set-within-sets”子查询的示例。我喜欢用group by和having来处理这些问题,因为这是一种非常灵活的方法。根据案文中的问题:

SELECT FieldA
FROM table
GROUP BY FieldA
HAVING SUM(CASE WHEN FieldB = 12 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN FieldB = 14 THEN 1 ELSE 0 END) = 0;

Each condition in the having clause is checking for one of the conditions you care about. The first counts the number of rows where FieldB = 12 and the > 0 makes sure there is at least one. The second counts the number of rows where FieldB = 14. The = 0 makes sure there are none.

having子句中的每个条件都是检查您关心的条件之一。第一个计算FieldB = 12的行数,> 0确保至少有一个。第二个计算FieldB = 14的行数。= 0确保没有。

If, as suggested by your code, you want 12 and 13, then you can do:

如果您的代码建议您想要12和13,那么您可以:

SELECT FieldA
FROM table
GROUP BY FieldA
HAVING SUM(CASE WHEN FieldB = 12 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN FieldB = 13 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN FieldB = 14 THEN 1 ELSE 0 END) = 0;

And, if you want 12 or 13, you can do:

而且,如果您想要12或13,您可以:

SELECT FieldA
FROM table
GROUP BY FieldA
HAVING SUM(CASE WHEN FieldB IN (12, 13) THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN FieldB = 14 THEN 1 ELSE 0 END) = 0;

#1