SQL - 组合多个类似查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2245536/
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 - Combining multiple like queries
提问by The Jug
Hey my first question on SO! Anywho...
嘿，我关于 SO 的第一个问题！任何人...
Still relatively a newb at SQL so I think I might be missing something here. My question is I currently have a table full of phone numbers. I want to have a query where I search for phone numbers that are similar to a list I have. So for example, I want to find phone numbers that begin with '555123', '555321', and '555987'. I know normally if you have a list of numbers you could just do a query such as
仍然是 SQL 的新手，所以我想我可能在这里遗漏了一些东西。我的问题是我目前有一张装满电话号码的表格。我想要一个查询，在其中搜索与我拥有的列表相似的电话号码。例如，我想查找以“555123”、“555321”和“555987”开头的电话号码。我通常知道如果你有一个数字列表，你可以做一个查询，比如
SELECT *
FROM phonenumbers
WHERE number in ('5551234567', '5559876543', .... );
Is there a way to do this with like? Such as
有没有办法用like来做到这一点？如
SELECT *
FROM phonenumbers
WHERE number in like ('555123%', '555321%', '555987%'); //I know this doesn't actually work
Instead of have to do this individually
而不是必须单独执行此操作
SELECT *
FROM phonenumbers
WHERE number like '555123%'
or number like '555321%'
or number like '555987%'; //Which does work but takes a long time
Or is there an easier to do this that I'm just missing? I'm using postgres, I don't know if there's any commands it has that would help out with that. Thanks!
或者有没有更容易做到这一点，我只是想念？我正在使用 postgres，我不知道它是否有任何命令可以帮助解决这个问题。谢谢！
回答by Pentium10
You can use SIMILAR TOand separate the tags with | pipe '555123%|555321%|555987%'
您可以使用SIMILAR TO和分隔标签 | 管道'555123%|555321%|555987%'
eg:
例如：
SELECT *
FROM phonenumbers
WHERE number SIMILAR TO '555123%|555321%|555987%'
回答by RickyA
Late to the party, but for posterity... You can also use a ANY(array expression)
迟到了，但为了后代......你也可以使用ANY(array expression)
SELECT *
FROM phonenumbers
WHERE number LIKE ANY(ARRAY['555123%', '555321%', '555987%'])
回答by Quassnoi
Assuming all your numbers do not contain letters, and your numbers are always "prefixes" (ex: LIKE '123%'):
假设您的所有数字都不包含字母，并且您的数字始终是“前缀”（例如：）LIKE '123%'：
SELECT number
FROM (
VALUES
('555123'),
('555321'),
('555000')
) prefixes (prefix)
JOIN phonenumbers
ON number >= prefix
AND number < prefix || 'a'
This will use an index on phonenumbers, if any, so could be faster.
这将在 上使用索引phonenumbers（如果有），所以可能会更快。
回答by toasteroven
I don't think so, but you could join phonenumberson a table criteriacontaining the values you want to match on, i.e.
我不这么认为，但是您可以加入包含要匹配的值phonenumbers的表criteria，即
JOIN criteria ON phonenumbers.number LIKE criteria.phonenumbers
...probably not worth it for a small number of conditions, though
...在少数情况下可能不值得，不过
回答by Victor Farazdagi
You can also rely on POSIX regular expressions, see section 9.7.3 of the official documentation.
您也可以依赖 POSIX 正则表达式，请参阅官方文档的9.7.3 节。
For example:
例如：
SELECT * FROM foobar WHERE name ~ '12345|34567';
It is important to note that your namefield is of a string type.
请务必注意，您的name字段是字符串类型。
回答by flybywire
Maybe if your prefixes are all the same length then you can do where RIGHT(number) in ('123456', '234456', 'etc', 'etc')
也许如果您的前缀长度都相同，那么您可以这样做 where RIGHT(number) in ('123456', '234456', 'etc', 'etc')
