Выбор имен столбцов со значениями

Рассмотрим следующую таблицу:

+-----------+---+------+---+
| Telephone | A |  B   | C |
+-----------+---+------+---+
|  12345    | x | NULL | y |
|  32456    | z | NULL | a |
+-----------+---+------+---+

Предполагая, что эта таблица не ограничена 4 столбцами (т. Е. Я не знаю количество столбцов, поскольку оно варьируется), как мне выбрать имена столбцов, которые не равны нулю? (В этом примере я хочу, чтобы результаты были A и C, поскольку они заполнены.)


mysql select notnull
person JetYamato    schedule 17.02.2017    source источник
comment
Такая проблема является признаком плохой конструкции.   -  person Strawberry    schedule 17.02.2017

Ответы (3)


arrow_upward
0
arrow_downward

Если вам нужны имена столбцов, вы можете использовать запрос агрегации с case:

select concat_ws(',',
                 (case when count(telephone) > 0 then 'telephone' end),
                 (case when count(A) > 0 then 'A' end),
                 (case when count(B) > 0 then 'B' end),
                 (case when count(C) > 0 then 'C' end)
                ) as ColumnsWithVowels
from t;
person Gordon Linoff    schedule 17.02.2017
comment
Я мог бы использовать desc test_table, чтобы отобразить все столбцы, а затем использовать текстовый редактор для создания этого запроса. - person JetYamato; 18.02.2017

arrow_upward
0
arrow_downward

Вот в чем хитрость, вам нужно только изменить имя схемы и имя таблицы, и он сгенерирует запрос для проверки этого.

1) выполнить этот запрос

2) выполнить результат как подготовленный оператор

3) выполнить результат

финиш :-)

SELECT
    -- CREATE a SELECT 
    CONCAT("SELECT CONCAT( 'SELECT ', CONCAT_WS(' , ' , "
    -- CONCAT only FIELD WHERE NOT NULL
    , GROUP_CONCAT('IF(Sum(IF(`',ic.COLUMN_NAME,'` IS NOT NULL ,1,0)) > 0,\''
        ,ic.COLUMN_NAME,'\',NULL)  '
        SEPARATOR ',\n'
    )
    , "),' FROM ",ic.TABLE_NAME, "') FROM ",ic.TABLE_NAME) INTO @tmpsql
FROM information_schema.COLUMNS ic
WHERE
    ic.TABLE_SCHEMA = 'test'
AND
    ic.TABLE_NAME = 'test_table' ;

пример таблицы

mysql> SELECT * FROM test_table;
+----+------+------+------+------+------+------+
| id | col1 | col2 | col3 | col4 | col5 | col6 |
+----+------+------+------+------+------+------+
|  1 |    1 |    3 | NULL |    5 |    4 | NULL |
|  2 |    7 |    8 | NULL |    3 |    2 | NULL |
+----+------+------+------+------+------+------+
2 rows in set (0,00 sec)

пример – выполнить запрос и просмотреть результат

mysql>
mysql> SELECT
    ->     -- CREATE a SELECT
    ->     CONCAT("SELECT CONCAT( 'SELECT ', CONCAT_WS(' , ' , "
    ->     -- CONCAT only FIELD WHERE NOT NULL
    ->     , GROUP_CONCAT('IF(Sum(IF(`',ic.COLUMN_NAME,'` IS NOT NULL ,1,0)) > 0,\''
    ->         ,ic.COLUMN_NAME,'\',NULL)  '
    ->         SEPARATOR ',\n'
    ->     )
    ->     , "),' FROM ",ic.TABLE_NAME, "') FROM ",ic.TABLE_NAME) INTO @tmpsql
    -> FROM information_schema.COLUMNS ic
    -> WHERE
    ->     ic.TABLE_SCHEMA = 'test'
    -> AND
    ->     ic.TABLE_NAME = 'test_table' ;
Query OK, 1 row affected (0,00 sec)

