postgresql WHERE 子句中的 SQL IN 运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4049879/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-20 00:21:48 来源:igfitidea点击:
SQL IN operator in the WHERE clause
提问by MrB
The following query:
以下查询：
SELECT SUM (peak_power) FROM t_mpi_pv WHERE info_id IN (
SELECT mp_info_id FROM t_mp WHERE mp_id IN (
ARRAY[29800001,29800008,29800015,29800022,29800029]
)
)
Doesn't work. I get the error:
不起作用。我收到错误：
"Operator does not exist: integer = integer[]".
“运算符不存在：整数 = 整数 []”。
But
但
SELECT SUM (peak_power) FROM t_mpi_pv WHERE info_id IN (
SELECT mp_info_id FROM t_mp WHERE mp_id = 29800001
)
Works.
作品。
What am I doing wrong?
我究竟做错了什么？
回答by codaddict
Try:
尝试：
SELECT SUM (peak_power) FROM t_mpi_pv WHERE info_id IN (
SELECT mp_info_id FROM t_mp WHERE mp_id IN
(29800001,29800008,29800015,29800022,29800029)
)
)
回答by Quassnoi
Alternatively, use this syntax:
或者，使用以下语法：
SELECT SUM(peak_power)
FROM t_mpi_pv
WHERE info_id IN
(
SELECT mp_info_id
FROM t_mp
WHERE mp_id = ANY(ARRAY[29800001,29800008,29800015,29800022,29800029])
)
Useful if you pass the array as a single bound variable or a parameter of a function:
如果您将数组作为单个绑定变量或函数的参数传递，则很有用：
CREATE FUNCTION fn_peak_power (mpids INT[])
AS
$$
SELECT SUM(peak_power)
FROM t_mpi_pv
WHERE info_id IN
(
SELECT mp_info_id
FROM t_mp
WHERE mp_id = ANY()
);
$$
LANGUAGE 'sql';
