在WHERE子句中使用CASE或AND/ or来限制结果

Question

我知道有很多类似的问题，我浏览了大约20个问题来寻找答案。大多数设置一个单独的变量，或使用AND/or格式，我目前有。

我试图创建一个函数，我在城市，州，Zip和国家的组合传递，它定位并返回所有信息。如果我使用OR方法，第一个选项将返回结果，如果Zip和Country不是NULL，但是只有Zip不是NULL的选项也可以返回结果。这些是我想要它们评估的顺序(从上到下)。我只返回一个值，但我不能保证结果的顺序相同，所以我想消除冗余的结果。

这是我当前的查询:

SELECT TOP(1) City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
(   -- Zip Code, Country: Will find City and State  /* BEST RESULTS */
@inZip IS NOT NULL AND 
@inCountry IS NOT NULL 
AND 
Zip = @inZip AND 
Country = @inCountry 
)
OR
(   -- City, State, Country:  Will find first Zip Code in City, State
@inCity IS NOT NULL AND 
@inState IS NOT NULL AND 
@inCountry IS NOT NULL 
AND 
City = @inCity AND 
(State_abbr = @inState OR State_long = @inState) AND 
Country = @inCountry 
)
OR
(   -- City, State: Will find first Zip Code in City, State. Will find Country
@inCity IS NOT NULL AND 
@inState IS NOT NULL 
AND 
City = @inCity AND 
(State_abbr = @inState OR State_long = @inState) 
)
OR          
(   -- City, State, Zip: Will find Country
@inState IS NOT NULL AND 
@inCountry IS NOT NULL 
AND 
(State_abbr = @inState AND State_long = @inState) AND 
Country = @inCountry 
)
OR
(   -- City, Zip:  Will find State and Country
@inCity IS NOT NULL AND 
@inState IS NOT NULL 
AND 
City = @inCity AND 
(State_abbr = @inState AND State_long = @inState) 
)
OR
(   -- State, Zip:  Will find City and Country
@inCity IS NOT NULL AND 
@inCountry IS NOT NULL 
AND 
City = @inCity AND 
Country = @inCountry 
)
OR
(
-- Zip Code: Will find City, State and Country  ** Possible error if Country is not US or Canada
@inZip IS NOT NULL 
AND 
Zip = @inZip 
)

这是我对CASE

的尝试

SELECT TOP(1) City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
CASE
-- Zip Code, Country: Will find City and State  /* BEST RESULTS */
WHEN ( @inZip IS NOT NULL AND @inCountry IS NOT NULL )
THEN Zip = @inZip AND Country = @inCountry 
-- City, State, Country:  Will find first Zip Code in City, State
WHEN ( @inCity IS NOT NULL AND @inState IS NOT NULL AND @inCountry IS NOT NULL )
THEN City = @inCity AND (State_abbr = @inState OR State_long = @inState) AND Country = @inCountry 
END

我收到

'='附近语法错误。

对应= in "THEN Zip = @inZip AND Country = @inCountry.

如果使用CASE是不可能的，是否有可能用我的OR语句做一些不同的事情来实现我正在寻找的?

• SQL Server v15.0.2070
• ssm v18.5

Answer 1

要确保该行只返回一次，请添加额外的逻辑以确保在已经找到时忽略它，例如替换

(
-- Zip Code: Will find City, State and Country  ** Possible error if Country is not US or Canada
@inZip IS NOT NULL 
AND Zip = @inZip 
)

(
-- Zip Code: Will find City, State and Country  ** Possible error if Country is not US or Canada
@inZip IS NOT NULL 
-- Only go through this branch if country is null because otherwise we have handled it elsewhere
AND @inCountry IS NULL 
AND Zip = @inZip 
)

并使用类似的逻辑来处理其他类似的情况。

Answer 2

我们可以对每组条件做一个UNION ALL，添加一个排序列并取第一个

这应该是非常高性能的，因为它使用了MERGE CONCATENATION，正如Paul White所描述的:

SELECT TOP(1) City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
(SELECT TOP(1) 1 AS Ordering, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
(   -- Zip Code, Country: Will find City and State  /* BEST RESULTS */
@inZip IS NOT NULL AND 
@inCountry IS NOT NULL 
AND 
Zip = @inZip AND 
Country = @inCountry 
)
UNION ALL
SELECT TOP(1) 2, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
(   -- City, State, Country:  Will find first Zip Code in City, State
@inCity IS NOT NULL AND 
@inState IS NOT NULL AND 
@inCountry IS NOT NULL 
AND 
City = @inCity AND 
(State_abbr = @inState OR State_long = @inState) AND 
Country = @inCountry 
)
UNION ALL
SELECT TOP(1) 3, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
(   -- City, State: Will find first Zip Code in City, State. Will find Country
@inCity IS NOT NULL AND 
@inState IS NOT NULL 
AND 
City = @inCity AND 
(State_abbr = @inState OR State_long = @inState) 
)
UNION ALL
SELECT TOP(1) 4, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
(   -- City, State, Zip: Will find Country
@inState IS NOT NULL AND 
@inCountry IS NOT NULL 
AND 
(State_abbr = @inState AND State_long = @inState) AND 
Country = @inCountry 
)
UNION ALL
SELECT TOP(1) 5, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
(   -- City, Zip:  Will find State and Country
@inCity IS NOT NULL AND 
@inState IS NOT NULL 
AND 
City = @inCity AND 
(State_abbr = @inState AND State_long = @inState) 
)
UNION ALL
SELECT TOP(1) 6, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
(   -- State, Zip:  Will find City and Country
@inCity IS NOT NULL AND 
@inCountry IS NOT NULL 
AND 
City = @inCity AND 
Country = @inCountry 
)
UNION ALL
SELECT TOP(1) 7, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
(
-- Zip Code: Will find City, State and Country  ** Possible error if Country is not US or Canada
@inZip IS NOT NULL 
AND 
Zip = @inZip 
)
) t
ORDER BY ordering;

Answer 3

根据您的CASE示例，我猜您在寻找这样的东西(?):

SELECT TOP(1) City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
FROM [ULTRA].[dbo].[CityStateInfo]
WHERE
1 = CASE
-- Zip Code, Country: Will find City and State  /* BEST RESULTS */
WHEN ( @inZip IS NOT NULL AND @inCountry IS NOT NULL )
THEN 
CASE WHEN Zip = @inZip AND Country = @inCountry THEN 1 ELSE 0 END
-- City, State, Country:  Will find first Zip Code in City, State
WHEN ( @inCity IS NOT NULL AND @inState IS NOT NULL AND @inCountry IS NOT NULL )
THEN 
CASE WHEN City = @inCity AND (State_abbr = @inState OR State_long = @inState) AND Country = @inCountry 
THEN 1 ELSE 0 END
ELSE 0          
END 

注意"ELSE 0"部分是可选的，如果需要的话可以删除，因为如果CASE不匹配任何条件并且一直到最后，它将返回NULL，它永远不会等于1。