Core code /*-------------------------------- Find records t1 where two or more fields are NULL: id, id1, id2, id3, id4, id5, id6 In the t1 table there is a field; Where id is the primary key; How to print the record ids where one or more of the fields are NULL? In addition, what is the most convenient way to read records one by one in sequence during the storage process? Note: The primary key id has no order and may also be a string; -----------------------------------------*/ drop table if exists t1; create table t1(id int,id1 int,id2 int,id3 int,id4 int,id5 int,id6 int); insert t1 select 1,1,1,1,1,null,null union all select 2,null,null,null,1,2,3 union all select 3,1,2,3,4,5,6 union all select 4,1,2,3,4,5,null union all select 5,null,3,4,null,null,null ; delimiter $$ create procedure usp_c_null() begin declare n_c int; declare idd int; declare cur cursor for select id,case char_length(concat(ifnull(id1,'@'),ifnull(id2,'@'),ifnull(id3,'@'),ifnull(id4,'@'),ifnull(id5,'@'),ifnull(id6,'@'))) - char_length(replace(concat(ifnull(id1,'@'),ifnull(id2,'@'),ifnull(id3,'@'),ifnull(id4,'@'),ifnull(id5,'@'),ifnull(id6,'@')),'@','') ) when 6 then 6 when 5 then 5 when 4 then 4 when 3 then 3 when 2 then 2 when 1 then 1 else 0 end as c from t1; declare exit HANDLER for not found close cur ; open cur; repeat fetch cur into idd,n_c; if(n_c>=2) then select * from t1 where id=idd; end if ; until 0 end repeat; close cur; end ; $$ delimiter ; /* +------+------+------+------+------+------+------+ | id | id1 | id2 | id3 | id4 | id5 | id6 | +------+------+------+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | NULL | NULL | +------+------+------+------+------+------+------+ 1 row in set (0.10 sec) +------+------+------+------+------+------+------+ | id | id1 | id2 | id3 | id4 | id5 | id6 | +------+------+------+------+------+------+------+ | 2 | NULL | NULL | NULL | 1 | 2 | 3 | +------+------+------+------+------+------+------+ 1 row in set (0.14 sec) +------+------+------+------+------+------+------+ | id | id1 | id2 | id3 | id4 | id5 | id6 | +------+------+------+------+------+------+------+ | 5 | NULL | 3 | 4 | NULL | NULL | NULL | +------+------+------+------+------+------+------+ 1 row in set (0.17 sec) */ You may also be interested in:
|
<<: Detailed explanation of for loop and double for loop in JavaScript
>>: Detailed explanation of common Docker Compose commands
I have been learning about responsive design rece...
Table of contents 1. Installation 2. Import 3. De...
Table of contents 1. Introduction 2. Interface 3....
Written in front I have been writing a special to...
Alignment issues like type="radio" and t...
Encapsulate el-dialog as a component When we use ...
Preface There are many devices nowadays, includin...
1. Create the backup.sh script file #!/bin/sh SOU...
Table of contents 1. The concept of process and t...
Table of contents 1. some 2. every 3. find 1. som...
Preface I once encountered a difficult problem. I...
Windows: Support NTFS, FAT Linux supports file fo...
iOS 1. URL scheme This solution is basically for ...
This article shares two methods of implementing t...
Overview of MySQL MySQL is a relational database ...