有时候按某些查询条件进行查询时,可能查到数据库里的有多条记录,记录有新有旧,有完整也有些不太完整的。
新记录与旧记录容易区分,但怎么样去找完整性最高的记录呢?
比如一张contact表中有
contactId,givenName,familyName,nric,gender,mobilePhoneNumber,email,birthDate
这几个字段,用户填写时有些不是必填的字段可能会空着,我们要筛选出完整性最高的记录可以使用下面的语句:
select contactId , if (givenName is null or givenName = '', 0, 1) +
if (familyName is null or familyName = '', 0, 1) +
if (nric is null or nric = '', 0, 1) +
if (gender is null or gender = '', 0, 1) +
if (mobilePhoneNumber is null or mobilePhoneNumber = '', 0, 1) +
if (email is null or email = '', 0, 1) +
if (birthDate is null or birthdate = '', 0, 1) as completeness
from
contact where contactId in (contactid集合)
order by completeness desc, contactId asc
completeness的值越大完整性越高,这里if语句的使用很精彩。记录下来供以后参考。