如何比较一列的值？(How to compare the values of one column? SQL(Postgres))

如何比较一列的值？(How to compare the values of one column? SQL(Postgres))

Postgres的

我有表user_answers：

---------------------------- | id | user_id | answer_id | ---------------------------- | 1 | 47 | 121 | ---------------------------- | 2 | 47 | 125 | ---------------------------- | 3 | 47 | 141 | ---------------------------- | 4 | 49 | 122 | ---------------------------- | 5 | 49 | 121 | ---------------------------- | 6 | 49 | 101 | ---------------------------- | 7 | 52 | 121 | ---------------------------- | 8 | 52 | 125 | ---------------------------- | 9 | 52 | 101 | ---------------------------- | 10 | 67 | 101 | ----------------------------

我想得到user_id，只有user_id，其中answer_id = 121，answer_id = 125。

一个好结果：47和52因为：user_id = 49有121，但没有125

这个简单的查询不起作用（不返回任何内容）：

SELECT user_id FROM user_answers WHERE answer_id = 121 AND answer_id = 125

Postgres

I have table user_answers:

---------------------------- | id | user_id | answer_id | ---------------------------- | 1 | 47 | 121 | ---------------------------- | 2 | 47 | 125 | ---------------------------- | 3 | 47 | 141 | ---------------------------- | 4 | 49 | 122 | ---------------------------- | 5 | 49 | 121 | ---------------------------- | 6 | 49 | 101 | ---------------------------- | 7 | 52 | 121 | ---------------------------- | 8 | 52 | 125 | ---------------------------- | 9 | 52 | 101 | ---------------------------- | 10 | 67 | 101 | ----------------------------

I would like to get user_id, only user_id where answer_id = 121 and answer_id = 125.

A good result: 47 and 52 because: user_id = 49 has 121, but no 125

This simple query does not work (returns nothing):

SELECT user_id FROM user_answers WHERE answer_id = 121 AND answer_id = 125

最满意答案

您的查询不返回任何内容，因为answer_id在任何给定行上只有一个值。 它不能在行上有多个值。

这是set-within-sets查询的示例。 我建议使用group by和having 。 这是一种方法：

SELECT user_id FROM user_answers WHERE answer_id IN (121, 125) GROPU BY user_id HAVING COUNT(DISTINCT answer_id) = 2;

这将返回具有121,125和其他值的值。 只获得这两个值：

SELECT user_id FROM user_answers WHERE answer_id IN (121, 125) GROPU BY user_id HAVING SUM(CASE wHEN answer_id = 121 THEN 1 ELSE 0 END) > 0 AND SUM(CASE wHEN answer_id = 125 THEN 1 ELSE 0 END) > 0;

Your query returns nothing because answer_id has only one value on any given row. It cannot have multiple values on the row.

This is an example of a set-within-sets query. I would recommend using group by and having. Here is one method:

SELECT user_id FROM user_answers WHERE answer_id IN (121, 125) GROPU BY user_id HAVING COUNT(DISTINCT answer_id) = 2;

THis will return values that have 121, 125 and other values. To get only those two values:

SELECT user_id FROM user_answers WHERE answer_id IN (121, 125) GROPU BY user_id HAVING SUM(CASE wHEN answer_id = 121 THEN 1 ELSE 0 END) > 0 AND SUM(CASE wHEN answer_id = 125 THEN 1 ELSE 0 END) > 0;