如何使用where-in子句以编程方式包含多个列的PostgreSQL查询？

问题描述

我的查询是这样的：

select * from plat_customs_complex
where (code_t,code_s) 
in (('01013090','10'),('01029010','90'));

在psql控制台中运行良好。我的问题是如何在客户端代码中执行此查询。（通过C＃或Java）

It runs well in psql console. My question is how to perform this query in client code.(via C# or Java)

而且我已经知道以下代码可以很好地运行（C＃）：

And I already know the following code works well(C#):

string[] codeT = new string[]{"01013090","01029010"};    
connection.Query("SELECT * FROM plat_customs_complex WHERE code_t=ANY(@CodeT)",
new { CodeT = codeT });

推荐答案

最后，我找到了 unnest 函数可以提供帮助。

Finally, I found the unnest function can help.

纯SQL就是这样：

select * from plat_customs_complex
where (code_t,code_s) = ANY(select * from unnest(ARRAY['01013090','01029010'],ARRAY['10','90']))

可以轻松将其转换为C＃代码：

Can convert it to C# code easily:

string[] codeTs = new string[]{"01013090","01029010"}; 
string[] codeSs = new string[]{"10", "90"};
connection.Query("select * from plat_customs_complex
where (code_t,code_s) = ANY(select * from unnest(@CodeTs, @CodeSs))", 
new {CodeTs=codeTs, CodeSs=codeSs});

这篇关于如何使用where-in子句以编程方式包含多个列的PostgreSQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！