postgresql SQL 查询以任何顺序将值列表与字段列表匹配而不重复
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22207970/
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
SQL query to match a list of values with a list of fields in any order without repetition
提问by Gabriel Mazetto
I recently had to wrote a query to filter some specific data that looked like the following:
我最近不得不编写一个查询来过滤一些如下所示的特定数据：
Let's suppose that I have 3 distinct values that I want to search in 3 different fields of one of my tables on my database, they must be searched in all possible orders without repetition.
假设我有 3 个不同的值，我想在我的数据库中的一个表的 3 个不同字段中搜索它们，必须以所有可能的顺序搜索它们，而不能重复。
Here is an example (to make it easy to understand, I will use named queries notation to show where the values must be placed):
这是一个示例（为了便于理解，我将使用命名查询符号来显示必须放置值的位置）：
val1 = "a", val2 = "b", val3 = "c"
val1 = "a", val2 = "b", val3 = "c"
This is the query I've generated:
这是我生成的查询：
SELECT * FROM table WHERE
(fieldA = :val1 AND fieldB = :val2 AND fieldC = :val3) OR
(fieldA = :val1 AND fieldB = :val3 AND fieldC = :val2) OR
(fieldA = :val2 AND fieldB = :val1 AND fieldC = :val3) OR
(fieldA = :val2 AND fieldB = :val3 AND fieldC = :val1) OR
(fieldA = :val3 AND fieldB = :val1 AND fieldC = :val2) OR
(fieldA = :val3 AND fieldB = :val2 AND fieldC = :val1)
What I had to do is generate a query that simulates a permutation without repetition. Is there a better way to do this type of query?
我必须做的是生成一个查询来模拟排列而不重复。有没有更好的方法来执行这种类型的查询？
This is OK for 3x3 but if I need to do the same with something bigger like 9x9 then generating the query will be a huge mess.
这对于 3x3 来说是可以的，但是如果我需要对 9x9 之类的更大的东西做同样的事情，那么生成查询将是一团糟。
I'm using MariaDB, but I'm okay accepting answers that can run on PostgreSQL. (I want to learn if there is a smart way of writing this type of queries without "brute force")
我正在使用 MariaDB，但我可以接受可以在 PostgreSQL 上运行的答案。（我想了解是否有一种聪明的方法可以在没有“蛮力”的情况下编写此类查询）
回答by Gordon Linoff
There isn't a muchbetter way, but you can use in:
没有一个多更好的办法，但你可以使用in：
SELECT *
FROM table
WHERE :val1 in (fieldA, fieldB, fieldC) and
:val2 in (fieldA, fieldB, fieldC) and
:val3 in (fieldA, fieldB, fieldC)
It is shorter at least. And, this is standard SQL, so it should work in any database.
至少它更短。而且，这是标准的 SQL，所以它应该适用于任何数据库。
回答by Erwin Brandstetter
... I'm okay accepting answers that can run on PostgreSQL. (I want to learn if there is a smart way of writing this type of queries without "brute force")
...我可以接受可以在 PostgreSQL 上运行的答案。（我想了解是否有一种聪明的方法可以在没有“蛮力”的情况下编写此类查询）
There is a "smart way" in Postgres, with sorted arrays.
Postgres 中有一种“聪明的方法”，即对数组进行排序。
Integer
整数
For integervalues use sort_asc()of the additional module intarray.
对于附加模块的integer值使用。sort_asc()intarray
SELECT * FROM tbl
WHERE sort_asc(ARRAY[id1, id2, id3]) = '{1,2,3}' -- compare sorted arraysWorks for anynumber of elements.
适用于任意数量的元素。
Other types
其他类型
As clarified in a comment, we are dealing with strings.
Create a variant of sort_asc()that works for any typethat can be sorted:
正如评论中阐明的那样，我们正在处理strings。
创建一个sort_asc()适用于任何可以排序的类型的变体：
CREATE OR REPLACE FUNCTION sort_asc(anyarray)
RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT array_agg(x ORDER BY x COLLATE "C") FROM unnest() AS x';
Not as fast as the sibling from intarray, but fast enough.
不如来自 的兄弟姐妹intarray快，但足够快。
- Make it
IMMUTABLEto allow its use in indexes. - Use
COLLATE "C"to ignore sorting rules of the current locale: faster, immutable. - To make the function work for anytype that can be sorted, use a polymorphicparameter.
- 使其
IMMUTABLE允许在索引中使用。 - 使用
COLLATE "C"忽略当前区域设置的排序规则：快的，一成不变的。 - 要使该函数适用于任何可以排序的类型，请使用多态参数。
Query is the same:
查询是一样的：
SELECT * FROM tbl
WHERE sort_asc(ARRAY[val1, val2, val3]) = '{bar,baz,foo}';
Or, if you are not sure about the sort order in "C" locale ...
或者，如果您不确定“C”语言环境中的排序顺序......
SELECT * FROM tbl
WHERE sort_asc(ARRAY[val1, val2, val3]) = sort_asc('{bar,baz,foo}'::text[]);
Index
指数
For best read performance create a functional index(at some cost to write performance):
为了获得最佳读取性能，请创建一个功能索引（以写入性能为代价）：
CREATE INDEX tbl_arr_idx ON tbl (sort_asc(ARRAY[val1, val2, val3]));
回答by T McKeown
My answer assumes there is a Keycolumn that we can single out. The output should be all the keys that meet all 3 values and each field and value being used:
我的回答假设有一个Key我们可以单独列出的列。输出应该是满足所有 3 个值的所有键以及正在使用的每个字段和值：
This "should" get you a list of Keys that meet the criteria
这“应该”为您提供符合条件的密钥列表
SELECT F.KEY
FROM (
SELECT DISTINCT L.Key, L.POS
FROM (
SELECT Key, 'A' AS POS, FieldA AS FIELD FROM table AS A
UNION ALL
SELECT Key, 'B' AS POS, FieldB AS FIELD FROM table AS A
UNION ALL
SELECT Key, 'C' AS POS, FieldC AS FIELD FROM table AS A ) AS L
WHERE L.FIELD IN(:VAL1, :VAL2, :VAL3)
) AS F
GROUP BY F.KEY
HAVING COUNT(*) = 3
回答by a_horse_with_no_name
Although Gordon's answer is definitely shorter and almost certainly faster as well, I was toying with the idea on how to minimize the code change when the number of combinations increase.
尽管戈登的答案肯定更短，而且几乎肯定更快，但我正在考虑如何在组合数量增加时最小化代码更改的想法。
And I can come up with is something for Postgres which is by no means shorter, but more "change-friendly":
我可以想出 Postgres 的一些东西，它绝不是更短，而是更“易于更改”：
with recursive params (val) as (
values (1),(2),(3) -- these are the input values
), all_combinations as (
select array[val] as elements
from params
union all
select ac.elements||p.val
from params p
join all_combinations ac
on array_length(ac.elements,1) < (select count(*) from params)
)
select *
from the_table
where array[id1,id2,id3] = any (select elements from all_combinations);
What does it do?
它有什么作用？
First we create a CTE holding the values we are looking for, the recursive CTE then builds a list of all possible permutations from those values. This list will include too many elements because it will also hold arrays with 1 or two elements.
首先，我们创建一个 CTE 来保存我们正在寻找的值，然后递归 CTE 从这些值构建一个所有可能排列的列表。此列表将包含太多元素，因为它还会保存具有 1 个或两个元素的数组。
The final select that puts the columns that should be compared into an array and compares that with the permutations generated by the CTE.
最后的选择将应该比较的列放入一个数组中，并将其与 CTE 生成的排列进行比较。
Here is a SQLFiddle example: http://sqlfiddle.com/#!15/43066/1
这是一个 SQLFiddle 示例：http://sqlfiddle.com/#!15/43066/1
When the number of values (and columns) increase you only need to add the new value to the valuesrow constructor and add the additional column to the array of columns in the wherecondition.
当值（和列）的数量增加时，您只需将新值添加到values行构造函数并将附加列添加到where条件中的列数组。
回答by Mateus Dubiela Oliveira
Using a naive approach, I would use the inclause for this job, and since there should not be any repetition, exclude when the fields repeat.
使用一种天真的方法，我会在in这项工作中使用该子句，并且由于不应有任何重复，因此在字段重复时排除。
There is also some optimisations you could do.
您还可以进行一些优化。
First you can exclude the last field, since:
首先，您可以排除最后一个字段，因为：
A <> B, A <> C
A <> B, B <> C,
Also means that:
也意味着：
C <> B, C <> A
And also, the following queries doesn't need a previously queried field, since:
而且，以下查询不需要先前查询的字段，因为：
A <> B == B <> A
The query would be written as:
查询将写为：
SELECT * FROM table
WHERE :val1 in (fieldA, fieldB, fieldC) and
:val2 in (fieldA, fieldB, fieldC) and
:val3 in (fieldA, fieldB, fieldC) and
fieldA not in (fieldB, fieldC) and
fieldB <> fieldC
This is a naive approach, there are probably others which use the MySQL API, but this one does the job.
这是一种天真的方法，可能还有其他人使用 MySQL API，但这个方法可以完成工作。
