我有以下可以正确运行的查询:
SELECT Future.enemy_type,
CASE WHEN Future.enemy_type = 'square' THEN Users.color
ELSE ''
END AS color,
CASE WHEN Future.enemy_type = 'square' THEN Users.user_ID
ELSE ''
END AS ID,
CASE WHEN Future.enemy_type = 'headquarters' THEN Users.username
ELSE ''
END AS username,
CASE WHEN Future.enemy_type = 'headquarters' THEN Users.home_lat
ELSE ''
END AS lat
FROM Future
LEFT JOIN Users ON Future.user_ID_affected = Users.user_ID
WHERE Future.time > 1539503510
AND Future.time <= 1539503512
AND Future.user_ID = 10;
但是,有没有更高效的写法呢?我需要根据条件(例如广场或总部)选择一些值,但我知道 CASE 只能返回 1 个值。然而,每次我想返回一个值时都用新的 CASE 语句检查条件似乎效率很低。
SO 上的一些人建议对类似查询使用 JOIN 语句,但我不确定这如何与多个条件一起使用?
提前致谢。
我喜欢IF()简单语句的函数:
SELECT
Future.enemy_type,
IF (Future.enemy_type = 'square', Users.color, '') AS color,
IF (Future.enemy_type = 'square', Users.user_ID, '') AS ID,
IF (Future.enemy_type = 'headquarters', Users.username, '') AS username,
IF (Future.enemy_type = 'headquarters', Users.home_lat, '') AS lat
FROM Future
LEFT JOIN Users
ON Future.user_ID_affected = Users.user_ID
WHERE
Future.time > 1539503510 AND
Future.time <= 1539503512 AND
Future.user_ID = 10;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)