Ruby跨行获取最大值-VCBeta.CN

假如有一张表

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

我希望找出每行中的最大数，如果用SQL语句有几个方法：

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

冗长，容易出错，但是性能最好的一个方法。对于上面四种方式，性能依次是4>1=3>2。用第一种方法比较容易方便，不容易出错。当然，还有另外一种方法就是用其他的开发语言，比如Ruby：

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}”

转载需保留链接来源：VCBeta.CN » Ruby跨行获取最大值

相关推荐

热门文章