SQL以不同的顺序选择和分组具有相同单词的记录

我在表中有以下值:

食用水果

id | name                        | price
-----------------------------------------
 1 | 'APPLE BANANA ORANGE'       | 12.00
 2 | 'BANANA ORANGE APPLE'       |  4.00
 3 | 'ORANGE APPLE BANANA'       | 10.00
 4 | 'LEMON APPLE BANANA ORANGE' |  7.00
 5 | 'APPLE LEMON BANANA ORANGE' |  8.00

我想选择前三行的所有值，而我所拥有的只是一个包含“APPLE BANANA ORANGE”的文本

示例

SELECT *
FROM fruits
WHERE name IN
('APPLE BANANA ORANGE','BANANA ORANGE APPLE','ORANGE APPLE BANANA')

问题是这些值来自另一个表，我正在寻找一种方法来生成可能值的不同组合

也许使用一个函数:

SELECT *
FROM fruits f
INNER JOIN order o ON o.name IN some_function(f.name)

也许使用一些正则表达式:

SELECT *
FROM fruits f
INNER JOIN order o ON o.name ~ '(?=' || f.name || ')'

我试图使用一些在互联网上找到的正则表达式，该正则表达式使用环顾四周，并且它们带来了包含这三个词的所有值，但也包含了包含更多词的那个值。像“LEMON APPLE BANANA ORANGE”和“APPLE LEMON BANANA ORANGE”

另一件事是，无论结果出现的顺序如何，我都希望对它们进行分组

SELECT sum(price)
FROM fruits f
INNER JOIN order o ON o.name ~ '(?=' || f.name || ')'
GROUP BY somefunction(name);

谢谢你的帮助

马塞尔·艾萨克(Marcel Isaac)

更新

我创建了一个函数，该函数可拆分字符串的内容，对单词进行排序，并使用排序后的单词返回新的字符串

CREATE OR REPLACE FUNCTION order_words(txt character varying)
returns character varying as
$BODY$
DECLARE
    tmp character varying;
BEGIN
    SELECT string_agg(t,' ')
    INTO tmp
    FROM (
      SELECT rstt
      FROM regexp_split_to_table(txt, ' ') rstt
      ORDER BY rstt) t;

    RETURN tmp;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

当对值执行时，它总是返回相同的字符串

select order_words('APPLE BANANA ORANGE');
    order_words
---------------------
APPLE BANANA ORANGE
(1 row)

select order_words('BANANA ORANGE APPLE');
    order_words
---------------------
APPLE BANANA ORANGE
(1 row)

select order_words('ORANGE APPLE BANANA');
    order_words
---------------------
APPLE BANANA ORANGE
(1 row)

现在我可以编写我的代码

SELECT order_words(name),sum(price)
FROM fruits f
INNER JOIN order o ON order_words(o.name) = order_words(f.name)
GROUP BY order_words(name);

我会测试性能

最佳答案

不知道这是否会比您的解决方案更快:

select f.*
from fruits f
  join orders o
    on string_to_array(f.name, ' ') @> string_to_array(o.name, ' ')
   and cardinality(string_to_array(f.name, ' ')) = cardinality(string_to_array(o.name, ' '));

想法是将两个值都拆分为数组，然后检查它们是否重叠。但是因为“重叠”并不意味着所有元素都相等，所以我也在比较数组的长度。如果它们的长度相等且重叠，则所有元素均相等。
string_to_array(f.name, ' ')表达式可以被索引，并且@>运算符甚至可以使用GIN索引。

关于SQL以不同的顺序选择和分组具有相同单词的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30334845/