MySQL 如果 WHERE 子句上的其他
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15162404/
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 else on WHERE clause
提问by botenvouwer
I've this query:
我有这个查询：
SELECT `id` , `naam`
FROM `klanten`
WHERE (
`email` LIKE '%@domain.nl%'
OR `email2` LIKE '%@domain.nl%'
)
But I want to do something like this:
但我想做这样的事情：
SELECT `id` , `naam`
FROM `klanten`
WHERE IF(`email` > 0,
`email` LIKE '%@domain.nl%'
, `email2` LIKE '%@domain.nl%'
)
How to check if email exist? I want to use email and if this field is empty I want to use email2. How do I accomplish this?
如何检查电子邮件是否存在？我想使用电子邮件，如果此字段为空，我想使用 email2。我该如何实现？
回答by Rocket Hazmat
IFis used to select the field, then the LIKEclause is placed after it:
IF用于选择字段，然后在其后LIKE放置子句：
SELECT `id` , `naam`
FROM `klanten`
WHERE IF(`email` != '', `email`, `email2`) LIKE '%@domain.nl%'
回答by Gordon Linoff
You want to use coalesce():
你想使用coalesce()：
where coalesce(email, email2) like '%[email protected]%'
If you want to handle emptystrings ('') versus NULL, a case works:
如果要处理空字符串 ('') 与 NULL，则有一个案例：
where (case when email is NULL or email = '' then email2 else email end) like '%[email protected]%'
And, if you are worried about the string really being just spaces:
而且，如果您担心字符串真的只是空格：
where (case when email is NULL or ltrim(email) = '' then email2 else email end) like '%[email protected]%'
As an aside, the sample ifstatement is really saying "If email starts with a number larger than 0". This is because the comparison is to 0, a number. MySQL implicitly tries to convert the string to a number. So, '[email protected]' would fail, because the string would convert as 0. As would '[email protected]'. But, '[email protected]' and '[email protected]' would succeed.
顺便if说一句，示例语句实际上是在说“如果电子邮件以大于 0 的数字开头”。这是因为比较的是 0，一个数字。MySQL 隐式地尝试将字符串转换为数字。因此，'[email protected]' 会失败，因为字符串会转换为 0。就像 '[email protected]' 一样。但是，“[email protected]”和“[email protected]”会成功。
回答by RB.
Note the following is functionally different to Gordon Linoff's answer. His answer assumes that you want to use email2if emailis NULL. Mine assumes you want to use email2if emailis an empty-string. The correct answer will depend on your database (or you could perform a NULL check andan empty-string check - it all depends on what is appropriate for your database design).
请注意，以下内容与 Gordon Linoff 的回答在功能上有所不同。他的回答假设您要使用email2ifemail为 NULL。我的假设你想使用email2ifemail是一个空字符串。正确答案将取决于您的数据库（或者您可以执行 NULL 检查和空字符串检查 - 这完全取决于您的数据库设计适合什么）。
SELECT `id` , `naam`
FROM `klanten`
WHERE `email` LIKE '%[email protected]%'
OR (LENGTH(email) = 0 AND `email2` LIKE '%[email protected]%')
回答by Ameen Maheen
try this ,hope it helps
试试这个，希望它有帮助
select user_display_image as user_image,
user_display_name as user_name,
invitee_phone,
(
CASE
WHEN invitee_status=1 THEN "attending"
WHEN invitee_status=2 THEN "unsure"
WHEN invitee_status=3 THEN "declined"
WHEN invitee_status=0 THEN "notreviwed" END
) AS invitee_status
FROM your_tbl
回答by Thanura Jayasekara
Here is a sample query for a table having a foreign key relationship to the same table with a query parameter.
下面是一个表的示例查询，该表与具有查询参数的同一个表具有外键关系。
SET @x = -1;
SELECT id, categoryName
FROM Catergory WHERE IF(@x > 0,category_ParentId = @x,category_ParentId IS NOT NULL);
@xcan be changed.
@x可以改变。
