在 Postgresql 中的多列上执行 WHERE IN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6672665/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Doing a WHERE IN on multiple columns in Postgresql
提问by PreciousBodilyFluids
I have a table 'answers' with an indexed 'problem_id' integer column, a 'times_chosen' integer column, and an 'option' column that's a varchar. Currently the only values for the 'option' column are 'A', 'B', 'C' and 'D', though those may expand later on. I want to increment by one the 'times_chosen' values of many (50-100) answers, when I know the problem_id and option of each of them.
我有一个表 'answers'，其中有一个索引的 'problem_id' 整数列、一个 'times_chosen' 整数列和一个 varchar 的 'option' 列。目前，'option' 列的唯一值是 'A'、'B'、'C' 和 'D'，但这些值可能会在以后扩展。当我知道每个答案的 question_id 和选项时，我想将许多（50-100）个答案的“times_chosen”值加一。
So I need a query that's something like:
所以我需要一个类似的查询：
UPDATE answers
SET times_chosen = times_chosen + 1
WHERE (problem_id, option) IN ((4509, 'B'), (622, 'C'), (1066, 'D'), (4059, 'A'), (4740, 'A')...)
Is this possible?
这可能吗？
采纳答案by Dirk
It should, at least I've done it before in other SQLs.
它应该，至少我之前在其他 SQL 中做过。
Have you tried it? You can test it with SET times_chosen = times_chosen
你试过吗？你可以用SET times_chosen = times_chosen
回答by Frank Farmer
You can join against a virtual table of sorts:
您可以加入各种虚拟表：
SELECT * FROM answers
JOIN (VALUES (4509, 'B'), (622, 'C'), (1066, 'D'), (4059, 'A'), (4740, 'A'))
AS t (p,o)
ON p = problem_id AND o = option
You can do something similar with UPDATE.
你可以用 UPDATE 做类似的事情。
回答by Flimzy
You can do this if you cast the data to an array first:
如果先将数据转换为数组，则可以执行此操作：
UPDATE answers
SET times_chosen = times_chosen + 1
WHERE ARRAY[problem_id::VARCHAR,option] IN ('{4509,B}', '{622,C}', ... )
However, this will be incredibly inefficient, as it cannot use indexes. Using a JOIN as suggested by @Frank Farmer is a far better solution:
然而，这将是非常低效的，因为它不能使用索引。使用@Frank Farmer 建议的 JOIN 是一个更好的解决方案：
UPDATE answers a
SET times_chosen = times_chosen + 1
FROM (VALUES (4509,'B'), (622,'C') ...) AS x (id,o)
WHERE x.id=a.problem_id AND x.o=a.option;
回答by Winfield Trail
You're probably looking for the
您可能正在寻找
SELECT * FROM foo, bar WHERE foo.bob = "NaN" AND bar.alice = "Kentucky";
style syntax. Essentially, you use tablename.rowname to specify what individual field you're looking for. In order to line everything up properly, you add WHERE clauses that make sure the primary keys match:
样式语法。本质上，您使用 tablename.rowname 来指定您要查找的单个字段。为了正确排列所有内容，您添加 WHERE 子句以确保主键匹配：
...WHERE foo.primarykey = bar.primarykey
or similar. You'd do well to look up inner joins.
或类似。您最好查找内部联接。
