/*
求2个或以上字段为
NULL
的记录
t1:
id, id1, id2, id3, id4, id5, id6
在t1 表中有个字段;
其中id是主键;
怎样打印其中个字段或以上为
NULL
的记录id?
另外,存储过程中怎么实现按顺序一条一条读取记录最方便?
注:主键id 是没有顺序的,也可能是字符串的;
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)
*/