lyfz_repos/经典版源码/document/数据库维护脚本/测试语句记录.sql


update dindan set time2='2014-06-28' where id='20140628-007' and isnull(time2,'')<>null
update dindan set time2='2014-06-28' where id='20140628-007' and isnull(time2,0)<>''
update dindan set time2='2014-06-28' where id='20140628-007' and isnull(time2,'')<>''
update dindan set time2='2014-06-28' where id='20140628-007' and isnull(time2,'')=''
update dindan set time2='2014-06-28', status='OK' where id='20140628-007' and time2 is null

/*right*/
update dindan set time2=case when isnull(time2,'')='' then '2014-07-28' else time2 end , name2='OK',time3='2015-01-01',time1='2014-05-30' where id='20140628-007'
update dindan set [status]='OK',[waiter1]='员工001',[waiter2]='',[curno]='12',[time2]=case when isnull([time2],'')='' then '2014-07-29' else [time2] end where id='20140701-002'
update dindan set [time2]=case when isnull([time2],'')='' then '2014-07-28' end ,[status]='OK',[waiter1]='员工001',[waiter2]='',[curno]='12' where id='20140701-002'

update dindan set [status]='OK',[waiter1]='员工001',[waiter2]='',[curno]='12',[time2]=case when [status]<>'OK' then '2018-07-29' else [time2] end where id='20140701-002'
select name1,name2,time1,time2,time3,status from dindan where id='20140701-002'
///////////////////////////////////////////////////////////////////////////////
select name1,name2,time1,time2,time3,waiter1,status from dindan where id='20140601-001'
select * from dindanjd where id='20140601-001'
//////////////////////////////////////////////////////////////
update dindan set [status]=case when [status]<>'OK' then '拍摄中' else [status] end where id = '20140701-002'

update dindanjd set [status]='拍摄中' where id='20140731-001'
update dindanjd set [status]='拍摄中' where id='20140731-001'

select * from takeview where bookingdate>='201-06-01' and bookingdate<='2014-06-01'
select * from dindan time4>='201-06-01' and time4<='2014-06-01'

select * from dindan convert(datetime,'30-03-2011',105)

/*从一个数据库的表复制内容到另一个数据库中*/
insert into [db].[dbo].[dindan] select * from [db].[dbo].[dindan$] 

delete dindan
/*查看某数据库日志*/
select * From ::fn_dblog(default,default)

/*查数据库日志*/
DBCC log(db_db,2)



--设置数据库为单用户：
ALTER DATABASE [dbname] SET SINGLE_USER

--设置数据库为多用户：
ALTER DATABASE [dbname] SET MULTI_USER 

ALTER DATABASE [dbname] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

--DBCC CHECKCATALOG 示例
ALTER DATABASE lyfzdb SET SINGLE_USER
DBCC CHECKDB (lyfzdb, repair_allow_data_loss) with NO_INFOMSGS
--DBCC CHECKCATALOG (lyfzdb, repair_allow_data_loss) with NO_INFOMSGS
 
DBCC CHECKCATALOG ('lyfzdb')
ALTER DATABASE lyfzdb SET MULTI_USER

-- Check the current database.
DBCC CHECKCATALOG;
GO
-- Check the AdventureWorks2012 database.
DBCC CHECKCATALOG (lyfzdb);
GO

DBCC CHECKDB('lyfzdb',REPAIR_ALLOW_DATA_LOSS)

-- 对损坏的表进行修复;
ALTER DATABASE [lyfzdb] SET SINGLE_USER
DBCC CHECKTABLE('dindanjd',REPAIR_REBUILD)
DBCC CHECKTABLE('dindanjd',REPAIR_REBUILD)
DBCC CHECKDB ('dindanjd',Repair_Fast)
-- 如果上面的修复也失败了
DBCC CHECKTABLE('dindanjd',repair_allow_data_loss)

dbcc checkdb('lyfzdb')

alter database [db] set EMERGENCY
alter database [db] set online

use lyfzdb
DBCC CHECKTABLE('dindan')

dbcc checkdb('DBAP',repair_allow_data_loss)

DBCC CHECKCATALOG('lyfzdb')
dbcc checkdb 

