MySQL GROUP BY NULL和EMPTY [英] MySQL GROUP BY NULL and EMPTY

问题描述

在一个MySQL查询中，我正在做一个带有文本字段的 GROUP BY 。由于原始数据的性质，某些行包含此字段的空字符串，其他行为true null 。

In a MySQL query I am doing a GROUP BY with a text field. Due to the nature of the original data, some rows are contain empty strings for this field, and others are true null.

在分组时，如何将空字符串和null分组在一起，将两者都视为 null ？

When grouping, how can I group empty string and null together, treating both as null?

推荐答案

这可以通过SELECT CASE来完成。可能有一种更简单的方式，我不知道。

This can be accomplished by SELECT CASE. There may be a simpler way I do not know of.

SELECT CASE的格式是

The format of SELECT CASE is

SELECT
CASE
    WHEN table_name.text_field IS NULL OR table_name.text_field = ''
    THEN null
    ELSE table.text_field
END as new_field_name,
other_field, another_field, ...rest of query...

看到，你可以用WHEN / THEN将CASE值合并，并通过ELSE默认为真实值。

So you see, you can CASE together values with WHEN/THEN and default to the real value by ELSE.

这篇关于MySQL GROUP BY NULL和EMPTY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！