mysql 求解求2个或以上字段为NULL的记录
核心代码
/*-------------------------------- 求2个或以上字段为NULL的记录 t1: id,id1,id2,id3,id4,id5,id6 在t1表中有个字段; 其中id是主键; 怎样打印其中个字段或以上为NULL的记录id? 另外,存储过程中怎么实现按顺序一条一条读取记录最方便? 注:主键id是没有顺序的,也可能是字符串的; -----------------------------------------*/ droptableifexistst1; createtablet1(idint,id1int,id2int,id3int,id4int,id5int,id6int); insertt1select 1,1,1,1,1,null,nullunionallselect 2,null,null,null,1,2,3unionallselect 3,1,2,3,4,5,6unionallselect 4,1,2,3,4,5,nullunionallselect 5,null,3,4,null,null,null; delimiter$$ createprocedureusp_c_null() begin declaren_cint; declareiddint; declarecurcursorfor selectid,casechar_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,'@')),'@','')) when6then6when5then5when4then4when3then3when2then2when1then1else0endascfromt1; declareexitHANDLERfornotfoundclosecur; opencur; repeat fetchcurintoidd,n_c; if(n_c>=2)then select*fromt1whereid=idd; endif; until0endrepeat; closecur; end; $$ delimiter; /* +------+------+------+------+------+------+------+ |id|id1|id2|id3|id4|id5|id6| +------+------+------+------+------+------+------+ |1|1|1|1|1|NULL|NULL| +------+------+------+------+------+------+------+ 1rowinset(0.10sec) +------+------+------+------+------+------+------+ |id|id1|id2|id3|id4|id5|id6| +------+------+------+------+------+------+------+ |2|NULL|NULL|NULL|1|2|3| +------+------+------+------+------+------+------+ 1rowinset(0.14sec) +------+------+------+------+------+------+------+ |id|id1|id2|id3|id4|id5|id6| +------+------+------+------+------+------+------+ |5|NULL|3|4|NULL|NULL|NULL| +------+------+------+------+------+------+------+ 1rowinset(0.17sec) */
热门推荐
1 训练狗狗憋尿
2 狗狗追踪训练
3 狗狗训练要多少钱
5 深圳狗狗训练
6 训练狗狗唤回
7 狗狗叫怎么训练
8 狗狗训练班
10 狗狗社会化训练
11 狗狗叫回训练
12 广州狗狗训练学校
13 狗狗训练躺下
14 狗狗随行训练
15 训练狗狗巡回
16 狗狗要怎么训练
17 狗狗喂食训练
18 训练狗狗躺下