数据表如下
----------------------------------------------------------------------------
meterid date T1 T2 T3 T4 T5 T6 T7 T8 T9
----------------------------------------------------------------------------
2001 05-01-01 254 251 235 281 54 356 59 654 187
2002 05-01-01 22 42 31 45 78 88 64 25 54我想求T1-T9的最大值,并显示为新的字段T10
这个问题不好解决
对行不能用系统自带的函数,该怎么处理呢?希望大家不吝赐教!万分感谢。
----------------------------------------------------------------------------
meterid date T1 T2 T3 T4 T5 T6 T7 T8 T9
----------------------------------------------------------------------------
2001 05-01-01 254 251 235 281 54 356 59 654 187
2002 05-01-01 22 42 31 45 78 88 64 25 54我想求T1-T9的最大值,并显示为新的字段T10
这个问题不好解决
对行不能用系统自带的函数,该怎么处理呢?希望大家不吝赐教!万分感谢。
数据库中建立一个函数用于返回当前meterid对应的列组最大值create function GetMaxColValue(@meterid int)
Returns int
as
begin
declare @result int
Set @result = 0
select @result=max(a.t1) from
(
select meterid,t1 from tablename union all
select meterid,t2 from tablename union all
select meterid,t3 from tablename union all
select meterid,t4 from tablename union all
select meterid,t5 from tablename union all
select meterid,t6 from tablename union all
select meterid,t7 from tablename union all
select meterid,t8 from tablename union all
select meterid,t9 from tablename
) a where a.meterid = @meterid
return @result
end调用函数,结果返回给报表
select meterid,t1,t2,t3,t4,t5,t6,t7,t8,t9,dbo.GetMaxColValue(meterid) as t10 from tablename/*
-------------------测试结果
meterid date t1 t2 t3 t4 t5 t6 t7 t8 t9 t10
2001 05-01-01 123 23 242 34345 3423 234 544 6342 3453 34345
2002 05-01-01 121 123 411 245 232 456 774 23 84 774*/
一句就行
select greatest(列1,列2,列3,...) t10 from tablename
能说的详细点吗?
把上面代码
create function GetMaxColValue(@meterid int)
Returns int
as
begin
declare @result int
Set @result = 0
select @result=max(a.t1) from
(
select meterid,t1 from tablename union all
select meterid,t2 from tablename union all
select meterid,t3 from tablename union all
select meterid,t4 from tablename union all
select meterid,t5 from tablename union all
select meterid,t6 from tablename union all
select meterid,t7 from tablename union all
select meterid,t8 from tablename union all
select meterid,t9 from tablename
) a where a.meterid = @meterid
return @result
end
运行OK
应该不会有错
看看你的表结构
前面是我简化的模型,主键是 meterid和daytime
先SQL 查询符合条件的数据集.然后设计报表.
要是那么简单又何必出这么多分?
不好做啊?
各位大侠,帮帮我啦!
到底要求什么???(每行中的最大值??)
你是什么实现查询的
meterid和daytime
是主键的话
修改一下函数就可以了
create function GetMaxColValue(@meterid int,@daytime,datetime)
Returns int
as
begin
declare @result int
Set @result = 0
select @result=max(a.t1) from
(
select meterid,daytime,t1 from tablename union all
select meterid,daytime,t2 from tablename union all
select meterid,daytime,t3 from tablename union all
....
) a where a.meterid = @meterid and a.daytime 条件
return @result
end
那用在sql server里面写一个这样的函数吧
在企业管理器里面有创建函数,看一下帮助很简单的create function GetMaxT(@t1 int,@t2 int,@t3 int,...)
Returns int
as
begin
declare @result int
Set @result = @t1
if @result < @t2
set @result = @t2
if @result < @t3
set @result = @t3
...
return @result
end
用的时候
select *,dbo.getmaxt(t1,t2,t3,...) as maxt from tb呵呵,这方法显得有点太笨了