-- 1、空值 SELECT rowid com_id, customer_id, corp_sim_name, ( case when tel_no is NULL then '' else ltrim(rtrim(tel_no)) end ) as tel_no, strftime('%Y.%m.%d %H:%M:%f',mainten_datetime,'localtime') as mainten_datetime,mainten_datetime as mainten_datetime1 FROM Sdf00504 where (tel_no is NULL) and strftime('%Y.%m.%d %H:%M:%f',mainten_datetime,'localtime') <=strftime('%Y.%m.%d %H:%M:%f','2018-05-28 10:09:11.000','localtime');
-- 2、时间截 -- 2.1、Sqlite3 select strftime('%Y.%m.%d %H:%M:%f','now','localtime') from Sdf00504; -- localtime ：兼容IOS下，本地时间的时差同步 ，now :数据库前时间，注意：引号 select strftime('%Y.%m.%d %H:%M:%f',mainten_datetime,'localtime'),corp_sim_name, ( case when tel_no is NULL then '' else ltrim(rtrim(tel_no)) end ) as tel_no from Sdf00504 -- mainten_datetime ：表字段，不用引号 where (tel_no is NULL) and strftime('%Y.%m.%d %H:%M:%f',mainten_datetime,'localtime') <=strftime('%Y.%m.%d %H:%M:%f','2018-05-28 10:09:11.000','localtime');-- 2.2、ms sql server select tel_no,corp_sim_name, ( case when tel_no is NULL then '' else ltrim(rtrim(tel_no)) end ) as tel_no from Sdf00504 where (tel_no is NULL) and convert(varchar(23),mainten_datetime,121) <='2018-05-28 10:09:11.000'
-- 3、查询结果咧：增加行序号：Sqlite3自带默认 rowid 列，ms sql server需要使用临时表 -- 3.1、Sqlite3 select rowid, com_id,ivt_oper_listing,sd_date,BillDate, ( case when store_struct_id is NULL then '' else ltrim(rtrim(store_struct_id)) end ) as store_struct_id,clerk_id, CashAmount,i_AMount,RealAmount,ChangeAmount,sumoq, c_memo,corp_name,tel_no,dept_id,comfirm_flag from pos_bill
-- 3.2、ms sql server-- 创建临时表： select identity(int,1,1) as rowid, com_id,ivt_oper_listing,sd_date,BillDate, ( case when store_struct_id is NULL then '' else ltrim(rtrim(store_struct_id)) end ) as store_struct_id,clerk_id, CashAmount,i_AMount,RealAmount,ChangeAmount,sumoq, c_memo,corp_name,tel_no,dept_id,comfirm_flag into #temp83110089609817889975420987085 from PosBill where (1=1) and (convert(varchar(23),sd_date,121)>='2016-01-23 08:00:00.000') and (convert(varchar(23),sd_date,121) <'2016-01-23 10:00:00.000')-- 查询临时表： select rowid, com_id,ivt_oper_listing,sd_date,BillDate, ( case when store_struct_id is NULL then '' else ltrim(rtrim(store_struct_id)) end ) as store_struct_id,clerk_id, CashAmount,i_AMount,RealAmount,ChangeAmount,sumoq, c_memo,corp_name,tel_no,dept_id,comfirm_flag from #temp83110089609817889975420987085 where (1=1) and (convert(varchar(23),sd_date,121)>='2016-01-23 08:00:00.000') and (convert(varchar(23),sd_date,121) <'2016-01-23 10:00:00.000') and (rowid>0) and (rowid<=800) -- 用完后，及时删除临时表： drop table #temp83110089609817889975420987085
-- 4、自增长字段及主键设置： -- 4.1、Sqlite3 ： /* Disable foreign keys */ PRAGMA foreign_keys = 'off'; /* Database properties */ PRAGMA auto_vacuum = 0; PRAGMA encoding = 'UTF-8'; PRAGMA page_size = 4096; /* Drop table [CtL00001] */ DROP TABLE IF EXISTS [main].[CtL00001]; /* Table structure [CtL00001] */ CREATE TABLE [main].[CtL00001]( [id] "[int] IDENTITY"(1, 1) NOT NULL, [c_tablename] varchar(300) PRIMARY KEY ASC ON CONFLICT ABORT NOT NULL, -- 主键 [c_tableTitle] varchar(300), [c_type] char(1), [c_use] char(1), [LastTime_UpdateData] datetime, [LastTime_Product] datetime); /* Enable foreign keys */ PRAGMA foreign_keys = 'on';
-- 4.2、ms sql server if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CtL00001]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[CtL00001] GO CREATE TABLE [dbo].[CtL00001] ( [id] [int] IDENTITY (1, 1) NOT NULL , [c_tablename] [varchar] (300) COLLATE Chinese_PRC_CI_AS NOT NULL , [c_tableTitle] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL , [c_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [c_use] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [LastTime_UpdateData] [datetime] NULL , [LastTime_UpdateStruct] [datetime] NULL , [LastTime_Product] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[CtL00001] WITH NOCHECK ADD CONSTRAINT [PK_CtL00001] PRIMARY KEY CLUSTERED ( [c_tablename] ) ON [PRIMARY] GO
