MySQL -对结果进行求和并在单个查询中加入

Question

表1中相应程序的总时数应与表2中的prod_count相乘。

例如:让我们使用一个用户ram。

ram开发了表1中的两个程序pro-1 = 9hr (5 + 4)和pro-2 = 6 hr

表2中pro-1 is 200和pro-2 is 120的pro-2 is 120

所以理想情况下应该是200*9 = 1800和120*6 = 720

所以total is (1800 + 720) = 2520

表1中的total task count is (656 + 23 + 44) = 723

ram的结果应该是ram, 2520 , 723

表1

| id | name  | program | task_hours | task_count |
|----|-------|---------|------------|------------|
| 1  | raj   | pro-1   | 6.5        | 344        |
| 2  | raj   | pro-2   | 2          | 324        |
| 3  | ram   | pro-1   | 5          | 656        |
| 4  | ram   | pro-1   | 4          | 23         |
| 5  | ram   | pro-2   | 6          | 44         |
| 6  | kumar | pro-2   | 7          | 345        |
| 7  | kumar | pro-3   | 4          | 222        |

表2

| id | program | prod_count |
|----|---------|------------|
| 1  | pro-1   | 200        |
| 2  | pro-2   | 120        |
| 3  | pro-3   | 60         |

预期结果：

 name  | total_calc | total_count |
|-------|------------|-------------|
| raj   | 1540       | 668         |
| ram   | 2520       | 723         |
| kumar | 1080       | 567         |

SQL http://sqlfiddle.com/#!9/f75e15

Answer 1

加入表格并汇总：

select t1.name,
       sum(t1.task_hours * t2.prod_count) total_calc,
       sum(t1.task_count) total_count
from Table1 t1 inner join Table2 t2
on t2.program = t1.program
group by t1.name

见演示。

结果：

| name  | total_calc | total_count |
| ----- | ---------- | ----------- |
| kumar | 1080       | 567         |
| raj   | 1540       | 668         |
| ram   | 2520       | 723         |

Answer 2

试试这个：http://sqlfiddle.com/#!9/eb5f61/1/0

select
  name,
  sum(task_hours * prod_count) total_calc,
  sum(task_count) total_count
from table1 t1
join table2 t2
on t1.program = t2.program
group by name
order by t1.id

注意: task_hours应该是浮动类型。