mysql - Rails group_by 总金额

Question

我正在使用 Rails 和 MySql，目前，我的数据在 View 中显示如下:

Customer A, 5
Customer B, 3
Customer C, 2
Customer A, 2
Customer B, 4

我希望它像这样显示:

Customer A, 7
Customer B, 7
Customer C, 2

目前我的 .html.erb View 如下所示:

<% @licenses.each do |l| %>
<tr>
    <td><%= l.customer.companyname %></td>
    <td><%= l.customer.licensecontact %></td>
    <td><%= l.amount %></td>
    </td>
</tr>
<% end %>

我很确定我需要在 @licenses.each 之后添加一些内容，我只是不确定什么？

Answer 1

根据您提供的很少的信息，我认为您必须使用 Group BY

SELECT customer, SUM(quantity) AS quantity FROM orders GROUP BY customer

使用 Rails 3

Order.select("customer, SUM(quantity) AS quantity").group("customer")

使用 Rails 2 #这也适用于 Rails 3

Order.find(:all, :select => "customer, SUM(quantity) AS quantity", :group => "customer")

已编辑

您必须通过以下方式获取记录

@licenses = License.find_by_sql("SELECT c.companyname, c.licensecontact, SUM(l.amount) 
                                  FROM licenses l 
                                  LEFT JOIN customers c ON c.id=l.customer_id
                                  GROUP BY c.companyname, c.licensecontact")

将您的 View 更改为以下

<% @licenses.each do |l| %>
<tr>
    <td><%= l.companyname %></td>
    <td><%= l.licensecontact %></td>
    <td><%= l.amount %></td>
    </td>
</tr>
<% end %>