如何在Arel中编写`(SELECT @row_index:=-1) AS r`查询语句？

下面是对应你给出的MySQL查询的Arel实现方案，完全匹配原SQL的逻辑：

1. 定义基础对象与参数 #

首先绑定Rails模型对应的Arel表，以及传入查询所需的参数：

# 假设你的模型名为 LocationMarket
location_markets_table = LocationMarket.arel_table

# 替换为实际业务中的参数值
target_column = :price # 对应原SQL的 #{column_name}
location_type_val = "city" # 对应原SQL的 #{location_type}
median_type_val = "monthly" # 对应原SQL的 #{type}
# 自定义WHERE条件，示例：location_markets_table[:region].eq("north")
custom_where = location_markets_table[:region].eq("north")

2. 构建子查询（对应原SQL的内层SELECT） #

原SQL的子查询核心是实现用户变量@row_index的自增，Arel没有原生支持MySQL变量，直接用SqlLiteral嵌入原生SQL片段即可：

# 生成 @row_index:=@row_index + 1 AS row_index 的Arel表达式
row_index_inc = Arel::Nodes::SqlLiteral.new("@row_index:=@row_index + 1").as("row_index")

# 构建子查询
subquery = location_markets_table.project(
  row_index_inc,
  location_markets_table[target_column]
).where(
  # 拼接所有WHERE条件：自定义条件 + location_type匹配 + 列非空
  custom_where.and(
    location_markets_table[:location_type].eq(location_type_val)
  ).and(
    location_markets_table[target_column].not_eq(nil)
  )
).order(location_markets_table[target_column])

3. 构建主查询（对应原SQL的外层SELECT） #

主查询需要关联子查询、计算平均值、初始化变量、设置过滤条件：

# 给子查询设置别名，并创建对应的Arel表对象用于引用列
subq_alias = subquery.as("subq")
subq_table = Arel::Table.new("subq")

# 生成 (SELECT @row_index:=-1) AS r 的Arel表达式
init_row_var = Arel::Nodes::SqlLiteral.new("(SELECT @row_index:=-1)").as("r")

# 生成 FLOOR(@row_index / 2) 和 CEIL(@row_index / 2) 的Arel表达式
floor_expr = Arel::Nodes::NamedFunction.new("FLOOR", [Arel::Nodes::SqlLiteral.new("@row_index / 2")])
ceil_expr = Arel::Nodes::NamedFunction.new("CEIL", [Arel::Nodes::SqlLiteral.new("@row_index / 2")])

# 构建最终的主查询
final_query = Arel::SelectManager.new(Arel::Table.engine)
final_query.project(
  # 计算AVG作为median_value
  Arel::Nodes::NamedFunction.new("AVG", [subq_table[target_column]]).as("median_value"),
  init_row_var,
  # 生成常量值作为median_type
  Arel::Nodes::SqlLiteral.new("'#{median_type_val}'").as("median_type")
).from(subq_alias).where(
  subq_table[:row_index].in([floor_expr, ceil_expr])
)

# 可以输出SQL字符串验证是否和原SQL一致
puts final_query.to_sql

关键说明 #

• 对于MySQL特有的用户变量操作，Arel没有封装对应的方法，直接使用Arel::Nodes::SqlLiteral嵌入原生SQL是最简洁的解决方案。
• 子查询必须通过.as()设置别名，再用Arel::Table.new(别名)来引用子查询的列，这是Arel关联子查询的标准方式。
• 聚合函数和数学函数（AVG、FLOOR、CEIL）可以用Arel::Nodes::NamedFunction构建，也可以直接用SqlLiteral，前者更贴合Arel的面向对象风格。

内容的提问来源于stack exchange，提问作者user20795798