DBCC CHECKCATALOG
GO
-- Check the pubs database.
DBCC CHECKCATALOG ('lyfzdb')
GO

DBCC CHECKTABLE('dindanjd',repair_rebuild) with tablock

/*------------------------------------------------------------------------------------------------------------*/
update sendreg set [msgcount] = case when [msgcount]>5 then 11 else [msgcount] end,[content]='sdfd111111111' where [autoid]='94787'
update sendreg set [msgcount] = case when cast([msgcount] as decimal(10,4)) > 10 then str(10) else [msgcount] end,[content]='sdfd' where [autoid]='94787'
update sendreg set [msgcount] = case when convert(decimal(10,4),[msgcount]) > 3 then str(13) else [msgcount] end,[content]='sdfd33333' where [autoid]='94787'

update dindan set curno = '0'

////////////////////////////////////////////////////////////////////////////////
/*-sql判断一个字段是否存在:-*/
if not exists(select name from syscolumns where id=object_id('[tableName]') and name='[columnName]') 
 alter table [tablename] add [columnName] [datatype]

if not exists(select name from syscolumns where id=object_id('[dindan]') and name='[id]') 
	alter table [dindan] add [id] [nvchar](50) NULL)

--判断数据库[TestDB]是否存在
if exists(select name from sysobjects where name='dindan1')
    print 'dindan存在'
else
    print 'dindan不存在'

--判断表[TestTb]是否存在
if not exists(select name from syscolumns where id=object_id('dindan') and name='id')
    print '表字段不存在'
else
    print '表字段存在'

--判断[TestDB]数据中[TestTb]表中是否存在[Name]字段
if exists(select * from syscolumns where id=object_id('lyfzdb.dbo.dindan') and name='id')
    print '字段存在'
else
    print '字段不存在'

insert into [db].[dbo].[dindansp] select * from [db].[dbo].[dindansp$] 

------------------------------------------------------------------------------------
select * from renyuan where bm = '摄影部'
--郭翔龙
--张浩谦
--任永杰
--张庆艺
--赵大永
--行程
select count(*) ct from dindan where waiter1 = '赵大永' and status = 'OK' and time2 >='2014-09-01' and time2<='2014-09-10'
select * from dindan where waiter1 = '郭翔龙' and status = 'OK' and time2 >='2014-09-01' and time2<='2014-09-30'
select * from dindan where waiter1 = '张浩谦' and status = 'OK' and time2 >='2014-09-01' and time2<='2014-09-30'
select * from dindan where waiter1 = '任永杰' and status = 'OK' and time2 >='2014-09-01' and time2<='2014-09-30'
select * from dindan where waiter1 = '张庆艺' and status = 'OK' and time2 >='2014-09-01' and time2<='2014-09-30'
select * from dindan where waiter1 = '赵大永' and status = 'OK' and time2 >='2014-09-01' and time2<='2014-09-30'
select * from dindan where waiter1 = '行程' and status = 'OK' and time2 >='2014-09-01' and time2<='2014-09-30'

select * from dindan where waiter1 = '赵大永' and status = 'OK' and time2 >='2014-09-01' and time2<='2014-09-30'
select * from dindan where waiter1 = '赵大永' and time2 >='2014-09-01' and time2<='2014-09-30'

select * from dindanjd where waiter1 = '赵大永' and status = 'OK' and date >='2014-09-01' and date<='2014-09-30'
select * from dindanjd where waiter1 = '任永杰' and status = 'OK' and date >='2014-09-01' and date<='2014-09-30'
select * from dindanjd where waiter1 = '赵大永' and status = 'OK' and date >='2014-09-01' and date<='2014-09-30'
select * from dindanjd where waiter1 = '赵大永' and status = 'OK' and date >='2014-09-01' and date<='2014-09-30'
select * from dindanjd where waiter1 = '赵大永' and status = 'OK' and date >='2014-09-01' and date<='2014-09-30'

delete dindansp

//添加主键约束	
alter table [shangpin] add constraint PK__shangpin__440B1D61 primary key(spid)

//删除主键约束	
alter table [shangpin] drop constraint PK__shangpin__440B1D61

alter table [shangpin] drop primary key(spid)

