SQL 多个 LIKE 语句 [英] SQL Multiple LIKE Statements

问题描述

我目前正在编写一份报告，其中显示了我们销售团队涵盖的所有邮政编码.

I'm currently working on a report that shows me all post codes covered by our sales team.

每个团队涵盖 100 多个邮政编码.我想做的是创建一个报告，将客户带回邮政编码中.目前我的代码看起来像这样.

Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this.

SELECT * FROM tbl_ClientFile
WHERE CLNTPOST1 LIKE ('B79%')
OR CLNTPOST1 LIKE ('BB1%')
OR CLNTPOST1 LIKE ('BB10%')
OR CLNTPOST1 LIKE ('BB11%')
OR CLNTPOST1 LIKE ('BB12%')
OR CLNTPOST1 LIKE ('BB18%')
OR CLNTPOST1 LIKE ('BB2%')
OR CLNTPOST1 LIKE ('BB3%')
OR CLNTPOST1 LIKE ('BB4%')
OR CLNTPOST1 LIKE ('BB5%')
OR CLNTPOST1 LIKE ('BB6%')
OR CLNTPOST1 LIKE ('BB8%')
OR CLNTPOST1 LIKE ('BB9%')
OR CLNTPOST1 LIKE ('BB94%')
OR CLNTPOST1 LIKE ('BD1%')
OR CLNTPOST1 LIKE ('BD10%')
OR CLNTPOST1 LIKE ('BD11%')
OR CLNTPOST1 LIKE ('BD12%')
OR CLNTPOST1 LIKE ('BD13%')
OR CLNTPOST1 LIKE ('BD14%')
OR CLNTPOST1 LIKE ('BD15%')
OR CLNTPOST1 LIKE ('BD16%')
OR CLNTPOST1 LIKE ('BD17%')
OR CLNTPOST1 LIKE ('BD18%')
OR CLNTPOST1 LIKE ('BD19%')
OR CLNTPOST1 LIKE ('BD2%')
OR CLNTPOST1 LIKE ('BD20%')
OR CLNTPOST1 LIKE ('BD21%')
OR CLNTPOST1 LIKE ('BD22%')
OR CLNTPOST1 LIKE ('BD3%')
OR CLNTPOST1 LIKE ('BD4%')
OR CLNTPOST1 LIKE ('BD5%')
OR CLNTPOST1 LIKE ('BD6%')

我希望有一种更快更简单的方法来做到这一点.任何建议将不胜感激.有没有办法为每个销售团队创建一个变量，例如 @SalesTeam1 = SELECT * FROM tbl_ClientFile WHERE POSTCODE1 like '' or like ''

What i was hoping for is that there is a faster and easier way of doing this. Any suggestions would be greatly appreciated. Is there a way to create a variable for each sales Team like @SalesTeam1 = SELECT * FROM tbl_ClientFile WHERE POSTCODE1 like '' or like ''

真的只是在寻找想法.干杯

Just fishing for ideas really. Cheers

推荐答案

使用 CTE 作为(选择值从 (值 ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'),('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6')) V(值)) 

WITH CTE AS
(
SELECT VALUE
FROM (
        VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'),
                ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6')
     ) V(VALUE)
)   

选择 *从 tbl_ClientFile TWHERE EXISTS (SELECT TOP 1 1 from CTE WHERE T.CLNTPOST1 LIKE CTE.VALUE + '%')
SELECT * 
FROM tbl_ClientFile T
WHERE EXISTS ( SELECT TOP 1 1 FROM CTE WHERE T.CLNTPOST1 LIKE CTE.VALUE + '%')


                        
这篇关于SQL 多个 LIKE 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！