MySQL 返回在一行中具有 NULL 或 0 个值的列

Return columns which has NULL or 0 Values in a row

提问by user1549991

I've a Tablewith 10 fields. Each fieldor columncontains Integervalues.

我Table有 10 个字段。每个field或column包含Integer值。

Now I need only field(s) to be returned which has Nullor 0in the result set.

现在我只需要返回结果集中有Null或0有的字段。

回答by Quitiweb

This worked for me. For instance:

这对我有用。例如：

Instead of:

代替：

where column_name is null or column_name = 0

It would be:

这将是：

where COALESCE(column_name,0) = 0

Posted by Jon Gabrielson on December 18, 2002

乔恩·加布里埃尔森 (Jon Gabrielson) 于 2002 年 12 月 18 日发表

The function 'COALESCE' can simplify working with null values. for example, to treat null as zero, you can use: select COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

函数“COALESCE”可以简化对空值的处理。例如，要将 null 视为零，您可以使用： select COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

回答by Jeshurun

Use where column_name is null or column_name = 0

用 where column_name is null or column_name = 0

回答by spencer7593

It's not clear what you are asking.

不清楚你在问什么。

Can you elaborate a bit on what the resultset should look like, do you want all 10 columns returned, but only include the rows that have at least one column containing NULL or 0? That's very easy to do, by specifying appropriate predicates in the WHERE clause.

您能否详细说明结果集应该是什么样子，您是否希望返回所有 10 列，但只包括至少有一个包含 NULL 或 0 的列的行？通过在 WHERE 子句中指定适当的谓词，这很容易做到。

SELECT col0, col1, col2, col3, col4, col5, col6, col7, col8, col9
  FROM mytable
 WHERE IFNULL(col0,0) = 0
    OR IFNULL(col1,0) = 0
    OR IFNULL(col2,0) = 0
    OR IFNULL(col3,0) = 0
    OR IFNULL(col4,0) = 0
    OR IFNULL(col5,0) = 0
    OR IFNULL(col6,0) = 0
    OR IFNULL(col7,0) = 0
    OR IFNULL(col8,0) = 0
    OR IFNULL(col9,0) = 0

That will return all rows that have a zero or NULL in at least one of the specified columns.

这将返回至少在指定列之一中具有零或 NULL 的所有行。

But your question seems to be asking about something a little bit different; you seem to be asking about returning only certain columns based on conditions. The columns to be returned in the result set are determined by the list of expressions following the SELECTkeyword. You can't dynamically alter the expressions in the SELECT list based on the values the column contain.

但是你的问题似乎是在问一些有点不同的东西；您似乎要求根据条件仅返回某些列。结果集中要返回的列由SELECT关键字后面的表达式列表决定。您不能根据列包含的值动态更改 SELECT 列表中的表达式。

To return the names of the columns which have at least one row that contains a NULL or zero in that column, you could write a query like this (this is limited to 5 columns, could be easily extended to 10 or more columns):

要返回在该列中至少有一行包含 NULL 或零的列的名称，您可以编写这样的查询（限制为 5 列，可以轻松扩展到 10 列或更多列）：

SELECT 'col0' AS col_name FROM mytable WHERE IFNULL(col0,0) = 0
 UNION SELECT 'col1' FROM mytable WHERE IFNULL(col1,0) = 0
 UNION SELECT 'col2' FROM mytable WHERE IFNULL(col2,0) = 0
 UNION SELECT 'col3' FROM mytable WHERE IFNULL(col3,0) = 0
 UNION SELECT 'col4' FROM mytable WHERE IFNULL(col4,0) = 0

(That query is going to do some serious scanning through the table. If indexes are available, the predicates can be rewritten to allow for index range scan.)

（该查询将对表进行一些认真的扫描。如果索引可用，则可以重写谓词以允许索引范围扫描。）

Here's a way to to the column_names in a single row. (A NULL in one of the columns would mean that the column does not contain any zeros or NULL.)

这是在单行中访问 c​​olumn_names 的方法。（其中一列中的 NULL 表示该列不包含任何零或 NULL。）

SELECT (SELECT 'col0' FROM mytable WHERE IFNULL(col0,0)=0 LIMIT 1) AS col0
     , (SELECT 'col1' FROM mytable WHERE IFNULL(col1,0)=0 LIMIT 1) AS col1
     , (SELECT 'col2' FROM mytable WHERE IFNULL(col2,0)=0 LIMIT 1) AS col2
     , (SELECT 'col3' FROM mytable WHERE IFNULL(col3,0)=0 LIMIT 1) AS col3
     , (SELECT 'col4' FROM mytable WHERE IFNULL(col4,0)=0 LIMIT 1) AS col4

