推荐订阅源

一、sqlite中不能使用日期进行相减，执行结果无效

　　例如：SELECT count(*) as cnt FROM DayBanalces WHERE (date(ofDay)- date('2013-04-26 00:00:00'))=0          ×

      正确：   SELECT count(*) as cnt FROM DayBanalces WHERE (date(ofDay) = date('2013-04-26 00:00:00'))

二、sqlite中 0 与0.0的差别

　　首先先在数据库中创建1张表及插入少许数据

 1 Create Table ZeroDemo
 2 (
 3     id  int  identity(1,1),--在Sqlite中这句话的写法应该为:id  INTEGER    PRIMARY KEY AUTOINCREMENT  NOT NULL,
 4     num1 numeric(18,10),
 5     num2 numeric(18,10),    
 6     numType int 
 7 ) 
 8 
 9 
10 INSERT INTO ZeroDemo(num1,num2,numType) values(1,10,1)
11 INSERT INTO ZeroDemo(num1,num2,numType) values(2,20,1)
12 INSERT INTO ZeroDemo(num1,num2,numType) values(3,30,1)
13 INSERT INTO ZeroDemo(num1,num2,numType) values(4,40,1)
14 INSERT INTO ZeroDemo(num1,num2,numType) values(5,50,2)
15 INSERT INTO ZeroDemo(num1,num2,numType) values(1,1.19999,1)

通常SqlServer中我们的写法如下：

1 ---在SqlServer中如果某列的类型与所需要的列不一致，而又要统计合计sum值的时候，写法一般如下：
2 SELECT 
3 
4 SUM(     
5     (CASE  numType   WHEN 1 THEN  num1*num2 ELSE 0 end )
6 )  as demosCoumns
7 
8 FROM ZeroDemo

但是在sqlite中特别是操作Sum函数时候，一定要强制转成REAL类型， CAST(num1 as REAL)* CAST(num2 as REAL) ELSE CAST(0 AS REAL)

1 SELECT 
2 
3 SUM(     
4     (CASE  numType   WHEN 1 THEN  CAST(num1 as REAL)* CAST(num2 as REAL) ELSE CAST(0 AS REAL) end )
5 )  as demosCoumns
6 
7 FROM ZeroDemo

或者   0.0

SUM(     
    (CASE  numType   WHEN 1 THEN  num1*num2 ELSE 0.0 end )
)  as demosCoumns

FROM ZeroDemo

以上sqlite在工具中执行的结果可能是一样的，但是如是你用.net调用System.Data.SQLite.dll的时候，如果你依然使用sqlserver的写法，那么可能出来的结果小数位数就会不见。具体的原因参见如下的文章：

http://lvyaojia.sinaapp.com/2012/08/sqlite%E5%AD%A6%E4%B9%A0%E6%89%8B%E5%86%8C%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B-%E8%BD%AC/