急sql 表中相同时间类型的字段的最小值
日期：2014-05-17 浏览次数：20748 次
急！！sql 求一个表中相同时间类型的字段的最小值
具体如下：
例如 表a
字段 a date,b date,c date,d date 求类似这样四个字段中的最小值。可能不止这4个。
------解决方案--------------------
------解决方案--------------------
用动态的语句就可以：
------解决方案--------------------
具体如下：
例如 表a
字段 a date,b date,c date,d date 求类似这样四个字段中的最小值。可能不止这4个。
------解决方案--------------------
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
*/
------解决方案--------------------
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-1
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