alter table [shangpin] drop column spid primary key

alter table [shangpin] drop column spid constraint

alter table [shangpin] drop spid constraint

alter table [shangpin] drop  constraint('spid')

EXEC sp_helpconstraint N'shangpin'

EXEC sp_pkeys @table_name='shangpin'
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin'
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin'

EXEC sp_pkeys @table_name='shangpin'
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin'
select constraint_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin'

if (select constraint_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin') not null

-------------------------------
Declare @FlName varchar(800)
set @FlName=''
set @FlName =(select data_type from information_schema.columns where table_name='tb_ErpOrder' and column_name ='Ord_Number')
if @FlName='nvarchar'
begin
alter table tb_ErpOrder alter column Ord_Number int
end
-------------------------------

-- 删除主键;
declare @key_name nvarchar(200)
set @key_name = (select constraint_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin')
if @key_name <> ''begin
	PRINT'begin'
	set @key_name ='alter table [shangpin] drop constraint ' +(select constraint_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin')
	exec(@key_name)
end
else
PRINT'没有主键'


alter table [shangpin] drop constraint select constraint_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin'

select constraint_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='shangpin'
------------------------------------------------------------------------------
exec sp_helpindex 'shangpin'
exec sp_helpconstraint 'shangpin'
------------------------------------------------------------------------------------------
--主键约束(Primary Key constraint):要求主键列的数据唯一，并且不允许为空。
--唯一约束(Unique Constraint)：要求该列唯一，允许为空，但只能出现一个空值。
--检查约束(Check Constraint):某列取值范围限制、格式限制等，如有关年龄的约束。
--默认约束(Default Constraint):某列的默认值，如我们的男性同学较多，性别默认为男。
--外键约束(Foreign Key):用于在两表之间建立关系需要制定引用主表的哪一列。
 
 
语法如下
 
alter table 表名
add constraint 约束名 约束类型具体的约束说明
 
 
--示例：
--添加主键约束
alter table stuInfo
add constraint PK_stuNo primary key(stuNo)

--添加唯一键约束
alter table stuInfo
add constraint UQ_stuID unique(stuID)

--添加默认约束
alter table stuInfo
add constraint DF_stuAddress default('地址不详') for stuAddress

--添加检查约束
alter table stuInfo
add constraint CK_stuAge check(stuAge between 15 and 40)

--添加外键约束
alter table stuInfo
add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
 
--删除约束
alter table 表名 drop constraint 约束名


--------------------------------------
SELECT owner,constraint_name,constraint_type,table_name,status,deferrable,validated FROM user_constraints ORDER BY 'shangpin'

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[shangpin]') AND name = N'DF_shangpin_spid')	
ALTER TABLE [dbo].[shangpin] DROP CONSTRAINT [DF_shangpin_spid]

alter table [shangpin] add constraint PK_shangpin_ primary key(spid)
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[shangpin]') AND name = N'DF_shangpin_spid'
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[shangpin]') AND name = N'DF_shangpin_spid')	
ALTER TABLE [dbo].[shangpin] DROP CONSTRAINT [DF_shangpin_spid]


---------------------------------------------------
4)删除某字段的约束
declare @name varchar(100)
--DF为约束名称前缀
select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('shangpin') and b.id=a.cdefault and a.name='spid' and b.name like 'DF%'
--删除约束
alter table [shangpin] drop constraint @name
--为字段添加新默认值和约束
ALTER TABLE 表名 ADD CONSTRAINT @name  DEFAULT (0) FOR [字段名]对字段约束进行更改
--删除约束
ALTER TABLE tablename
Drop CONSTRAINT 约束名
--修改表中已经存在的列的属性（不包括约束，但可以为主键或递增或唯一）
ALTER TABLE tablename 
alter column 列名 int not null
--添加列的约束
ALTER TABLE tablename
ADD CONSTRAINT DF_tablename_列名 DEFAULT(0) FOR 列名
--添加范围约束
alter table  tablename  add  check(性别 in ('M','F'))

select b.name from syscolumns a,sysobjects b where a.id=object_id('shangpin') and b.id=a.cdefault and a.name='spid' and b.name like 'DF%'


------------------------
declare @name varchar(100)
--DF为约束名称前缀
select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('shangpin') and b.id=a.cdefault and a.name='spid' and b.name like 'DF%'
--删除约束
if @name <> '' begin
set @name = 'alter table [shangpin] drop constraint ' + (select b.name from syscolumns a,sysobjects b where a.id=object_id('shangpin') and b.id=a.cdefault and a.name='spid' and b.name like 'DF%')
exec (@name)
end

insert into client(id,name1,name2)values('20140930-006',' ','韩昕诺')

韩昕诺

select * from dindanview where name2='韩昕诺'
select * from dindan where name2='韩昕诺'
select * from dindansp where id='20140930-006'
select * from client where id='20140930-006'

update client set id = (select id from dindan)

select id from client where EXISTS(select id from dindan)
select id from dindan where EXISTS(select id from client)
select id from dindan where id != (select id from client)
select dindan.id,dindan.name1,dindan.name2,client.id from dindan FULL OUTER JOIN client on dindan.id = client.id

select * from dindan
select * from client
select * from client where id = '20140930-002'

select id,name1,name2 from dindan where id not in(select id from client)
select count(id) from dindan where id not in(select id from client)

//----------------------------------------------------------------------------
-- 将dindan表id\name1\name2不存在client表里的内容循环插入到client表里。
declare @Myid nvarchar(50)
declare @Myname1 nvarchar(50)
declare @Myname2 nvarchar(50)
declare @MyCount int
select @MyCount=count(id) from dindan where id not in(select id from client)
while @MyCount > 0 begin
select top 1 percent @Myid=id,@Myname1=name1,@Myname2=name2 from dindan where id not in(select id from client)
insert into client(id,name1,name2)values(@Myid,@Myname1,@Myname2)
select @MyCount=count(id) from dindan where id not in(select id from client)
end
//----------------------------------------------------------------------------

alter table [dindan] add [ImageStatus] numeric(6,0) not null default(0)
alter table [dindan] alter column [ImageStatus] numeric(6,0) 
alter table [dindan] drop column [ImageStatus]

dbcc checkdb('db')
dbcc checktable('dindansp')
dbcc checktable('dindansp')
dbcc dbreindex('dindan',' ')
dbcc dbreindex('dindanbukuan2',' ')

--DBCC checktable('dindansp', REPAIR_REBUILD) ALL_ERRORMSGS
ALTER DATABASE [db] SET SINGLE_USER
DBCC checktable('dindansp', REPAIR_REBUILD) with NO_INFOMSGS
ALTER DATABASE [db] SET MULTI_USER 

ALTER DATABASE [db] SET SINGLE_USER
DBCC checktable('dindansp', Repair_Fast) with NO_INFOMSGS
ALTER DATABASE [db] SET MULTI_USER

ALTER DATABASE [db] SET SINGLE_USER
DBCC checktable('dindansp', repair_allow_data_loss) with NO_INFOMSGS
ALTER DATABASE [db] SET MULTI_USER

use master       
declare @databasename varchar(255)       
set @databasename='db'       
exec sp_dboption @databasename, N'single', N'true' --将目标数据库置为单用户状态       
dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)       
dbcc checkdb(@databasename,REPAIR_REBUILD)       
exec sp_dboption @databasename, N'single', N'false'--将目标数据库置为多用户状态  

use master       
declare @databasename varchar(255)       
set @databasename='db'       
--exec sp_dboption @databasename, N'single', N'true' --将目标数据库置为单用户状态      
ALTER DATABASE [db] SET SINGLE_USER 
dbcc checktable('dindansp',REPAIR_ALLOW_DATA_LOSS)       
dbcc checktable('dindansp',REPAIR_REBUILD)       
exec sp_dboption @databasename, N'single', N'false'--将目标数据库置为多用户状态  


数据库被置疑后的解决方法
 
A．我们使用默认方式建立一个供恢复使用的数据库(如test)。可以在SQL Server Enterprise Manager 
 里面建立。 
B．停掉数据库服务器。 
C．将刚才生成的数据库的日志文件test_log.ldf删除，用要恢复的数据库mdf文件覆盖刚才生成的数据 
 库数据文件test_data.mdf。 
D．启动数据库服务器。此时会看到数据库test的状态为"置疑"。这时候不能对此数据库进行任何*作。 
E．设置数据库允许直接*作系统表。此*作可以在SQL Server Enterprise Manager里面选择数据库服 
 务器，按右键，选择"属性"，在"服务器设置"页面中将"允许对系统目录直接修改"一项选中。也可以 
 使用如下语句来实现。 
 use master 
 go 
 sp_configure 'allow updates',1 
 go 
 reconfigure with override 
 go 
F．设置test为紧急修复模式 
 update sysdatabases set status=-32768 where dbid=DB_ID('db') 
 此时可以在SQL Server Enterprise Manager里面看到该数据库处于"只读\置疑\脱机\紧急模式"可以 
 看到数据库里面的表，但是仅仅有系统表 
G．下面执行真正的恢复*作，重建数据库日志文件 
 dbcc rebuild_log('test','C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.ldf') 
 执行过程中，如果遇到下列提示信息： 
 
 服务器: 消息 5030，级别 16，状态 1，行 1 
 未能排它地锁定数据库以执行该*作。 
 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 
 
 说明您的其他程序正在使用该数据库，如果刚才您在F步骤中使用SQL Server Enterprise Manager 
 打开了test库的系统表，那么退出SQL Server Enterprise Manager就可以了。 
  
 正确执行完成的提示应该类似于： 
 
 警告: 数据库 'test' 的日志已重建。已失去事务的一致性。应运行 DBCC CHECKDB 以验证物理一致 
 性。将必须重置数据库选项，并且可能需要删除多余的日志文件。 
 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 
  
 此时打开在SQL Server Enterprise Manager里面会看到数据库的状态为"只供DBO使用"。此时可以 
 访问数据库里面的用户表了。 
H．验证数据库一致性（可省略） 
 dbcc checkdb('test') 
  
 一般执行结果如下： 
  
 CHECKDB 发现了 0 个分配错误和 0 个一致性错误（在数据库 'test' 中）。 
 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 
  
I．设置数据库为正常状态 
 sp_dboption 'test','dbo use only','false' 
 如果没有出错，那么恭喜，现在就可以正常的使用恢复后的数据库啦。 
J．最后一步，我们要将步骤E中设置的"允许对系统目录直接修改"一项恢复。因为平时直接*作系统表 
 是一件比较危险的事情。当然，我们可以在SQL Server Enterprise Manager里面恢复，也可以使用 
 如下语句完成 
 sp_configure 'allow updates',0 
 go 
 reconfigure with override 
 go 
 
 
--数据库（如test) 分离后再附加  
 
