HoneyHoney

CREATE TABLE [dbo].[test1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [a] [int] NULL,
    [b] [int] NULL,
    [c] [int] NULL,
    [d] [int] NULL,
    [e] [int] NULL,
    [f] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into test1 (a, b, c, d, e, f)
VALUES (
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99)
)
go 900000 

1. 用Values
   

SELECT  id,         (         SELECT  MAX(x)         FROM    ( VALUES ( a), ( b), ( c), ( d), ( e), ( f) ) t (x)         ) FROM    test1;

2. 用unpivot

SELECT id, MAX(list.value) AS greatest FROM test1 UNPIVOT(value FOR ColumName IN(a,b,c,d,e,f)) list GROUP BY id

3. 类似的Values

select id, (SELECT MAX(value) FROM (select t.a value UNION ALL SELECT t.b UNION ALL select t.c UNION ALL select t.d UNION ALL select t.e UNION ALL select t.f ) data ) maxvalue from test1 t

4. 用CASE
冗长，容易出错，但是性能最好的一个方法。

require 'tiny_tds'
tdate = Time.now
client = TinyTds::Client.new(:username => 'username', :password => 'password', :dataserver => 'localhost',:port => 1433, :database => 'event_service', :timeout => 300)
data = client.execute("select * from test1;");
tmp = []
data.each do |t|
tmp << t['a'] << t['b'] << t['c'] << t['e'] << t['f']
max = tmp.max
# puts t['id'].to_s + "\t" + max.to_s
tmp = []
end
delta = Time.now - tdate
puts "Elapsed time: --#{delta}"