Ruby跨行获取最大值

假如有一张表

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

转载需保留链接来源:软件玩家 » Ruby跨行获取最大值

赞 (0)

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

感谢您的支持!

微信扫一扫打赏