Trung tâm cá nhân
sách gpt4 ai đã đi

mysql - 连接三个表(2 级)并根据条件求和 - SQL Rails

In lại 作者：行者123 更新时间：2023-11-29 08:16:37 26 4
mua khóa gpt4 Nike

我有以下架构:

User:
---
NHẬN DẠNG
---

Tasks:
-------------------
ID | classification
-------------------

Timesheets:
------------------------
ID | task_id | user_id
------------------------

TimesheetItem:
--------------------------------
ID | timesheet_id | hours | date
--------------------------------

协会:

class User 
  has_many :timesheets
kết thúc

class Task 
  has_many :timesheets
kết thúc

class Timesheet 
  has_many :timesheet_items
  belongs_to :user
  belongs_to :task
kết thúc

class TimesheetItem
  belongs_to :timesheet
kết thúc

分类可以是“可计费”或“不可计费”。现在我需要为每个用户找到可计费和不可计费时间的总和，如下所示:

-----------------------------------
|user_id | billable | nonbillable |
-----------------------------------

我在 Rails 中所做的是:

User.joins(:timesheets, :timesheets => :task, :timesheets => :timesheet_items)
    .select("SUM(CASE 
                   WHEN tasks.task_classification = 'Billable' 
                      THEN timesheet_items.hours 
                   ELSE 
                      0 
                 END) as billable, 
             SUM(CASE 
                   WHEN tasks.task_classification = 'Non-Billable' 
                     THEN timesheet_items.hours 
                   ELSE 0 
                 END) as nonbillable")

但是 MySQL 给出错误“tasks.classification”是未知列。查看正在生成的查询，这是可以理解的:

SELECT SUM(CASE WHEN tasks.classification = 'Billable' THEN hours ELSE 0 END) as billable, SUM(CASE WHEN tasks.classification = 'Non-Billable' THEN hours ELSE 0 END) as nonbillable FROM `users` INNER JOIN `timesheets` ON `timesheets`.`user_id` = `users`.`id` INNER JOIN `timesheet_items` ON `timesheet_items`.`timesheet_id` = `timesheets`.`id`

正如您所看到的，任务表没有被加入。

我该如何实现这一目标？谢谢。

biên tập:

我继续进行了一个简单的 SQL 查询，该查询连接了任务表来获取结果，因为该数据仅在一个地方使用，并且很少使用。

但现在我需要按月对小时数进行分组，并找出每个用户记录的计费小时数和非计费小时数。例如。

kết quả:

-----------------------------------------------
user_id | month | BillableHrs | NonBillableHRS|
-----------------------------------------------

我尝试过group(user_id, MONTH(date))但是..结果很奇怪。我怎样才能获得此类信息？

顺便说一句，将连接更改为:

joins(:timesheets, :timesheets => [:task, :timesheet_items])

解决了未找到列的问题:)

我终于找到了这个解决方案。有什么优化的想法吗？ 

LỰA CHỌN 
  users.id as user_id,
  users.name as user_name,
  CONCAT(MONTHNAME(date)," ",YEAR(date)) as month,
  SUM( CASE 
        WHEN tasks.task_classification = "Billable" 
          THEN hours 
        ELSE 
          0 
        END ) as blb_sum, 
  SUM( CASE 
        WHEN tasks.task_classification = "Non-Billable"
          THEN hours 
        ELSE 
          0 
        END ) as nblb_sum
FROM `users` 
  INNER JOIN `timesheets` ON `timesheets`.`user_id` = `users`.`id` 
  INNER JOIN `timesheet_items` ON `timesheet_items`.`timesheet_id` = `timesheets`.`id` 
  INNER JOIN `tasks` ON `timesheets`.`task_id` = `tasks`.`id` 
WHERE 
  timesheet_items.date >= '2013-11-1' AND
  timesheet_items.date <= '2013-11-31'

1 Câu trả lời

由于如果将其表示为 activerecord 和 SQL 的混合体，那么这将是一些相当丑陋的代码，因此一种想法是将复杂查询定义为 View 并将其引用为模型。

做起来非常简单 - 如果这是用户级别的一组指标，则按照以下方式构建一个 View :

create view user_billing_metrics
BẰNG
select user.id user_id,
        sum(case ... blah blah) billable_hours,
        sum(case ... blah blah) unbillable_hours
from ...

然后创建一个只读模型...

class UserBillingMetric < ActiveRecord::Base

  belongs_to :user, :inverse_of => :user_billing_metric

  def read_only?
    ĐÚNG VẬY
  kết thúc

kết thúc

然后...

lớp Người dùng < ActiveRecord::Base

  has_one :user_billing_metric, :inverse_of => :user

  delegate :billable_hours , :to => :user_billing_metric
  delegate :unbillable_hours, :to => :user_billing_metric

  def read_only?
    ĐÚNG VẬY
  kết thúc
kết thúc

然后您可以:

u = User.find( ...)

u.billable_hours

...或...

u = User.find( ...)
hours= u.user_billing_metric

vân vân.

可能在那里犯了一个愚蠢的打字错误。

它的一个很好的功能是您可以，例如:

users_to_fire = User.joins(:user_billing_metric).
                     where(:user_billing_metrics => {:billable_hours = 0})

再说一次，可能是愚蠢的拼写错误。

将其推送到数据库非常有效，比尝试通过 Rails 运行它要高效得多。一个好的查询优化器不会计算 View 中不需要的表达式，甚至不会执行逻辑上冗余的连接。

无论如何，只是将其发布以征求意见。我知道将业务逻辑放在数据库层并不符合每个人的口味，但它可以保持干燥，并且我在自己的应用程序中遇到了这样的情况，出于性能原因，这绝对是唯一的选择。

关于mysql - 连接三个表(2 级)并根据条件求和 - SQL Rails，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/20494082/

26 4 0
Bài viết được đề xuất: java - 我将如何创建一个旋转矩阵，将 X 旋转 a，Y 旋转 b，Z 旋转 c？
Bài viết được đề xuất: PHP创建表错误1064
Bài viết được đề xuất: mysql - 从 Shiny 的 renderUI 中获取
Bài viết được đề xuất: MYSQL 如何获取从最高排名到最高范围的行
行者123
Hồ sơ cá nhân

Tôi là một lập trình viên xuất sắc, rất giỏi!

Nhận phiếu giảm giá Didi Taxi miễn phí
Mã giảm giá Didi Taxi
Các bài viết nóng hổi trên toàn bộ trang web
Giấy chứng nhận ICP Bắc Kinh số 000000
Hợp tác quảng cáo: 1813099741@qq.com 6ren.com