时间戳

首页 > 解决方案 > MySQL 一行仅选择没有 NULL 值的列

mysql - MySQL 一行仅选择没有 NULL 值的列

问题描述

    SELECT r.id AS Id, r.slot AS Slot, rg.name AS Grade, rt.name AS Type,
    r.main_stat AS Main Stat, r.stat01 AS 1st Substat, r.stat02 AS 2nd Substat,
    r.stat03 AS 3rd Substat, r.stat04 AS 4th Substat,
    CASE WHEN r.isEquipted = 0 THEN 'FALSE' ELSE 'TRUE' END AS isEquipted      
    FROM runes r                                                                           
    INNER JOIN rune_grade rg                                                                  
    ON r.grade_id = rg.id                                                                  
    INNER JOIN rune_type rt                                                                   
    ON r.type_id = rt.id                                                                    
    WHERE r.id = 7;

我有这个查询，它只给了我一条记录。WHERE 子句中的这个“7”在我的代码中是可变的。我想删除给我 NULL 值的列。此查询返回：

ID Slot Grade Type M Stat 1st Stat 2nd Stat 3rd Stat 4th Stat

7 4 normal swift spd-24 NULL NULL NULL NULL

如果 Where 子句等于其他 id，则 1st 2nd 3rd 4th Stat 可以有值。

ID Slot Grade Type M Stat 1st Stat 2nd Stat 3rd Stat 4th Stat

6 3 normal swift spd-24 atk-32 hp-355 NULL NULL

对不起英语不好.. <3

标签： mysqlsqlrow

解决方案

用于COALESCE()过滤掉所有[X Stat]列都为的行NULL：

WHERE r.id = 7 
  AND COALESCE(r.stat01, r.stat02, r.stat03, r.stat04) IS NOT NULL

推荐阅读