仅从表中选择具有空值/空值的列

I have a data set that looks like this but columns goes on as data4, data5 up to data20

我有一个看起来像这样的数据集,但列作为data4,data5直到data20

id      data1       data2       data3
(int)   (varchar)   (int)       (date)
-------------------------------------
1       xyz         (null)      0000-00-00
2       (empty)     321         2013-09-02
3       abc         555         2013-02-29
4       def         (null)      2013-09-02
5       lmn         678         2013-03-19

I only want to select the rows that has columns with null or empty values so the customer can decide which fields are needed to be filled in their tickets.

我只想选择具有null或空值的列的行,以便客户可以决定需要在其票证中填写哪些字段。

data1       data2       data3
(varchar)   (int)       (date)
-------------------------------------
2            1,4          1,5

Or if anyone could think of a better variation? I am open to anything at the moment as long as it is doing the work I want. Is this even possible? Thanks!

或者,如果有人能想到更好的变化?只要它正在完成我想要的工作,我现在对任何事情持开放态度。这有可能吗?谢谢!

2 个解决方案

#1

This would probably be a bad idea considering you have up to 20 data columns, but would something like this work?

考虑到你有多达20个数据列,这可能是一个坏主意,但这样的工作会是什么?

SELECT * FROM
    (SELECT GROUP_CONCAT(id) data1 FROM foo WHERE data1 IS NULL OR data1 = '') AS f1,
    (SELECT GROUP_CONCAT(id) data2 FROM foo WHERE data2 IS NULL OR data2 = '') AS f2,
    (SELECT GROUP_CONCAT(id) data3 FROM foo WHERE data3 IS NULL OR data3 = '') AS f3

#2

You can do it with dynamic SQL like this

你可以像这样使用动态SQL

DELIMITER $$
CREATE PROCEDURE sp_empty()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(
           CONCAT('(SELECT GROUP_CONCAT(id) FROM Table1 WHERE `', 
                  column_name, '` IS NULL ',
                  CASE  
                    WHEN data_type IN('varchar', 'char') 
                      THEN CONCAT('OR `', column_name, '` = ''''')
                    WHEN data_type IN('date', 'datetime', 'time') 
                      THEN CONCAT('OR `', column_name, '` = 0')
                    ELSE ''
                  END, ')`', column_name, '`'))
     INTO @sql
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = SCHEMA()
     AND table_name = 'table1'
     AND column_name NOT IN ('id')
   GROUP BY table_name;

  SET @sql = CONCAT('SELECT ', @sql);

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Note that it takes care of different data types of columns.

请注意,它会处理不同的列数据类型。

Sample usage:

CALL sp_empty();

Output:

| DATA1 | DATA2 | DATA3 |
|-------|-------|-------|
|     2 |   1,4 |     1 |

Here is SQLFiddle demo

这是SQLFiddle演示

---

You obviously don't necessarily have to use a stored procedure. It's just simplifies things on the calling end. You can do

SET @sql = NULL;

SELECT GROUP_CONCAT(
         CONCAT('(SELECT GROUP_CONCAT(id) FROM Table1 WHERE `', 
                column_name, '` IS NULL ',
                CASE  
                  WHEN data_type IN('varchar', 'char') 
                    THEN CONCAT('OR `', column_name, '` = ''''')
                  WHEN data_type IN('date', 'datetime', 'time') 
                    THEN CONCAT('OR `', column_name, '` = 0')
                  ELSE ''
                END, ')`', column_name, '`'))
   INTO @sql
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_schema = SCHEMA()
   AND table_name = 'table1'
   AND column_name NOT IN ('id')
 GROUP BY table_name;

SET @sql = CONCAT('SELECT ', @sql);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo

这是SQLFiddle演示

---

If for some reason you can't use a version with dynamic SQL, then as a last resort you can produce a query like this on your own including all of your columns

如果由于某种原因您不能使用带有动态SQL的版本,那么作为最后的手段,您可以自己生成这样的查询,包括所有列

SELECT 
(
  SELECT GROUP_CONCAT(id) 
     FROM Table1 
    WHERE `data1` IS NULL 
       OR `data1` = ''
) `data1`,
(
  SELECT GROUP_CONCAT(id) 
    FROM Table1 
   WHERE `data2` IS NULL
) `data2`,
(
  SELECT GROUP_CONCAT(id) 
    FROM Table1 
   WHERE `data3` IS NULL 
      OR `data3` = 0
) `data3`
...

Here is SQLFiddle demo

这是SQLFiddle演示