declare @sql varchar(4000) set @sql='' select @sql=@sql+'union all select ['+a.name+'] as xdate from 表名 ' +CHAR(10) from syscolumns a inner join systypes b on a.xtype=b.xtype where id=OBJECT_ID('表名') and (b.name='datetime' or b.name='date')set @sql=STUFF(@sql,1,10,'') print @sql exec ('select min(xdate) as xdate from ('+@sql+')t ')
用动态的语句就可以: create table a(a date,b date,c date,d date)insert into a select '2012-01-09','2013-05-10','2008-10-01','2003-08-09' union all select '2012-01-10','2013-06-09','2006-10-01','2002-08-09' union all select '2012-01-23','2013-12-09','2005-10-01','2001-08-09' union all select '1990-01-11','2013-11-09','2004-10-01','1998-08-09' declare @sql nvarchar(max) declare @tb_name nvarchar(100)set @sql = '' set @tb_name = 'a' select @sql = @sql + ' union all select ' + c.name + ' as xx' + ' from ' + @tb_name from sys.tables t inner join sys.columns c on t.object_id = c.object_id where t.name = @tb_name --and c.name like 'num%'select @sql = 'select min(xx) as min_date from (' + stuff(@sql,1,len(' union all'),'') + ' )tb' --动态生成的语句 select @sql /* select min(xx) as min_date from ( select a as xx from a union all select b as xx from a union all select c as xx from a union all select d as xx from a )tb */exec(@sql) /* min_date 1990-01-11 */
stuff这个函数貌似是要用得。
create table a(a date,b date,c date,d date)insert into a select '2012-01-09','2013-05-10','2008-10-01','2003-08-09' union all select '2012-01-10','2013-06-09','2006-10-01','2002-08-09' union all select '2012-01-23','2013-12-09','2005-10-01','2001-08-09' union all select '1990-01-11','2013-11-09','2004-10-01','1998-08-09' declare @sql nvarchar(max) declare @tb_name nvarchar(100)set @sql = '' set @tb_name = 'a'select @sql = @sql + ' union all select ''' + c.name + ''' as columnName,'+c.name+' as xx' + ' from ' + @tb_name from sys.tables t inner join sys.columns c on t.object_id = c.object_id where t.name = @tb_nameselect @sql = 'select columnName,xx as minDate from( select columnName,xx,row_number() over(order by xx) rn from (' +stuff(@sql,1,len(' union all'),'') + ' )tb) t1 where rn=1';--动态生成的语句 select @sql exec(@sql) /* columnName minDate a 1990-01-11 */在5楼的基础上修改出来的
在原来的基础上修改了一下: drop table acreate table a(a date,b date,c date,d date)insert into a select '2012-01-09','2013-05-10','2008-10-01','2003-08-09' union all select '2012-01-10','2013-06-09','2006-10-01','2002-08-09' union all select '2012-01-23','2013-12-09','2005-10-01','2001-08-09' union all select '1990-01-11','2013-11-09','2004-10-01','1998-08-09' declare @sql nvarchar(max) declare @tb_name nvarchar(100)set @sql = '' set @tb_name = 'a' select @sql = @sql + ' union all select '''+c.name +''' as col,' + c.name + ' as xx' + ' from ' + @tb_name from sys.tables t inner join sys.columns c on t.object_id = c.object_id where t.name = @tb_name --and c.name like 'num%'select @sql = 'select col,xx from(select col,xx,row_number() over(order by xx) as rownum from (' + stuff(@sql,1,len(' union all'),'') + ' )tb)tb where rownum = 1' --动态生成的语句 select @sql /* select col,xx from ( select col,xx,row_number() over(order by xx) as rownum from ( select 'a' as col,a as xx from a union all select 'b' as col,b as xx from a union all select 'c' as col,c as xx from a union all select 'd' as col,d as xx from a )tb )tb where rownum = 1 */exec(@sql) /* col xx a 1990-01-11 */
我这个表不止有时间类型的数据。还有好多其他类型的字段。用动态sql sys.columns c 这里面有好多其他字段。怎么办。
在原来的基础上修改了一下: drop table acreate table a(a date,b date,c date,d date)insert into a select '2012-01-09','2013-05-10','2008-10-01','2003-08-09' union all select '2012-01-10','2013-06-09','2006-10-01','2002-08-09' union all select '2012-01-23','2013-12-09','2005-10-01','2001-08-09' union all select '1990-01-11','2013-11-09','2004-10-01','1998-08-09' declare @sql nvarchar(max) declare @tb_name nvarchar(100)set @sql = '' set @tb_name = 'a' select @sql = @sql + ' union all select '''+c.name +''' as col,' + c.name + ' as xx' + ' from ' + @tb_name from sys.tables t inner join sys.columns c on t.object_id = c.object_id where t.name = @tb_name --and c.name like 'num%'select @sql = 'select col,xx from(select col,xx,row_number() over(order by xx) as rownum from (' + stuff(@sql,1,len(' union all'),'') + ' )tb)tb where rownum = 1' --动态生成的语句 select @sql /* select col,xx from ( select col,xx,row_number() over(order by xx) as rownum from ( select 'a' as col,a as xx from a union all select 'b' as col,b as xx from a union all select 'c' as col,c as xx from a union all select 'd' as col,d as xx from a )tb )tb where rownum = 1 */exec(@sql) /* col xx a 1990-01-11 */ 不同类型数据怎么求最小
改了一下,新增了一个非日期字段xxx,会自动过滤这个非日期字段:drop table acreate table a (a date,b date, xxx int, --增加了非日期字段 c date,d date)insert into a select '2012-01-09','2013-05-10',1,'2008-10-01','2003-08-09' union all select '2012-01-10','2013-06-09',2,'2006-10-01','2002-08-09' union all select '2012-01-23','2013-12-09',3,'2005-10-01','2001-08-09' union all select '1990-01-11','2013-11-09',4,'2004-10-01','1998-08-09' declare @sql nvarchar(max) declare @tb_name nvarchar(100)set @sql = '' set @tb_name = 'a' select @sql = @sql + ' union all select '''+c.name +''' as col,' + c.name + ' as xx' + ' from ' + @tb_name from sys.tables t inner join sys.columns c on t.object_id = c.object_id inner join sys.types tp on c.system_type_id = tp.system_type_id and c.user_type_id = tp.user_type_id where t.name = @tb_name and (tp.name like '%date%' or tp.name like '%time%') --只包含日期 --and c.name like 'num%' select @sql = 'select col,xx from(select col,xx,row_number() over(order by xx) as rownum from (' + stuff(@sql,1,len(' union all'),'') + ' )tb)tb where rownum = 1' --动态生成的语句 select @sql /* select col,xx from ( select col,xx,row_number() over(order by xx) as rownum from ( select 'a' as col,a as xx from a union all select 'b' as col,b as xx from a union all select 'c' as col,c as xx from a union all select 'd' as col,d as xx from a )tb )tb where rownum = 1 */exec(@sql) /* col xx a 1990-01-11 */
set @sql=''
select @sql=@sql+'union all select ['+a.name+'] as xdate from 表名 ' +CHAR(10)
from syscolumns a
inner join systypes b on a.xtype=b.xtype
where id=OBJECT_ID('表名') and (b.name='datetime' or b.name='date')set @sql=STUFF(@sql,1,10,'')
print @sql
exec ('select min(xdate) as xdate from ('+@sql+')t ')
create table a(a date,b date,c date,d date)insert into a
select '2012-01-09','2013-05-10','2008-10-01','2003-08-09' union all
select '2012-01-10','2013-06-09','2006-10-01','2002-08-09' union all
select '2012-01-23','2013-12-09','2005-10-01','2001-08-09' union all
select '1990-01-11','2013-11-09','2004-10-01','1998-08-09'
declare @sql nvarchar(max)
declare @tb_name nvarchar(100)set @sql = ''
set @tb_name = 'a'
select @sql = @sql + ' union all select ' + c.name + ' as xx' +
' from ' + @tb_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = @tb_name
--and c.name like 'num%'select @sql = 'select min(xx) as min_date from (' +
stuff(@sql,1,len(' union all'),'') + ' )tb'
--动态生成的语句
select @sql
/*
select min(xx) as min_date
from
(
select a as xx from a union all
select b as xx from a union all
select c as xx from a union all
select d as xx from a
)tb
*/exec(@sql)
/*
min_date
1990-01-11
*/
create table a(a date,b date,c date,d date)insert into a
select '2012-01-09','2013-05-10','2008-10-01','2003-08-09' union all
select '2012-01-10','2013-06-09','2006-10-01','2002-08-09' union all
select '2012-01-23','2013-12-09','2005-10-01','2001-08-09' union all
select '1990-01-11','2013-11-09','2004-10-01','1998-08-09'
declare @sql nvarchar(max)
declare @tb_name nvarchar(100)set @sql = ''
set @tb_name = 'a'select @sql = @sql + ' union all select ''' + c.name + ''' as columnName,'+c.name+' as xx' +
' from ' + @tb_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = @tb_nameselect @sql = 'select columnName,xx as minDate from( select columnName,xx,row_number() over(order by xx) rn from ('
+stuff(@sql,1,len(' union all'),'') + ' )tb) t1 where rn=1';--动态生成的语句
select @sql exec(@sql)
/*
columnName minDate
a 1990-01-11
*/在5楼的基础上修改出来的
在原来的基础上修改了一下:
drop table acreate table a(a date,b date,c date,d date)insert into a
select '2012-01-09','2013-05-10','2008-10-01','2003-08-09' union all
select '2012-01-10','2013-06-09','2006-10-01','2002-08-09' union all
select '2012-01-23','2013-12-09','2005-10-01','2001-08-09' union all
select '1990-01-11','2013-11-09','2004-10-01','1998-08-09'
declare @sql nvarchar(max)
declare @tb_name nvarchar(100)set @sql = ''
set @tb_name = 'a'
select @sql = @sql + ' union all select '''+c.name +''' as col,' +
c.name + ' as xx' + ' from ' + @tb_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = @tb_name
--and c.name like 'num%'select @sql = 'select col,xx from(select col,xx,row_number() over(order by xx) as rownum from (' +
stuff(@sql,1,len(' union all'),'') + ' )tb)tb where rownum = 1'
--动态生成的语句
select @sql
/*
select col,xx
from
(
select col,xx,row_number() over(order by xx) as rownum
from
(
select 'a' as col,a as xx from a union all
select 'b' as col,b as xx from a union all
select 'c' as col,c as xx from a union all
select 'd' as col,d as xx from a
)tb
)tb
where rownum = 1
*/exec(@sql)
/*
col xx
a 1990-01-11
*/
drop table acreate table a(a date,b date,c date,d date)insert into a
select '2012-01-09','2013-05-10','2008-10-01','2003-08-09' union all
select '2012-01-10','2013-06-09','2006-10-01','2002-08-09' union all
select '2012-01-23','2013-12-09','2005-10-01','2001-08-09' union all
select '1990-01-11','2013-11-09','2004-10-01','1998-08-09'
declare @sql nvarchar(max)
declare @tb_name nvarchar(100)set @sql = ''
set @tb_name = 'a'
select @sql = @sql + ' union all select '''+c.name +''' as col,' +
c.name + ' as xx' + ' from ' + @tb_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = @tb_name
--and c.name like 'num%'select @sql = 'select col,xx from(select col,xx,row_number() over(order by xx) as rownum from (' +
stuff(@sql,1,len(' union all'),'') + ' )tb)tb where rownum = 1'
--动态生成的语句
select @sql
/*
select col,xx
from
(
select col,xx,row_number() over(order by xx) as rownum
from
(
select 'a' as col,a as xx from a union all
select 'b' as col,b as xx from a union all
select 'c' as col,c as xx from a union all
select 'd' as col,d as xx from a
)tb
)tb
where rownum = 1
*/exec(@sql)
/*
col xx
a 1990-01-11
*/ 不同类型数据怎么求最小
改了一下,新增了一个非日期字段xxx,会自动过滤这个非日期字段:drop table acreate table a
(a date,b date,
xxx int, --增加了非日期字段
c date,d date)insert into a
select '2012-01-09','2013-05-10',1,'2008-10-01','2003-08-09' union all
select '2012-01-10','2013-06-09',2,'2006-10-01','2002-08-09' union all
select '2012-01-23','2013-12-09',3,'2005-10-01','2001-08-09' union all
select '1990-01-11','2013-11-09',4,'2004-10-01','1998-08-09'
declare @sql nvarchar(max)
declare @tb_name nvarchar(100)set @sql = ''
set @tb_name = 'a'
select @sql = @sql + ' union all select '''+c.name +''' as col,' +
c.name + ' as xx' + ' from ' + @tb_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
where t.name = @tb_name
and (tp.name like '%date%' or tp.name like '%time%') --只包含日期
--and c.name like 'num%'
select @sql = 'select col,xx from(select col,xx,row_number() over(order by xx) as rownum from (' +
stuff(@sql,1,len(' union all'),'') + ' )tb)tb where rownum = 1'
--动态生成的语句
select @sql
/*
select col,xx
from
(
select col,xx,row_number() over(order by xx) as rownum
from
(
select 'a' as col,a as xx from a union all
select 'b' as col,b as xx from a union all
select 'c' as col,c as xx from a union all
select 'd' as col,d as xx from a
)tb
)tb
where rownum = 1
*/exec(@sql)
/*
col xx
a 1990-01-11
*/