mysql>
mysql>
mysql> -- only to verify
mysql> SELECT @tmpsql\G
*************************** 1. row ***************************
@tmpsql: SELECT CONCAT( 'SELECT ', CONCAT_WS(' , ' , IF(Sum(IF(`id` IS NOT NULL ,1,0)) > 0,'id',NULL)  ,
IF(Sum(IF(`col1` IS NOT NULL ,1,0)) > 0,'col1',NULL)  ,
IF(Sum(IF(`col2` IS NOT NULL ,1,0)) > 0,'col2',NULL)  ,
IF(Sum(IF(`col3` IS NOT NULL ,1,0)) > 0,'col3',NULL)  ,
IF(Sum(IF(`col4` IS NOT NULL ,1,0)) > 0,'col4',NULL)  ,
IF(Sum(IF(`col5` IS NOT NULL ,1,0)) > 0,'col5',NULL)  ,
IF(Sum(IF(`col6` IS NOT NULL ,1,0)) > 0,'col6',NULL)  ),' FROM test_table') FROM test_table
1 row in set (0,00 sec)

выполнить подготовленный результат

mysql>
mysql> PREPARE stmt FROM @tmpsql;
Query OK, 0 rows affected (0,00 sec)
Statement prepared

mysql> EXECUTE stmt;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT( 'SELECT ', CONCAT_WS(' , ' , IF(Sum(IF(`id` IS NOT NULL ,1,0)) > 0,'id',NULL)  ,
IF(Sum(IF(`col1` IS NOT NULL ,1,0)) > 0,'col1',NULL)  ,
IF(Sum(IF(`col2` IS NOT NULL ,1,0)) > 0,'col2',NULL)  ,
IF(Sum(IF(`col3` IS NOT NULL ,1,0)) > 0,'col3',NULL)  , |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT id , col1 , col2 , col4 , col5 FROM test_table                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0,00 sec)

это противоречит вашему заявлению

mysql>
mysql> SELECT id , col1 , col2 , col4 , col5 FROM test_table;
+----+------+------+------+------+
| id | col1 | col2 | col4 | col5 |
+----+------+------+------+------+
|  1 |    1 |    3 |    5 |    4 |
|  2 |    7 |    8 |    3 |    2 |
+----+------+------+------+------+
2 rows in set (0,00 sec)

mysql>
person Bernd Buffen    schedule 17.02.2017

arrow_upward
0
arrow_downward

Попробуйте следующие варианты:
1. Процедура сохранения: отбрасывает столбцы NULLS.

DELIMITER $$

    DROP PROCEDURE IF EXISTS `eliminateNull` $$
        CREATE PROCEDURE `eliminateNull`()
        BEGIN

        declare colName_v varchar(20);
        declare done int;
        declare cur1 cursor for 
        SELECT colName,group_concat(value) 
        FROM(SELECT 'telephone' AS ColName, p.telephone  as value FROM `table`  p WHERE p.telephone IS NOT NULL
        UNION
        SELECT 'A' AS ColName FROM `table`  p WHERE p.A IS NOT NULL
        UNION
        SELECT 'B' AS ColName FROM `table`  p WHERE p.B IS NOT NULL
        UNION
        SELECT 'C' AS ColName FROM `table`  p WHERE p.C IS NOT NULL
        )t
       group by t.colName;
       declare continue handler for not found set done=1;

    SET @ddl='';
    set done = 0;
    open cur1;
    igmLoop: loop   
    fetch cur1 into colName_v;
     if done = 1 then leave igmLoop; end if;
     if(@ddl='')THEN
      SET @ddl = CONCAT(@ddl,'p.',colName_v);
      ELSE 
        SET @ddl = CONCAT(@ddl,',p.',colName_v);
        END IF;
      end loop igmLoop;
    close cur1;
      SET @ddl = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS SELECT  ',@ddl, '  FROM `table` p;');

        PREPARE STMT FROM @ddl;
      EXECUTE STMT;  
      DEALLOCATE PREPARE STMT;
         select * from table2;
        DROP TEMPORARY TABLE table2;
END $$
    DELIMITER ;

Вызов процедуры:

call  eliminateNull();

2- Выберите: Отменить нулевые столбцы и сгруппировать результат

SELECT colName,group_concat(value) 
FROM(SELECT 'Telephone' AS ColName, p.telephone  as value FROM table  p WHERE p.telephone IS NOT NULL
UNION
SELECT 'A' AS ColName, p.A  as value FROM table  p WHERE p.A IS NOT NULL
UNION
SELECT 'B' AS ColName, p.B as value FROM table  p WHERE p.B IS NOT NULL
UNION
SELECT 'C' AS ColName, p.C as value FROM table  p WHERE p.C IS NOT NULL
)t
group by t.colName
person Danilo Bustos    schedule 17.02.2017