sqlite中常见的问题总结

 

一、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张表及插入少许数据

　　

Create Table ZeroDemo
(
    id  int  identity(1,1),--在Sqlite中这句话的写法应该为:id  INTEGER    PRIMARY KEY AUTOINCREMENT  NOT NULL,
    num1 numeric(18,10),
    num2 numeric(18,10),    
    numType int 
) 


INSERT INTO ZeroDemo(num1,num2,numType) values(1,10,1)
INSERT INTO ZeroDemo(num1,num2,numType) values(2,20,1)
INSERT INTO ZeroDemo(num1,num2,numType) values(3,30,1)
INSERT INTO ZeroDemo(num1,num2,numType) values(4,40,1)
INSERT INTO ZeroDemo(num1,num2,numType) values(5,50,2)
INSERT INTO ZeroDemo(num1,num2,numType) values(1,1.19999,1)

 

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

---在SqlServer中如果某列的类型与所需要的列不一致，而又要统计合计sum值的时候，写法一般如下：
SELECT 

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

FROM ZeroDemo

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

SELECT 

SUM(     
    (CASE  numType   WHEN 1 THEN  CAST(num1 as REAL)* CAST(num2 as REAL) ELSE CAST(0 AS REAL) end )
)  as demosCoumns

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/