使用“END as”语句从动态生成的列名中选择不为null的字段

NULL 是 不相等 到 '' 在里面 MySQL . 无效的 方法 no data 和 empty string 方法 空字符串 ,它们是两种不同的东西。

您需要根据空字符串筛选数据,如

where weight_group != '';

现在,您可以通过多种方式实现所需的结果。

1、使用子查询

SELECT * 
FROM (SELECT player_name,weight,
             CASE 
                WHEN weight > 250 THEN 'over 250' 
                WHEN weight > 200 THEN '201-250' 
                WHEN weight > 175 THEN '176-200' 
                ELSE '' 
            END AS weight_group 
      FROM benn.college_football_players) AS t 
WHERE weight_group != '';

2、使用分组依据

SELECT player_name,weight,
         CASE WHEN weight > 250 THEN 'over 250'
              WHEN weight > 200 THEN '201-250'
              WHEN weight > 175 THEN '176-200'
              ELSE '' 
          END AS weight_group
FROM benn.college_football_players
GROUP BY weight_group, weight, player_name
HAVING weight_group != '';

您可以添加where条件 其中重量>;175

SELECT player_name,weight,
 CASE WHEN weight > 250 THEN 'over 250'
      WHEN weight > 200 THEN '201-250'
      WHEN weight > 175 THEN '176-200'
    ELSE '' END AS weight_group FROM benn.college_football_players where weight > 175

这里其他部分不归还任何东西。也可以使用以下代码

SELECT player_name,weight,
 CASE WHEN weight > 250 THEN 'over 250'
      WHEN weight > 200 THEN '201-250'
      WHEN weight > 175 THEN '176-200'
END AS weight_group FROM benn.college_football_players where weight > 175