But it would be much faster to do a single scan through the table:

但是通过表格进行单次扫描会快得多：

SELECT IF(c0>0,'col0',NULL)
     , IF(c1>0,'col1',NULL)
     , IF(c2>0,'col2',NULL)
     , IF(c3>0,'col3',NULL)
     , IF(c4>0,'col4',NULL)
  FROM ( SELECT SUM(IF(IFNULL(col0,0)=0,1,0)) AS c0
              , SUM(IF(IFNULL(col1,0)=0,1,0)) AS c1 
              , SUM(IF(IFNULL(col2,0)=0,1,0)) AS c2 
              , SUM(IF(IFNULL(col3,0)=0,1,0)) AS c3
              , SUM(IF(IFNULL(col3,0)=0,1,0)) AS c4
         FROM mytable 
       )

回答by Ozzy

I recently had to make a similar method in another language. The logic was to check that all "columns" in a record were "true" before it could be processed.

我最近不得不用另一种语言制作类似的方法。逻辑是在处理记录之前检查记录中的所有“列”是否为“真”。

The way I got around it was with this logic:

我绕过它的方式是这样的逻辑：

# loop through the records
for ($i=0; $i<count($records); $i++) {

  # in each record, if any column is 0 or null we will disgard it
  # this boolean will tell us if we can keep the record or not
  # default value is 'true', this gives it a chance to survive
  $keepit = true;

  # loop through each column
  for ($j=0; $j<count($records[$i]); $j++) {

    # add your boolean condition (true=go ahead, false=cant go ahead)
    $goahead = (!is_null($records[$i][$j]) && $records[$i][$j] != 0);

    # convert the boolean to a 0 or 1,
    # find the minimum number in a record
    # (so if any field in a record is false i.e. 0 or null, dont use the record)
    $keepit = min(+$goahead, +$keepit);

  }#end of col loop

  if ($keepit) {
    # keep the record
    array_push($anotherArray, $records[$i]);
  }

}

I've written this in PHP for you. It's probably doable in MySQL too but I'd recommend you fetch all of the records, process them in PHP, and send them back to MySQL.

我已经用 PHP 为您编写了这个。这在 MySQL 中也可能是可行的，但我建议您获取所有记录，在 PHP 中处理它们，然后将它们发送回 MySQL。

In future, you should design your database table so that invalid records are not allowed/not stored in the first place.

将来，您应该设计您的数据库表，以便首先不允许/不存储无效记录。

回答by dakhota

Do you need the columnsthat have NULL or 0, or the rowswith a column that is NULL or 0, or the rows where allcolumns are either NULL or 0?

你需要的列有NULL或0，或行与列是NULL或0，或行所有列是NULL或0？

But I doubt you would have 10 equivalent columns in a well-designed table, so you might want to re-design your data model -- make the columns rows in another table, and join the two tables together.

但是我怀疑在一个设计良好的表中是否会有 10 个等效的列，因此您可能想要重新设计数据模型——在另一个表中创建列行，然后将两个表连接在一起。

You might have something like this:

你可能有这样的事情：

CREATE TABLE a(a_id int primary key, b0 int, b1 int, b2 int, ..., b9 int );

But you want this:

但你想要这个：

CREATE TABLE a( a_id int primary key );
CREATE TABLE b( b_id int primary key );

INSERT INTO b (b_id) values (0), (1), ..., (9);

CREATE TABLE ab (
  a_id int, b_id int,
  v int not null, -- this would be the value of b0, b1, ...
  foreign key (a_id) references a(a_id),
  foreign key (b_id) references b(b_id),
  primary key(a_id, b_id)
);

And then you can write something like:

然后你可以写一些类似的东西：

SELECT * FROM a, b -- cross join to get all possible combinations
WHERE (a_id, b_id) NOT IN (
  -- then remove the ones that have a value
  SELECT a_id, b_id FROM a JOIN ab ON a.id = ab.a_id JOIN b ON ab.a_id
  WHERE ab.v <> 0);

The last line is unnecessary if, before running it, you delete all 0'd lines:

如果在运行之前删除所有 0 行，则最后一行是不必要的：

DELETE FROM ab WHERE v = 0;