数据库语句收集

获取每个类别最新记录：

select MAX(car_id) from tbl_carinfo  group by org_id

org_id--类别ID，car_id--主键

mysql 时间格式化函数：

DATE_FORMAT(`oper_date`, '%Y-%m-%d %k:%i:%s')

case when 语句

(CASE field WHEN '1' THEN '1' ELSE '0' END) AS field
(case when money is null then 0 else money end)

时间模糊查询

convert(varchar,edittime,120)   like '%2010%' 

异库转移数据

insert into Handspring.dbo.ClearLog(Uid,AgencyId,ProductId)  select Uid,AgencyId,Amount from ClearLogLog

select ClearLog.Uid, ClearLog.AgencyId,ClearLog.ProductId into ClearLog in (Handspringbz.dbo) from ClearLog

添加表字段

alter table `lore_lab` add fujian varchar(200)
ALTER TABLE smsrecv ADD R3 varchar(50) not null default  0

count()优化：
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择；
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = 'value' 这种查询；
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = 'value' 的出现。

创建触发器包含动态执行语句的内容(sql server 2000)：

CREATE TRIGGER singletri  
ON dbo.tbl_SMSendTask  
INSTEAD OF INSERT 
as

BEGIN
declare @tablename varchar(20)
declare @sqlcom nvarchar(500), @param nvarchar(500)
declare @Daddr varchar(20)
declare @scon varchar(500)
declare @stime datetime

declare @num  int

select  @Daddr=DestAddr,@scon=SM_Content,@stime=SendTime  from inserted


set @tablename='tbl_SMResult_'+SUBSTRING(CONVERT(varchar(100), GETDATE(), 12),3,4)
set @sqlcom='select @a=count(*) from '+@tablename+'  where  DestAddr=@Daddr  and SM_Content=@scon  and DATEDIFF(hh,SendTime,@stime)<=5'
set @param = '@Daddr varchar(20),@scon varchar(500),@stime datetime,@a int output'

exec sp_executesql @sqlcom,@param,@Daddr,@scon,@stime,@num output


 insert into tbl_SMSendTask(CreatorID,SmSendedNum,OperationType,OrgAddr,DestAddr,SM_Content,SendTime,NeedStateReport,ServiceID,FeeType,FeeCode
,SMType,MessageID,DestAddrType,SubTime,TaskStatus,SendLevel,SendState,tryTimes,SendType,taskName) 
 select CreatorID,SmSendedNum,OperationType,OrgAddr,DestAddr,SM_Content,SendTime,NeedStateReport,ServiceID,FeeType,FeeCode
,SMType,MessageID,DestAddrType,SubTime,TaskStatus,SendLevel,SendState,tryTimes,SendType,taskName from inserted ins 
	where @num<=0

insert into sms_history (OrgAddr,DestAddr,SM_Content,SendTime,SubTime,ServiceID) 
select OrgAddr,DestAddr,SM_Content,SendTime,SubTime,ServiceID from Inserted where @num>0

      

END  
GO