MySQL SQL中where子句中的IF语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5987208/
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
IF statement inside where clause in SQL
提问by einstein
I'm developing a calendar app. It stores times in two formats. The table have following columns title, startDate, startTime. If the user provides start time plus start date. The database stores UNIX time stamp(number of seconds since UNIX epok) in the column startTime, while startDate is NULL. If the user only provide a start date, the database stores the date in format nnnn-mm-ddin startDateand NULLin ′startTime`. I have this DB structure, because it's easier to display times around the world, because different timezones have different daylight saving times.
我正在开发一个日历应用程序。它以两种格式存储时间。该表具有以下列title, startDate, startTime。如果用户提供开始时间加上开始日期。数据库将 UNIX 时间戳（自 UNIX epok 以来的秒数）存储在 列中startTime，而 startDate 为NULL。如果用户只提供开始日期，则数据库以“startTime”nnnn-mm-dd中startDate和NULL“startTime”的格式存储日期。我有这个数据库结构，因为它更容易显示世界各地的时间，因为不同的时区有不同的夏令时。
I want select the events that are occurring after a specified date. The client computer provide the server with a UNIX timestamp of the beginning of that day($unix) and a date($date) in the format nnnn-mm-ddto select the correct dates.
我想选择在指定日期之后发生的事件。客户端计算机向服务器提供那天开始的 UNIX 时间戳 ( $unix) 和日期 ( $date) 格式nnnn-mm-dd以选择正确的日期。
The problem is, I don't know how to select those days that are occurring as specified above. This solution is not applicable for me, even though it works:
问题是，我不知道如何选择上面指定的那些日子。此解决方案不适用于我，即使它有效：
SELECT *
FROM events
WHERE startDate >= '$date'
OR startTime >= '$unix'
The thing is I have in some rows where unix time stamp is provided in startTime, I also have a date provided in startDateand other reason I which I don't want to explain. And because of that I can't use the solution above.
问题是我在某些行中提供了 Unix 时间戳startTime，我也有一个日期startDate和其他我不想解释的原因。正因为如此，我不能使用上面的解决方案。
I need some kind of solution that have an IF statement inside the Where clause like:
我需要某种在 Where 子句中包含 IF 语句的解决方案，例如：
SELECT *
FROM events
WHERE IF(startTime = NULL, startDate >= '$date', startTime >= '$unix')
I'm just guessing this solution. But is it right?
我只是在猜测这个解决方案。但这是对的吗？
回答by zerkms
WHERE (startTime IS NULL AND startDate >= '$date')
OR (startTime IS NOT NULL AND startTime >= '$unix')
回答by Ken Downs
All SQL dialects support CASE WHEN:
所有 SQL 方言都支持 CASE WHEN：
SELECT *
FROM events
WHERE CASE WHEN startTime is null
THEN startDate >= '$date'
ELSE startTime >= '$unix'
回答by Ibu
SELECT *
FROM events
WHERE
(startDate >= '$date' OR startTime >= '$unix')
AND
startDate != NULL
This will not return any row that has null value for startDate
这不会返回 startDate 为空值的任何行
回答by M.R.
I'm assuming that you want to use startDate when startDate is not null, and startTime when startTime is not null... then try this...
我假设你想在 startDate 不为空时使用 startDate ，当 startTime 不为空时使用 startTime ......然后试试这个......
SELECT * FROM events WHERE (startTime is not NULL and startDate >= '$date') OR (startTime is not NULL and startTime >= '$unix')
You can use the CASE statement also, but this makes more sense is more readable.
您也可以使用 CASE 语句，但这更有意义，更具可读性。
回答by josebailo
I know this is an old question but I think this is the best way to proceed...
我知道这是一个老问题，但我认为这是继续进行的最佳方式......
SELECT *
FROM events
WHERE IF(startTime IS NULL, '$date', '$unix') =< startDate
回答by Mitch Wheat
You just need some combined boolean logic in the WHEREclause:
您只需要在WHERE子句中使用一些组合布尔逻辑：
SELECT *
FROM events
WHERE
(startDate IS NULL AND startTime >= '$unix') OR
(startTime IS NULL AND startDate >= '$date')
