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)
as
begin
	DECLARE @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 @str
	
End

USE [airticket]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getdate]    Script Date: 03/26/2012 17:21:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***
生成指定时间段内的随机时间
@author zdw
*/
ALTER function [dbo].[fn_getdate] 
(
 @begin_date DATETIME,
 @end_date DATETIME
)
    returns varchar(100)
as
begin
declare @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 datetime
declare @rand float
select @rand=re from v_RAND
set @d1 = dateadd(second,@rand*@second,@begin_date)
if datepart(hour,@d1) >18
begin
  set @d1=dateadd(hour,-8,@d1)
end
if datepart(hour,@d1) <8
begin
  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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***
生成指定时间段内的随机时间
@author zdw
*/
ALTER function [dbo].[fn_getdate] 
(
 @begin_date DATETIME,
 @end_date DATETIME
)
    returns varchar(100)
as
begin
declare @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 datetime
declare @rand float
select @rand=re from v_RAND
set @d1 = dateadd(second,@rand*@second,@begin_date)
if datepart(hour,@d1) >18
begin
  set @d1=dateadd(hour,-8,@d1)
end
if datepart(hour,@d1) <8
begin
  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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***
****
自动生成insert sql语句<用于表中已有数据需要导出sql语句>
@author zdw
@createtime 2011-01-06
spGenInsertSQL 'city'
@param tablename <表名称>
**/
ALTER   proc [dbo].[spGenInsertSQL] (@tablename varchar(256))
as
begin
declare @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)
      ) T
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
exec (@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','北海')