返回一行中包含NULL或0值的列

I've a Table with 10 fields. Each field or column contains Integer values.

我有一个包含10个字段的表。每个字段或列都包含整数值。

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

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

5 个解决方案

#1

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视为零，您可以使用：从表中选择COALESCE（colname，0），其中COALESCE（colname，0）> 1;

#2

Use where column_name is null or column_name = 0

用于column_name为null或column_name = 0的位置

#3

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 SELECT keyword. 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.)

这是一行到单行中的column_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 
       )

#4

Do you need the columns that have NULL or 0, or the rows with a column that is NULL or 0, or the rows where all columns 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;

#5

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.

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