SQL多个LIKE语句

Question

我目前正在制作一份报告，向我显示我们销售团队涵盖的所有邮政编码。

每个团队涵盖100多个邮政编码。我想做的是创建一个报告，将邮政编码内的客户带回去。目前，我的代码如下所示。

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 ''

只是在钓鱼的想法。干杯

Answer 1

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)
)   


SELECT * 
FROM tbl_ClientFile T
WHERE EXISTS ( SELECT TOP 1 1 FROM CTE WHERE T.CLNTPOST1 LIKE CTE.VALUE + '%')