SQL笔记

相同数据去掉，取时间最近的一条。

SELECT * FROM dis_deliveryman_report WHERE ddid in

(SELECT t.ddid FROM (SELECT ddid=MAX(ddid),createtime=MAX(createtime) FROM dbo.dis_deliveryman_report

GROUP BY usid) as t)

convert(varchar(10),createtime,120)=getdate()

值：yyyy-MM-dd

convert(varchar(20),createtime,120)=getdate()

值：yyyy-MM-dd HH:mm:ss

-- =============================================-- Author:gzb-- Create date: 2012-02-02-- Description:查询某个表的字段名列表-- =============================================ALTER function [dbo].[fn_columnnames](@tablename varchar(20))returns varchar(8000)asbeginDECLARE @str VARCHAR(1000)SET @str=''SELECT @str=@str+','+CAST(name AS VARCHAR) FROM syscolumns WHERE id=object_id(@tablename) SET @str=RIGHT(@str,LEN(@str)-1)RETURN @strEnd

USE [airticket]GO/****** Object:  UserDefinedFunction [dbo].[fn_getdate]    Script Date: 03/26/2012 17:21:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/***生成指定时间段内的随机时间@author zdw*/ALTER function [dbo].[fn_getdate] ( @begin_date DATETIME, @end_date DATETIME)    returns varchar(100)asbegindeclare @second varchar(50)if @begin_date is null SET @begin_date='2012-01-01 08:01:01';if @end_date is null SET @end_date='2012-02-20 17:30:00';SET @second = DATEDIFF ( second , @begin_date,@end_date)declare @d1 datetimedeclare @rand floatselect @rand=re from v_RANDset @d1 = dateadd(second,@rand*@second,@begin_date)if datepart(hour,@d1) >18begin  set @d1=dateadd(hour,-8,@d1)endif datepart(hour,@d1) <8begin  set @d1=dateadd(hour,8,@d1)END    return @d1     end

USE [airticket]GO/****** Object:  UserDefinedFunction [dbo].[fn_getdate]    Script Date: 03/26/2012 17:21:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/***生成指定时间段内的随机时间@author zdw*/ALTER function [dbo].[fn_getdate] ( @begin_date DATETIME, @end_date DATETIME)    returns varchar(100)asbegindeclare @second varchar(50)if @begin_date is null SET @begin_date='2012-01-01 08:01:01';if @end_date is null SET @end_date='2012-02-20 17:30:00';SET @second = DATEDIFF ( second , @begin_date,@end_date)declare @d1 datetimedeclare @rand floatselect @rand=re from v_RANDset @d1 = dateadd(second,@rand*@second,@begin_date)if datepart(hour,@d1) >18begin  set @d1=dateadd(hour,-8,@d1)endif datepart(hour,@d1) <8begin  set @d1=dateadd(hour,8,@d1)END    return @d1     end

USE [airticket]GO/****** Object:  StoredProcedure [dbo].[spGenInsertSQL]    Script Date: 03/26/2012 18:15:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*******自动生成insert sql语句<用于表中已有数据需要导出sql语句>@author zdw@createtime 2011-01-06spGenInsertSQL 'city'@param tablename <表名称>**/ALTER   proc [dbo].[spGenInsertSQL] (@tablename varchar(256))asbegindeclare @sql varchar(8000)declare @sqlValues varchar(8000)set @sql =' ('set @sqlValues = 'values (''+'select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'  from      (select case                when xtype in (48,52,56,59,60,62,104,106,108,122,127)                           then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'                when xtype in (58,61)                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'               when xtype in (167)                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'                when xtype in (231)                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'                when xtype in (175)                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'                when xtype in (239)                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'                else '''NULL'''              end as Cols,name         from syscolumns        where id = object_id(@tablename)      ) Tset @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablenameprint @sqlexec (@sql)end

结果：
INSERT INTO [city] ([ctid],[code],[city]) values ('970af97330a64fe9a50d82136dc01d48','AAT','阿勒泰')
INSERT INTO [city] ([ctid],[code],[city]) values ('2ef25002f1b546c68c805a4c0a24f0c5','AKA','安康')
INSERT INTO [city] ([ctid],[code],[city]) values ('68b1c54610b74ccc89d0bf71a0efd470','AKU','阿克苏')
INSERT INTO [city] ([ctid],[code],[city]) values ('c44501bf510d414cb8136603c32c4669','ALA','阿拉木图')
INSERT INTO [city] ([ctid],[code],[city]) values ('caf6dc69385142f397c4292aeb6f578c','AOG','鞍山')
INSERT INTO [city] ([ctid],[code],[city]) values ('43d000d2b1894e3fb363bd3184081b84','AQG','安庆')
INSERT INTO [city] ([ctid],[code],[city]) values ('a021650dc3664530ae325dad5c52823d','AYN','安阳')
INSERT INTO [city] ([ctid],[code],[city]) values ('e16ec5225a464f7d8586b9a62a124712','BAV','包头')
INSERT INTO [city] ([ctid],[code],[city]) values ('e20bea3d258c4403a4ebc0d8113ed7ca','BFU','蚌埠')
INSERT INTO [city] ([ctid],[code],[city]) values ('67bb203a8b0c4cbd83abbb53c1c5ec78','BHY','北海')