EXEC sp_detach_db 'test' 
 
EXEC sp_attach_db 'test','c:\Program Files\Microsoft SQL Server\MSSQL\Data\test.mdf','c:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.ldf'


/*从一个数据库的表复制内容到另一个数据库中*/
set IDENTITY_INSERT [dindansp] on
set IDENTITY_INSERT [dindansp$] on
insert into [db].[dbo].[dindansp] select * from [db].[dbo].[dindansp$] 

dbcc checktable('dindan')

select count(*) as cot from client3 where (birthday like '%s-11-27%s' or birthday = '2014-08-21') and check1=01'

-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
-----------------儿童版本：
dindanclient:生日前n天 和 百天前n天
((birthday1 like '%%s%' or birthday1 = '%s') and check1='0') or ((birthday1 like '%%s%' or birthday1 = '%s') and check1='1')

--client2:生日前n天 和 百天前n天
((birthday like '%%s%' or birthday = '%s') and check1='0') or ((birthday like '%%s%' or birthday = '%s') and check1='1')

--client3:生日前n天 和 百天前n天
((birthday like '%%s%' or birthday = '%s') and check1='0') or ((birthday like '%%s%' or birthday = '%s') and check1='1')

-----------------婚纱版本：
dindanclient:
生日前n天
(birthday1 like '%%s%' and check1='0') or (birthday1 like '%%s%' and check1='1')
(birthday2 like '%%s%' and check2='0') or (birthday2 like '%%s%' and check2='1')
纪念日前n天
(time3 like '%%s%' and check3='0') or (time3 like '%%s%' and check3='1')
结婚当天
(time3 = '%s' and check3='0') or (time3 = '%s' and check3='1')

