许久没有写过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:修改数据库中的‘脏数据’
分享到:
相关推荐
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS ...
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...
字段说明=isnull(g.[value],''), --标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( ...
sql语句美化器,格式化sql语句 update a set a.ID = b.ID, Name = b.Name from Table1 a inner join ( select ID, Name from Table2 where Active <> 0 and Current <> 0 ) b on a.ownerid = b....
store_name is not NULL -- 统计非空 SELECT COUNT(DISTINCT store_name) FROM Store_Information -- 统计多 少个不同 (11) Group By .................................. .............. ............................
1.3 DM_SQL 语言的功能及语句 .........................................................................................2 1.4 DM_SQL 所支持的数据类型 ........................................................
实现sql语句1: 代码如下:UPDATE file_manager_folder f1LEFT OUTER JOIN file_manager_folder f2 ON f1.name = f2.name AND f2.parentId = 54SET f1.parentId = 54 WHERE f2.name IS NULL AND f1.id IN (1,2,3);...
SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩 ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 FROM SC T,Course where t.C#=...
--查看进程所执行的SQL语句 if (select COUNT(*) from master.dbo.sysprocesses) > 500 begin select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses a end ...
//创建SQL语句使用内联接连接条件为图书类型编号,查询条件为符合图书条形码的记录 string sql = "select * from tb_bookInfo as a inner join tb_bookType as b on a.bookType=typeID where a.bookBarCode='" + ...
构造sql语句是 比起来 复制代码 代码如下: $sql = ‘SELECT * FROM sdb_comments WHERE goods_id = ‘.intval($goods_id).’ AND for_comment_id IS NULL AND object_type = “.$item.” AND disabled=”false” AND...
精心收集整理的各种SQL语句示例,帮助您更加容易的熟悉和使SQL。 1 DECLARE @local_variable 1.1 使用 DECLARE 以下示例将使用名为 @find 的局部变量检索所有姓氏以“Man”开头的联系人信息。 USE AdventureWorks;...
select s.name + '-' + c.name from classes c, student s where s.cid = c.id; --where 条件 select * from student where id = 2; select * from student where id > 7; select * from student where id ; ...
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...
经典SQL语句大全 一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice ...
Public str1 As String '保存修改时的SQL语句 Public ID As Integer '保存记录编号 Private baddflag As Boolean rivate Sub AID_KeyDown(KeyCode As Integer, Shift As Integer) TabToEnter KeyCode End Sub ...
PL/SQL 基础,一个不错的 PL/SQL 参考手册。内容预览: ---- 第一章 PL/SQL 简介 ---- 1. Oracle应用编辑方法概览 1) Pro*C/C++/... : C语言和数据库打交道的方法,比OCI更常用; 2) ODBC 3) OCI: C语言和...
2004.03.11 22:02:00 改正更新时出现空值出错问题,新填函数isnull 2004.03.12 13:22:08 完成关键字高亮显示 高亮显示函数 mykeywords 2004.03.12 22:08:20 加强了高亮显示函数 mykeywords的功能,使其能识别不同...
//根据用户分组会话 select t.USERNAME,count(*) from v$session t ...//查找对应sql_id的对应sql语句 select m.SQL_TEXT from v$session t , v$sqlarea m where t.USERNAME ='BUDGET' and t.sql_id = m.SQL_ID // s
SQL常用语句最好用的 --在示例数据库Northwind中,执行数据查询,要求如下: use Northwind go --1、在”Suppliers”表中查询,得到不同的地区有多少家供应商的信息; select sum( SupplierID) 总数,Country 国家 ...