Linux mysql，使用 if
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3677150/
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
mysql, using if
提问by user319854
I have this sql query:
我有这个 sql 查询：
...
LEFT JOIN users ON
users.id = mod.id and mod.level = 1
...
But if don't found any result with mod.level = 1, i wish search with mod.data > 1 (users.id = mod.id and mod.data > 1)
但是如果没有找到 mod.level = 1 的任何结果，我希望搜索 mod.data > 1（users.id = mod.id 和 mod.data > 1）
回答by Scott's Oasys
You can switch your and to a WHERE and use an OR function:
您可以将 and 切换到 WHERE 并使用 OR 函数：
LEFT JOIN users ON
users.id = mod.id
WHERE mod.level = 1
OR mod.data > 1
回答by the_void
You can additionally filter on the JOINlike this:
您还可以JOIN像这样过滤：
LEFT JOIN users ON users.id = mod.id AND (mod.level = 1 OR mod.data > 1)
回答by RedFilter
Try something like this:
尝试这样的事情：
select *
from MyTable m
left outer join (
select id
from MyTable
where level = 1
) ml on m.id = ml.id
left outer join users u on m.id = u.id
and (u.id = m1.id or (m1.id is null and m.level > 1))
回答by Bill
It may be slow but the join returns all rows that fit either case. Then uses the where clause to filter out the rows you don't want.
它可能很慢，但连接返回适合任何一种情况的所有行。然后使用 where 子句过滤掉不需要的行。
Select *
From
LEFT JOIN users ON users.id = mod.id AND (mod.level = 1 OR mod.data > 1)
Where
Case
When mod.level = 1 then 1
When Not Exists(Select 1 from users Where users.id = mod.id and mod.level=1)
AND mod.data > 1 then 1
Else 0 END = 1;
回答by knittl
maybe using a XOR?
也许使用异或？
...
LEFT JOIN users
ON users.id = mod.id
WHERE mod.level = 1
XOR mod.data > 1
...
this will get rows where mod.level is 1 or mod.data is greater than 1, but not rows where level is 1 and data is greater 1 at the same time
这将获得 mod.level 为 1 或 mod.data 大于 1 的行，但不会同时获得 level 为 1 且 data 大于 1 的行
if you only want to look at mod.data when mod.level is not 1 use the following condition:
如果您只想在 mod.level 不是 1 时查看 mod.data，请使用以下条件：
...
WHERE mod.level = 1
OR (mod.level != 1
AND mod.data > 1)
...
