在SQL / Oracle中，'％'和'。-1，％'和'，％'或'％'是什么意思？

the code specifically I am referring to is:

我指的具体代码是:

AND (
(','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.' || 
    CASE WHEN log.subtype is null 
    THEN ' ' 
    ELSE log.subtype 
    END || ',%')
OR (','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.-1,%')
OR (to_char(log.logtypeid) LIKE 
    CASE 
    WHEN to_char('~[gpv:lt]') = '-1' 
    THEN '%' 
    ELSE ','||to_char('~[gpv:lt]')||',' 
    END)  
)

Any clarification would be great. Thank you!

任何澄清都会很棒。谢谢!

3 个解决方案

#1

Let's start with this expression:

让我们从这个表达开始:

(','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.' || 
    CASE WHEN log.subtype is null 
    THEN ' ' 
    ELSE log.subtype 
    END || ',%')

It is an example of this idiom:

这是这个成语的一个例子:

','||a||',' LIKE '%,'||b||',%'

in which a is your lt parameter, and b a "type dot subtype" string. This comparison can be used whenever you have an a string that is a comma-separated list of values, and a b string that is a single value, and you want to know whether the list a contains the value b.

其中a是你的lt参数,b是“type dot subtype”字符串。只要您的字符串是以逗号分隔的值列表,并且b字符串是单个值,并且您想知道列表a是否包含值b,就可以使用此比较。

To see why it's written that way, first look at this attempt to do it more simply:

要了解它为什么这样写,首先看看这个尝试更简单地做到:

a LIKE '%'||b||'%'

We take b, put a wildcard at the front and back, and match a. This will be true if a is for example 1,2,3 and b is 2. It will also unfortunately be true if a is 12,34,56 and b is 2. LIKE doesn't do comma-separated list parsing, just string matching.

我们拿b,在正面和背面放一个通配符,然后匹配一个。如果a是例如1,2,3且b是2,那将是真的。如果a是12,34,56并且b是2,那么也将是真的.LIKE不进行逗号分隔列表解析,只是字符串匹配。

So next you might try this:

接下来你可以试试这个:

a LIKE '%,'||b||',%'

Now if b is 2, the pattern is %,2,% - which will match any string containing ,2, so it is true for a=1,2,3 and false for a=12,34,56. Unfortunately it's also false for a=2,3,4 because the 2 doesn't have a comma before it and false for a=0,1,2 because the 2 doesn't have a comma after it.

现在,如果b为2,则模式为%,2,% - 将匹配任何包含2的字符串,因此a = 1,2,3为真,而a = 12,34,56为false。不幸的是,a = 2,3,4也是假的,因为2之前没有逗号,而a = 0,1,2则为假,因为2之后没有逗号。

For the next improvement there are 2 ways to go. You can use separate pattern cases to match b at the beginning, middle, and end of a (if you go this way, using a regular expression will help make it readable!)

对于下一步的改进,有两种方法可供选择。您可以使用单独的模式案例来匹配b的开头,中间和结尾(如果你这样做,使用正则表达式将有助于使其可读!)

The other way is to modify a to match the existing pattern. We didn't match 0,1,2 or 2,3,4 because the first element of the list and the last element of the list aren't surrounded by commas. But if we add a comma to the beginning of a before matching, then the first element of the list will be surrounded by commas! And add another comma to the end of a to make sure the last element is also surrounded by commas.

另一种方法是修改a以匹配现有模式。我们没有匹配0,1,2或2,3,4,因为列表的第一个元素和列表的最后一个元素没有逗号。但是如果我们在匹配之前添加一个逗号,那么列表的第一个元素将被逗号包围!并在a的末尾添加另一个逗号,以确保最后一个元素也被逗号包围。

','||a||',' LIKE '%,'||b||',%'

Now when a is 0,1,2 and b is 2, the LIKE expression becomes:

现在当a为0,1,2且b为2时,LIKE表达式变为:

',0,1,2,' LIKE '%,2,%'

which is a match! The first wildcard absorbs the ,0,1 and the ,2, is found. The last wildcard matches a zero-length substring at the end, which is allowed.

这是一场比赛!第一个通配符吸收了0,1和2,被发现。最后一个通配符匹配末尾的零长度子字符串,这是允许的。

#2

% is known as a Wildcard character. More information here.

%被称为通配符。更多信息在这里。

#3

Those -1,% are string literals and are used to build a LIKE condition based on several contants and the values of some columns. The statement shows a basic misunderstanding on how to use string literals in Oracle.

那些-1,%是字符串文字,用于根据几个内容和某些列的值构建LIKE条件。该声明显示了对如何在Oracle中使用字符串文字的基本误解。

Take for example this condition:

以这种情况为例:

','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.-1,%'

The to_char('~[gpv:lt]') is totally useless because it simply converts the string constant '~[gpv:lt]' to ... a string. So that part could be simplified to:

to_char('〜[gpv:lt]')完全没用,因为它只是将字符串常量'〜[gpv:lt]'转换为...一个字符串。这部分可以简化为:

',~[gpv:lt],' LIKE '%,' || to_char(log.logtypeid) || '.-1,%'

which essentially says:

基本上说:

Compare the string constant ',~[gpv:lt],' with the result of the concatenation of the following

将字符串常量',〜[gpv:lt],'与以下连接的结果进行比较

the constant '%,'

常数'%'

the value of the column log.logtypeid converted to a varchar

列log.logtypeid的值转换为varchar

the constant '.-1,%'

常数'。-1,%'

So, assuming log.logtypeid contains the value 42 this generates the condition

因此,假设log.logtypeid包含值42,则生成条件

',~[gpv:lt],' LIKE '%,42.-1,%'

This could only match if the column log.logtypeid contained a value like ~[gpv:lt]. Which would be a bit confusing as columns named "id" usually don't contain "structured data" like that.

如果列log.logtypeid包含类似〜[gpv:lt]的值,则只能匹配。由于名为“id”的列通常不包含“结构化数据”,因此会有点混乱。

If I had to guess, I'd say the data model is heavily de-normalized and those columns store comma separated, structured data (maybe even structured key/value pairs).

如果我不得不猜测,我会说数据模型被严重去标准化,这些列存储逗号分隔的结构化数据(甚至可能是结构化的键/值对)。

The other conditions do something similar.

其他条件做类似的事情。

#1