Sql server where condition基于所选的case列 [英] Sql server where condition based on selected case column

问题描述

如何根据case语句中选择的列编写sql where条件。



我尝试了什么：



我根据case语句选择列，并希望根据case语句中的选定列过滤where子句。

How to write sql where condition based on column selected in case statement.

What I have tried:

I am selecting columns based on case statement and want to filter with where clause based on selected column in case statement.

推荐答案

如果我正确理解了描述，你可以在where子句中使用CASE，例如

If I understand the description correctly, you could use CASE in the where clause, like

...
WHERE date1 <= CASE WHEN ...
AND   date2 >= CASE WHEN ...
...

但是因为你只想要来自不同字段的第一个非空值，我相信使用COALESCE（Transact-SQL） [ ^ ]会更容易，比如

But since you just want the first non null value from different fields, I believe using COALESCE (Transact-SQL)[^] would be easier, something like

SELECT COALESCE(UpdatedDate, InsertedDate, VisitedDate)
FROM tbl_UserTracking
WHERE COALESCE(UpdatedDate, InsertedDate, VisitedDate) BETWEEN date1 AND date2

见这个 [ ^ ]

在这种情况下你可以使用 IsNULL 。



因此您的查询将如下所示：

You can use IsNULL in this case.

So your query will look as below:

Select case when UpdatedDate is not null then UpdatedDate when InsertedDate is not null then InsertedDate else VisitedDate end
From tbl_UserTracking
where Isnull(UpdatedDate,Isnull(InsertedDate,VisitedDate)) between date1 and date2

如果有的话请告诉我您有任何疑虑或疑问，或者您仍然面临问题。

Please let me know if you have any concern or query or if you are still facing issue.

这篇关于Sql server where condition基于所选的case列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！