许久没有写过SQL语句了,连关键词也都忘的一干二净了。
今天做项目 (Heal Your Body)的时候,发现数据显示与iPhone那边显示的不一致。详细检查了自己的sql ,发现sql 书写完全正确,不知道为什么query的时候,漏了两条数据。
通过肉眼观察,这两种数据 完全满足sql里面的条件语句。不知道为什么一直就是查不出来。
完整的sql语句:
select a.id, (select b.problem from bookdata as b where b.id=a.subidof) || ' ' || a.problem as problem from bookdata as a where a.subidof <> '' union all select c.id, c.problem as problem from bookdata as c where c.subidof is null and (c.Cause is not null or c.Cure is not null or c.seeNext is not null ) order by problem asc;
通过拆分sql 语句后意外发现:
select c.id, c.problem as problem from bookdata as c where c.subidof is null and (c.Cause is not null or c.Cure is not null or c.seeNext is not null ) order by problem asc;
此句执行的时候,也查不出那两条数据。
再简化:
select c.id, c.problem as problem from bookdata as c where c.subidof is null ;
发现,还是会漏掉两条数据。 ---- (第65,68条)
再通过 :
select c.id, c.problem as problem from bookdata as c where c.subidof = '';
查出仅的那两种漏掉的数据。
原来 你通过肉眼看到(SQLite Database browser 或者 Sqlite Administer 工具 )的某些字段是空的,其实不一定是空的,它也许 is null, 也许 是 ‘’
soluton: 修改数据库中的‘脏数据’