SQL 如何在 where 子句中正确使用 case
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9793938/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to correctly use case in where clause
提问by James213
So i have a procedure that i'm currently in the process of debugging and i've narrowed it down to this select statement.
所以我有一个程序，我目前正在调试，我已经把它缩小到这个 select 语句。
Note: where the to_date(''), 3300, 5220 is a representation of what would come from a parameter.
注意：其中 to_date(''), 3300, 5220 表示来自参数的内容。
Now what this is suppose to do, is take the parameter which is a timestamp and subtract an offset value
现在这是假设做的，是取一个时间戳参数并减去一个偏移值
the offset is the number of minutes that have gone by since the beginning of the week where sunday at midnight = 0. (So if it was monday at midnight the offset would = 1440).
偏移量是自一周开始以来经过的分钟数，其中星期日午夜 = 0。（因此，如果是星期一午夜，则偏移量将 = 1440）。
when the offset is subtracted from the parameter, you then get the beginning of the week. You then get the offset value from the table which was already predetermined and add that value to the start of the week to obtain the timestamp.
当从参数中减去偏移量时，您就会得到一周的开始。然后您从已经预先确定的表中获取偏移值，并将该值添加到一周的开始以获得时间戳。
This is done in order to get the start date and end date of a shift.
这样做是为了获取轮班的开始日期和结束日期。
My original code works no problem with is below, however it is missing the boundary condition of saturday going into sunday.
我的原始代码在下面没有问题，但是它缺少周六到周日的边界条件。
SELECT SHIFT_ID_PK, SHIFT_NAME_FK,
SHIFT_START_DAY, SHIFT_START_TIME,
SHIFT_END_DAY, SHIFT_END_TIME,
SITE_ID_FK, SHIFT_DAY_ID,
STARTOFFSET, ENDOFFSET,
TO_TIMESTAMP_TZ(TO_CHAR((PSTARTTIMESTAMP - (VSTARTOFFSET / 24 / 60)) + (STARTOFFSET / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'), 'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_START_DATE,
TO_TIMESTAMP_TZ(TO_CHAR((PENDTIMESTAMP - (VENDOFFSET / 24 / 60)) + (ENDOFFSET / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM') ,'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_END_DATE
from shift_tbl
WHERE
ENDOFFSET >= VSTARTOFFSET
and STARTOFFSET < VENDOFFSET
order by shift_start_date asc;
Now what i have come up with to handle this boundary condition is below which i have been testing in a script.
现在我想出的处理这个边界条件的方法低于我在脚本中测试过的条件。
declare
VSTARTOFFSET integer;
VENDOFFSET integer;
SHIFTOFFSET integer;
PSTARTTIMESTAMP timestamp;
PENDTIMESTAMP timestamp;
begin
VSTARTOFFSET := 10020;
VENDOFFSET := 420;
PSTARTTIMESTAMP := TO_DATE('3/17/2012 23:00', 'mm/dd/yyyy hh24:mi');
PENDTIMESTAMP := TO_DATE('3/18/2012 7:00', 'mm/dd/yyyy hh24:mi');
SELECT SHIFT_ID_PK, SHIFT_NAME_FK,
SHIFT_START_DAY, SHIFT_START_TIME,
SHIFT_END_DAY, SHIFT_END_TIME,
SITE_ID_FK, SHIFT_DAY_ID,
STARTOFFSET, ENDOFFSET,
TO_TIMESTAMP_TZ(TO_CHAR((PSTARTTIMESTAMP - (VSTARTOFFSET / 24 / 60)) + (STARTOFFSET / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'),'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_START_DATE,
TO_TIMESTAMP_TZ(TO_CHAR((PENDTIMESTAMP- (VENDOFFSET / 24 / 60)) + (ENDOFFSET / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'),'YYYY-MM-DD HH:MI:SS AM TZH:TZM') AS SHIFT_END_DATE
from SHIFT_TBL
where
case
when SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1 then
SHIFTOFFSET:= ENDOFFSET + 10080;
and VENDOFFSET := VENDOFFSET + 10080;
else
SHIFTOFFSET := ENDOFFSET;
end
SHIFTOFFSET >= VSTARTOFFSET
and STARTOFFSET < VENDOFFSET
order by SHIFT_START_DATE asc;
end;
As you can see i am unsure as to how to handle the case statement inside of the where clause. Basically what i am trying to do is if the start day is Saturday and the end day is Sunday, then add 10080(one week) to the end offset/vend offset and if it does not meet that condition, then use the original values.
如您所见，我不确定如何处理 where 子句中的 case 语句。基本上我想要做的是，如果开始日是星期六，结束日是星期日，然后将 10080（一周）添加到结束偏移/销售偏移，如果不满足该条件，则使用原始值。
Basically my question is fairly simple...i believe but i am having difficultly obtaining the solution. So what i would like to know is how to properly use a case statement inside of the where clause. And if i am not suppose to use a case statement in this form inside a where clause how exactly would i set up this select statement.
基本上我的问题相当简单......我相信，但我很难获得解决方案。所以我想知道的是如何在 where 子句中正确使用 case 语句。如果我不打算在 where 子句中使用这种形式的 case 语句，我将如何设置这个 select 语句。
Any help or suggestions are greatly appreciated. Thank you.
非常感谢任何帮助或建议。谢谢你。
回答by arturro
You do not need to set any variables in the WHERE clause, actually even you can't do it. What you want to do is to write correct logical predicate (that is an expression returning true or false) describing rows you want to get.
你不需要在 WHERE 子句中设置任何变量，实际上你也做不到。您想要做的是编写正确的逻辑谓词（即返回 true 或 false 的表达式）描述您想要获取的行。
Here are 2 examples how I would try to define it (as far as I understand your requirements):
以下是我如何尝试定义它的 2 个示例（据我了解您的要求）：
without CASE:
WHERE ( SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1 AND ENDOFFSET + 10080 >= VSTARTOFFSET and STARTOFFSET < VENDOFFSET + 10080) OR ( NOT (SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1) AND SHIFTOFFSET >= VSTARTOFFSET and STARTOFFSET < VENDOFFSET )with CASE:
WHERE (CASE WHEN SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1 THEN ENDOFFSET + 10080 ELSE ENDOFFSET END) >= VSTARTOFFSET AND STARTOFFSET < (CASE WHEN SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1 THEN VENDOFFSET + 10080 ELSE VENDOFFSET END)
没有案例：
WHERE ( SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1 AND ENDOFFSET + 10080 >= VSTARTOFFSET and STARTOFFSET < VENDOFFSET + 10080) OR ( NOT (SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1) AND SHIFTOFFSET >= VSTARTOFFSET and STARTOFFSET < VENDOFFSET )案例：
WHERE (CASE WHEN SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1 THEN ENDOFFSET + 10080 ELSE ENDOFFSET END) >= VSTARTOFFSET AND STARTOFFSET < (CASE WHEN SHIFT_START_DAY = 7 and SHIFT_END_DAY = 1 THEN VENDOFFSET + 10080 ELSE VENDOFFSET END)
I did not debug this expressions so do not expect them to work ;), but I hope that you have got the idea.
我没有调试这个表达式，所以不要指望它们能工作;)，但我希望你有这个想法。
回答by onedaywhen
Don't calculate, query :)
不要计算，查询:)
Suggestion: consider using an auxiliary calendar table
建议：考虑使用辅助日历表
Why should I consider using an auxiliary calendar table?
A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree. Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:
How many business days between x and y?
What are all of the dates between the second Tuesday of March and the first Friday in April?
- ...
为什么要考虑使用辅助日历表？
日历表可以使围绕任何涉及日期的业务模型开发解决方案变得更加容易。最后我查了一下，这在某种程度上几乎涵盖了您能想到的任何商业模式。最终需要冗长、复杂和低效的方法的持续问题包括以下问题：
x 和 y 之间有多少个工作日？
三月的第二个星期二和四月的第一个星期五之间的所有日期是什么？
- ...
...perhaps with a column for julianized business days.
...也许有一个用于 julianized 工作日的列。
回答by Todd Allen
Instead of trying to set the variables in the WHERE clause, I would suggest putting a base query into a cursor and using that to drive the main query in a loop. That would allow you to set the variables outside the query for each iteration. It would look something like this:
我建议不要尝试在 WHERE 子句中设置变量，而是建议将基本查询放入游标中并使用它来驱动循环中的主查询。这将允许您为每次迭代在查询之外设置变量。它看起来像这样：
declare
--variables
cursor c_shifts is
select SHIFT_ID_PK, SHIFT_START_DAY, SHIFT_END_DAY
from SHIFT_TBL;
begin
for r_result in c_shifts
loop
if r_result.SHIFT_START_DAY = 1 and r_result.SHIFT_END_DAY = 7 then
--set variables to values for special case shifts
else
--set variables for all other cases
end if;
--run your query for the particular result in this loop iteration based upon r_result.SHIFT_ID_PK, using the variables you set above
--save results to a staging table, directly dbms_output from the block, etc., as needed
end loop;
--commit results if saving to a staging table, etc., as needed once the loop completes
end;
