我正在使用一个case语句,想知道是否有更好的方法来编写它?
这就是我目前拥有的,它很有效:
select
CASE WHEN ug.permissions = "" OR ug.permissions IS NULL
THEN
g.permissions
ELSE
ug.permissions
END as permissions,
CASE WHEN ug.display = "" OR ug.display IS NULL
THEN
g.display
ELSE
ug.display
END as display
from system.users_groups as ug
inner join system.groups as g on ug.group_id = g.id
where ug.user_id = ?
我想的更像这样,但我对sql:中的案例语句几乎没有经验
select
CASE WHEN ug.permissions = "" OR ug.permissions IS NULL
THEN
g.permissions,
g.display
ELSE
ug.permissions,
ug.display
END
from system.users_groups as ug
inner join system.groups as g on ug.group_id = g.id
where ug.user_id = ?
我更喜欢以下内容:
SELECT
g.id,
IFNULL(NULLIF(ug.permission, ''), g.permission),
IFNULL(NULLIF(ug.display, ''), g.display)
FROM groups AS g
INNER JOIN users_group AS ug
ON ug.group_id = g.id
SQLFiddle