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
Detailed Analysis of Iframe Usage <iframe frame...
Last week, the teacher gave me a small homework, ...
1. Introduction Image maps allow you to designate...
Table of contents 1. Download 2. Deployment 3. Ng...
Table of contents background What is the Metavers...
Table of contents background Problem Description ...
1. Introduction Presto is an open source distribu...
Table of contents Global Object Global objects an...
translate(-50%,-50%) attributes: Move it up and l...
Table of contents 1. Pull the image 2. Create a l...
Table of contents Preface Option 1: Option 2: Opt...
Docker only maps ports to IPv6 but not to IPv4 St...
Table of contents 1. Directive custom directive 2...
This article shares the specific code of JavaScri...
A style sheet describes how a document should be ...