在SQL Server中使用Case时使用Ruby On Rails

I have this query in SQL

我在SQL中有这个查询

select @cd_x=
Case 
    when tp_x2='ZZZ' then  tp_x3
    when tp_x2='XXX' then  tp_x3
    else
    tp_x2
end 
from table
where id=@id

How can I translate this query to a sentence in Ruby on Rails?

如何将此查询转换为Ruby on Rails中的句子？

2 个解决方案

#1

I think you would be looking at something like:

我想你会看到类似的东西：

@cd_x = table.select("CASE WHEN tp_x2='ZZZ' THEN tp_x3 WHEN tp_x2='XXX' then tp_x3 ELSE tp_x2 END").where(:id => id)

I am using this as a model though they created a whole module out of it rather than one line: Case Statement in ActiveRecord

我使用它作为模型虽然他们创建了一个完整的模块而不是一行：ActiveRecord中的Case语句

#2

I had a kind a similar problem. I wanted to update more records with one query. Basically an order sorting update, using CASE sql.

我有类似的问题。我想用一个查询更新更多记录。基本上是使用CASE sql进行订单排序更新。

#List of product ids in sorted order. Get from jqueryui sortable plugin.
#product_ids = [3,1,2,4,7,6,5]

#product_ids.each_with_index do |id, index|
#  Product.where(id: id).update_all(sort_order: index+1)
#end

##CASE syntax example:
##Product.where(id: product_ids).update_all("sort_order = CASE id WHEN 539 THEN 1 WHEN 540 THEN 2 WHEN 542 THEN 3 END")

case_string = "sort_order = CASE id "      

product_ids.each_with_index do |id, index|
  case_string += "WHEN #{id} THEN #{index+1} "
end

case_string += "END"

Product.where(id: product_ids).update_all(case_string)

#1