MySQL查询使用CASE选择多列 [英] MySQL query using CASE to SELECT multiple columns

问题描述

我有以下查询可以正常运行:

I have the following query which works correctly:

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语句检查条件似乎效率低下.

However, is there a more efficient way to write it? I need to SELECT a few values based on the conditional (e.g. square or headquarters), but I understand that CASE can only return 1 value. However, it seems inefficient to check the condition with a new CASE statement each time I want to return one value.

SO上的某些人建议对类似的查询使用JOIN语句，但是我不确定这在多个条件下如何工作?

Some people on SO have suggested using JOIN statements for similar queries, but I'm not sure how this would work with multiple conditionals?

谢谢.

推荐答案

我喜欢

I like the IF() function for simple statements:

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;

这篇关于MySQL查询使用CASE选择多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！