--client2:生日前n天
(birthday like '%%s%' and check1='0') or (birthday like '%%s%' and check1='1')

--client3:生日前n天 和 百天前n天
生日前n天
(birthday like '%%s%' and check1='0') or (birthday like '%%s%' and check1='1')
(birthday2 like '%%s%' and check2='0') or (birthday2 like '%%s%' and check2='1')
纪念日前n天
(time3 like '%%s%' and check3='0') or (time3 like '%%s%' and check3='1')
结婚当天
(time3 = '%s' and check3='0') or (time3 = '%s' and check3='1')

-----------------婚纱版本：
dindanclient:
生日前n天+纪念日前n天+结婚当天
(birthday1 like '%%s%' and check1='0') or (birthday1 like '%%s%' and check1='1') or (birthday2 like '%%s%' and check2='0') or (birthday2 like '%%s%' and check2='1') or (time3 like '%%s%' and check3='0') or (time3 like '%%s%' and check3='1') or (time3 = '%s' and check3='0') or (time3 = '%s' and check3='1')

--client2:生日前n天
(birthday like '%%s%' and check1='0') or (birthday like '%%s%' and check1='1')

--client3:
生日前n天+纪念日前n天+结婚当天
(birthday like '%%s%' and check1='0') or (birthday like '%%s%' and check1='1') or (birthday2 like '%%s%' and check2='0') or (birthday2 like '%%s%' and check2='1') or (time3 like '%%s%' and check3='0') or (time3 like '%%s%' and check3='1') or (time3 = '%s' and check3='0') or (time3 = '%s' and check3='1')

