如何提取一行中的最大值呀?谢谢!数据有好多行,现在拿两行说明下 表明为 TBtime日期 0点 1点 2点
2011-4-1 13196.79 6005.82 2811.20
2011-4-2 5846.48 12668.20 2761.23
……想要的结果是
日期 最大值
2011-4-1 13196.79
2011-4-2 12668.20
……谢谢
2011-4-1 13196.79 6005.82 2811.20
2011-4-2 5846.48 12668.20 2761.23
……想要的结果是
日期 最大值
2011-4-1 13196.79
2011-4-2 12668.20
……谢谢
declare @table table
(日期 varchar(10),[0点] numeric(7,2),[1点] numeric(7,2),[2点] numeric(6,2))
insert into @table
select '2011-4-1',13196.79,6005.82,2811.20 union all
select '2011-4-2',5846.48,12668.20,2761.23select 日期,最大值=
case when(case when
[0点]>[1点] then [0点] else [1点] end)>[2点]
then (case when [0点]>[1点] then [0点] else [1点] end)
else [2点] end from @table/*
日期 最大值
---------- ---------------------------------------
2011-4-1 13196.79
2011-4-2 12668.20
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([日期] [datetime],[a0点] [numeric](7,2),[a1点] [numeric](7,2),[a2点] [numeric](7,2)
)
INSERT INTO [tb]
SELECT '2011-4-1','13196.79','6005.82','2811.20' UNION ALL
SELECT '2011-4-2','5846.48','12668.20','2761.23'
--查询
SELECT *,
maxvalue=(SELECT tb.* FOR XML PATH('x'),TYPE).value('max(/x/*)','decimal(18,2)')
FROM tb
/*
日期 a0点 a1点 a2点 maxvalue
2011-04-01 00:00:00.000 13196.79 6005.82 2811.20 13196.79
2011-04-02 00:00:00.000 5846.48 12668.20 2761.23 12668.20
*/
create table tb
(日期 nvarchar(10), [0点] float, [1点] float, [2点] float)
insert tb
select '2011-4-1',13196.79, 6005.82, 2811.20 union all
select '2011-4-2',5846.48, 12668.20 ,2761.23 union all
select '2011-4-3',45196.79, 756.82, 2751.20 union all
select '2011-4-4',35446.48, 3568.20 ,78456.23 union all
select '2011-4-5',1786.79, 9875.82, 999.20 union all
select '2011-4-6',3576.48, 964568.20 ,678678.23
create table #tb(日期 nvarchar(10), [点] float)
declare @sql as nvarchar(4000)
set @sql=''
select @sql=@sql+' union all select 日期,['+[name]+'] from tb' from syscolumns
where object_id('tb')=id and [name]<>'日期' order by colid
set @sql=stuff(@sql,1,10,'insert #tb ')
exec(@sql)
select 日期,max([点])as 最大点数 from #tb group by 日期drop table #tb
有很多行数据当然可以,如果不止三列的话,那是不行的,要用动态sql