结合使用 CASE 和 WHERE 来指定不同的列作为过滤依据 [英] Using CASE in combination with WHERE to specify different columns to filter by

问题描述

这可能不是有效的 SQL，但这是我想要做的:

This probably isn't valid SQL, but this is what I'm trying to do:

SELECT * 
FROM tablename
WHERE 
    (CASE @ScoreType 
        WHEN 1 THEN score1 
        WHEN 2 THEN score2 
        ELSE score3 END) >= @Score

基本上，我试图根据 @ScoreType 的值在 WHERE 子句中按不同的列进行过滤.我猜测我的方法无效是否正确?如果是这样，解决此问题的正确方法是什么?

Basically, I'm trying to filter by different columns in the WHERE clause depending on the value of @ScoreType. Am I right in guessing that my approach isn't valid? If so, what would be the proper way to go about this?

edit:抱歉，我在简化查询时不小心省略了 end 以在此处发布.实际查询确实有 end.查询本身运行良好，但结果并不如预期.原来问题出在别处.我怀疑 WHERE 子句，因为我以前从未尝试过这样的事情，并且不确定它是否是有效的 SQL.

edit: Sorry, I accidentally omitted end while simplifying the query to post here. The actual query does have end. The query itself runs fine, but the results weren't as expected. It turns out the issue was elsewhere. I suspected the WHERE clause because I've never tried anything like this before and was unsure of whether it was valid SQL.

推荐答案

这应该可行吗?

SELECT * 
FROM tablename
WHERE 
    (CASE @ScoreType 
        WHEN 1 THEN score1 
        WHEN 2 THEN score2 
        WHEN 3 THEN score3
      END) >= @Score

<小时>

我认为问题是:你忘记了案例的end

I think the problem is: you forgot the end to the case

这篇关于结合使用 CASE 和 WHERE 来指定不同的列作为过滤依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！