选择distinct与多列rails mysql

RIght now, I have mysql database and this code:

现在,我有mysql数据库和这段代码:

Report.select('date, title, info').distinct

It is equivalent to this sql query:

它相当于这个sql查询:

SELECT DISTINCT
    date,
    title,
    marketplace
FROM
    reports

But what if I want to select not only date, title and info, but more params like price. But I want results to be distinct based only on date,title and info What do I do? I thought of digging somewhere to group method.

但是,如果我不仅要选择日期,标题和信息,还要选择价格等更多的参数。但我希望结果仅基于日期,标题和信息进行区分我该怎么办?我想到了挖掘某个地方的方法。

1 个解决方案

#1

It is possible to achieve this by specifying which columns the SQL query should be grouped by.

可以通过指定SQL查询应按哪些列进行分组来实现此目的。

I'll show the scenario of wanting to sum the price, as an example, since you did not include how you want to use price (like Danil Speransky mentioned).

作为一个例子,我将展示想要总结价格的情景,因为你没有包括你想要如何使用价格(如Danil Speransky所提到的)。

Calculate the sum of all prices per date, title and info:

计算每个日期,标题和信息的所有价格的总和:

@reports = Report.select([:date, :title, :info]).group([:date, :title, :info]).sum(:price)
(4.1ms)  SELECT SUM(`reports`.`price`) AS sum_price, date AS date, title AS title, info AS info FROM `reports` GROUP BY date, title, info
=> {["[date]", "[title]", "[info]"]=>1234,
    ["[date]", "[title2]", "[info]"]=>5678,
    ...
}

Which you could then loop through and access like this

然后您可以循环访问并像这样访问

@reports.each_pair do |columns, sum_price|
  [0] # date
  [1] # title
  [2] # info
  sum_price # The sum of all prices
end

Another option would be to build the select query manually like this

另一种选择是像这样手动构建选择查询

@reports = Report.select("SUM(`reports`.`price`) AS sum_price, date AS date, title AS title, info AS info").group([:date, :title, :info])
# Results in same SQL query, but will instantiate a model record for each row
=> [#<Report date: "2017-09-18", title: "[Title]", info: "[Info]">,
    #<Report date: "2017-09-18", title: "[Title2]", info: "[Info]">,
    ...
]

And even though the inspected records wont show the :sum_price column, it can still be accessed like this

即使被检查的记录不会显示:sum_price列,它仍然可以像这样访问

@reports.each do |report|
  report.date
  report.title
  report.info
  report.sum_price
end

#1