准备好循环行的语句

Question

场景：继我之前的问题（在存储过程中使用游标循环行MySQL）之后，我尝试执行一个嵌套的prepare语句，在该语句中，我向外部语句输入一个日期，然后调用内部语句，该语句从表中获取数据。

代码：

-- Create temporary table for the Output:
drop temporary table if exists `stats`;
create temporary table `stats`
(
    col_name varchar(32) null,
    num_nulls int null,
    num_values int null
);


-- Procedure for the check:
drop procedure if exists `set_column_null_stats`;
delimiter $$
create procedure `set_column_null_stats`
(`p_col_name` varchar(128), `wanted_date` date)
begin

-- Set variables:
set @col_nme = `p_col_name`;
set @date1 = `wanted_date`;

prepare stmt from 'insert into `stats` (`col_name`) values (?);';
execute stmt using @col_nme;
deallocate prepare stmt;

-- count number of NULLS based on conditions:
set @sql_txt = concat(
            'update `stats` s join(
            select 
            count(1) as `nb`
            from `btc`
            where`btc`.`date` = ', @date1, ' and `btc`.`', @col_nme, '` is null)
            t set `num_nulls` = t.`nb` where `col_name` = \'', @col_nme, '\';');
prepare stmt from @sql_txt;
execute stmt;
deallocate prepare stmt;

-- count number of not NULLS based on conditions:
set @sql_txt = concat(
           'update `stats` s join(
            select
            count(1) as `nb`
            from `btc`
            where `btc`.`date` = ', @date1, ' and `btc`.`', @col_nme, '` is not null)
            -- t set `num_values` = t.`nb` where `col_name` = \'', @col_nme, '\';');        
set @sql_txt = concat('update `stats` s join (select count(1) as `nb` from `btc` where `', @col_nme, '` is not null) t set `num_values` = t.`nb` where `col_name` = \'', @col_nme, '\';');
prepare stmt from @sql_txt;
execute stmt;
deallocate prepare stmt;

end$$
delimiter ;


-- Procedure for looping through rows of `wanted_columns` table:
delimiter $$
drop procedure if exists `data_check_loop` $$
create procedure `data_check_loop`(`wanted_date` date)
begin

declare dateval date default null;
declare colval text default null;

-- boolean variable to indicate cursor is out of data
declare done tinyint default false;

-- declare a cursor to select the desired columns from the desired source table
declare cursor1
    cursor for
        select *
        from `wanted_columns`; 

-- catch exceptions
        declare continue handler for not found set done = true;

set dateval = `wanted_date`;

-- open the cursor
        open cursor1;
            my_loop: 
            loop
                fetch next from cursor1 into colval;
                if done then 
                    leave my_loop; 
                else  
                    call `set_column_null_stats`(colval, dateval);
                end if;
            end loop;
        close cursor1;

end $$
delimiter ;


-- Start the process with the wanted date:
call `data_check_loop`('2018-08-13');

select * from `stats`;

问题：这段代码运行时没有错误，但没有给出任何结果。如果我只运行第一个准备好的语句，一个接一个地直接输入变量，就可以了。所以我猜问题在于我的第二句话。

问题：你知道我做错了什么吗？

Obs：第二个代码应该循环表中的行（需要的列），并将它们一个接一个地提供给第一个语句（与日期一起，日期应该总是相同的）

Obs2：我的目标与此查询是：从一个表的名称列表为行（id1，date1...）我打算读取每一行，并在另一个表中使用该值，其中名称（id1，date1...）是列，并为我想要的每个列获得NULL值而不是NULL值的总和（另外，考虑到日期输入的另一个约束）。最后，对于我的每个原始行（表1），我将输出一个带有#NULL和#not NULL的新行。

例表1：

Col_names
  Id1
  Name1
  Date1
  Process
  Time
  Class

例表2：

Id1    Name1    Date1    Process    Time    Class
aa     test1    01/01       3       NULL      A
NULL   test2    01/02       4       NULL      b
bb     test3    NULL        3       NULL     NULL

例如：产出：

Col_name    #Null    #notNull
  Id1         1          2
  Name1       0          3
  Date1       1          2 
  Process     0          3
  Time        3          0
  Class       1          2

Answer 1

您要将列转换为记录的接缝结束计数，其中该列的值为null或not null。
将列转换为记录的过程在大多数数据库系统中称为unpivot，这是由<通常情况下，这是通过结合一些聚合函数来完成的，比如MAX（）、MIX（）、SUM（）和代码>COUNT（）和一个CASE END子句来模拟MySQL上的UNPIVOT（）。

查询

SELECT 
   'Id1' AS Col_name
 , SUM(CASE WHEN Table1.Id1 IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Id1 IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Name1' AS Col_name
 , SUM(CASE WHEN Table1.Name1 IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Name1 IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Date1' AS Col_name
 , SUM(CASE WHEN Table1.Date1 IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Date1 IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Process' AS Col_name
 , SUM(CASE WHEN Table1.Process IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Process IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Time' AS Col_name
 , SUM(CASE WHEN Table1.Time IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Time IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Class' AS Col_name
 , SUM(CASE WHEN Table1.Class IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Class IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

后果

| Col_name | #Null | #notNull |
| -------- | ----- | -------- |
| Id1      | 1     | 2        |
| Name1    | 0     | 3        |
| Date1    | 1     | 2        |
| Process  | 0     | 3        |
| Time     | 3     | 0        |
| Class    | 1     | 2        |

演示