//-------------------------------------------------------------------------------------------------
// 当hospitalmsgcheck==1时;查询hospitalclient表里的宝宝生日和百天、满n天;
生日前n天公历
birthdaytped ='0' and (birthday like '%%%s%%' and check1='0') or (birthday like '%%%s%%' and check1='1') 
百天前n天生日
birthdaytped ='0' and (birthday ='%s' and check1='0') or (birthday ='%s' and check1='1')
满n天
birthdaytped ='0' and (birthday ='%s' and check1='0') or (birthday ='%s' and check1='1')

综合全部：birthdaytped ='0' and ((birthday like '%%%s%%' and check1='0') or (birthday like '%%%s%%' and check1='1')) or ((birthday ='%s' and check1='0') or (birthday ='%s' and check1='1')) or ((birthday ='%s' and check1='0') or (birthday ='%s' and check1='1'))

综合全部2：
birthdaytped ='0' and ((birthday like '%%%s%%' or birthday ='%s') and check1='0') or ((birthday like '%%%s%%' or birthday ='%s') and check1='1')

生日+满n天：birthdaytped ='0' and ((birthday like '%%%s%%' and check1='0') or (birthday like '%%%s%%' and check1='1')) or ((birthday ='%s' and check1='0') or (birthday ='%s' and check1='1'))

百天+满n天:birthdaytped ='0' and ((birthday ='%s' and check1='0') or (birthday ='%s' and check1='1')) or ((birthday ='%s' and check1='0') or (birthday ='%s' and check1='1'))

只有满n天：(birthday ='%s' and check1='0') or (birthday ='%s' and check1='1')

//--------------------------------------------------------------------------------------------------------------------
// 当hospitalcheck2==1时;查询hospitalclient表里怀孕满n天.
// 满足今天为怀孕满n天的日期条件：Today = Pregnant + n天;
// Pregnant = Today - n;
// 而数据库没有Pregnant这个直接记录,Pregnant = 来单日期 - 已怀天数;
// Today - n = orderDate - HasPregnantDays;
// 已怀孕天数无法提前获取,只有使用来单日期判断个大概（HasPregnantDays <= n）。
// orderDate = Today - (n - HasPregnantDays) > Today - n
// 所以只要判断出 orderDate > Today - n 成立的日期。
// 只有公历订单日期;预产期不需要理会。
//--------------------------------------------------------------------------------------------------------------------
%s = Today - n
birthdaytped ='1' and date1 > '%s'


update dindan set status3='OK'