多列之间的匹配模式 [英] Matching patterns between multiple columns

问题描述

我有两列说Main 和Sub.(它们可以是同一张桌子，也可以不是).

I have two columns say Main and Sub. (they can be of same table or not).

Main 是长度为 20 的 varchar，Sub 是长度为 8 的 varchar.
Sub是Main的总是子集，它是Main的最后8个字符.

Main is varchar of length 20 and Sub is varchar of length 8.
Sub is always subset of Main and it is last 8 characters of Main.

我可以使用 substr("Main",13,8)

查询:

select * from "MainTable"
 where substr("MainColumn",13,8) LIKE (
   select "SubColumn" From "SubTable" Where "SubId"=1043);

但我想在查询中使用 Like、%、_ 等，以便我可以松散地匹配模式(不是全部 8 个字符).

but I want to use Like, % , _ etc in my query so that I can loosely match the pattern (that is not all 8 characters).

问题是我该怎么做.?！

Question is how can i do that.?!

我知道下面的查询完全错误，但我想实现这样的目标，

I know that the query below is COMPLETELY WRONG but I want to achieve something like this,

Select * from "MainTable"
 Where "MainColumn" Like '%' Select "SubColumn" From "SubTable" Where "SubId"=2'

推荐答案

目前的答案未能解决您的问题:

The answers so far fail to address your question:

但我想在我的查询中使用 Like, % , _ 等，以便我可以松散地匹配模式(并非所有 8 个字符).

but I want use Like, % , _ etc in my query so that I can loosely match the pattern (that is not all 8 characters).

使用 LIKE 或 = 几乎没有任何区别，只要匹配整个字符串(并且在> 你的字符串).为了使搜索模糊，您需要替换部分模式，而不仅仅是添加.

It makes hardly any difference whether you use LIKE or = as long as you match the whole string (and there are no wildcard character in your string). To make the search fuzzy, you need to replace part of the pattern, not just add to it.

例如，匹配subcolumn的最后7个(而不是8个)字符:

For instance, to match on the last 7 (instead of 8) characters of subcolumn:

SELECT *
FROM   maintable m
WHERE  left(maincolumn, 8) LIKE 
       ( '%' || left((SELECT subcolumn FROM subtable WHERE subid = 2), 7));

我使用更简单的 left()(在 Postgres 9.1 中引入).
您可以将其简化为:

I use the simpler left() (introduced with Postgres 9.1).
You could simplify this to:

SELECT *
FROM   maintable m
WHERE  left(maincolumn, 7) =
       (SELECT left(subcolumn,7) FROM subtable WHERE subid = 2);

但是如果你使用我在后面提到的特殊索引，你就不会，因为函数索引中的表达式必须精确匹配才能使用.

But you wouldn't if you use the special index I mention further down, because expressions in functional indexes have to matched precisely to be of use.

您可能对扩展pg_tgrm感兴趣.

You may be interested in the extension pg_tgrm.

在 PostgreSQL 9.1 中，每个数据库运行一次:

In PostgreSQL 9.1 run once per database:

CREATE EXTENSION pg_tgrm;

两个原因:

• 它提供了 相似运算符%.有了它，您可以构建智能相似性搜索:

• It supplies the similarity operator %. With it you can build a smart similarity search:

--SELECT show_limit();
SELECT set_limit(0.5); -- adjust similarity limit for % operator

SELECT *
FROM maintable m
WHERE left(maincolumn, 8) %
      (SELECT subcolumn FROM subtable WHERE subid = 2);

它提供索引支持 用于 LIKE 和 %

如果读性能比写性能更重要，我建议你创建一个功能性 GIN 或 GiST 索引如下:

If read performance is more important than write performance, I suggest you create a functional GIN or GiST index like this:

CREATE INDEX maintable_maincol_tgrm_idx ON maintable
USING gist (left(maincolumn, 8) gist_trgm_ops);

该索引支持任一查询.请注意，它会带来一些写入操作成本.
此相关答案中类似案例的快速基准.

This index supports either query. Be aware that it comes with some cost for write operations.
A quick benchmark for a similar case in this related answer.

这篇关于多列之间的匹配模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！