从表中选择具有空/空值仅列(Select only columns with null/empty

我有一个数据集，看起来像这样，但列的推移作为数据4，数据5多达资料20

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

我只是想选择一个与空或空值的列，因此客户可以决定需要哪些领域在他们的票要填充的行。

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

或者，如果有人能想到更好的变化？我愿意在只要此刻什么，因为它是做我想要的工作。这甚至可能吗？谢谢！

Answer 1:

这很可能是考虑到你有最多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

Answer 2:

你可以用这样的动态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 ;

请注意，它需要不同的数据类型的列的照顾。

用法示例：

CALL sp_empty();

输出：

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

这里是SQLFiddle演示

显然，你不一定必须使用存储过程。这只是简化的主叫端的东西。你可以做

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;

这里是SQLFiddle演示

如果由于某种原因，你不能使用动态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`
...

这里是SQLFiddle演示