使用不同的查询mysql创建表

时间：2021-09-01 21:23:09

I want to create a table with the name of the WORKER, the total Square meteres he built and the total cost he was paid.

我想创建一个名为WORKER的表，他建立的Square Meteres总数以及他支付的总费用。

i have 2 tables

我有2张桌子

tbl_orden and tbl_costos

tbl_orden和tbl_costos

the obrero is the foreign key in tbl_costos

obrero是tbl_costos中的外键

this is the query im trying:

这是我尝试的查询：

SELECT a.obrero AS MAESTRO, ROUND(SUM(metros_c),2) AS METROS, SUM(b.costo_semanal)
FROM tbl_orden as a, tbl_costos as b GROUP BY a.obrero

Im trying the above query but i dont get the proper result. The SUM of metros_c is not correct neither the sum of the costo_semanal

我尝试上面的查询，但我没有得到正确的结果。 metros_c的SUM不是costo_semanal的总和

tbl_order

tbl_order

  • id (int) primary key
  • id（int）主键
  • name_obra (varchar)
  • name_obra（varchar）
  • num_fab (int)
  • num_fab（int）
  • dated (date)
  • 日期（日期）
  • type (varchar)
  • type（varchar）
  • obrero (varchar)
  • obrero（varchar）
  • metros_c (float)
  • metros_c（float）
  • comment (varchar)
  • 评论（varchar）

tbl_costos:

tbl_costos：

  • id (int ) primary key
  • id（int）主键
  • costo_semanal (float)
  • costo_semanal（浮动）
  • obrero (foreign key from tbl_order)
  • obrero（来自tbl_order的外键）
  • fechai (date)
  • fechai（日期）
  • fechaf (date)
  • fechaf（日期）

I want this query results:

我想要这个查询结果：

 SELECT obrero AS Maestro, ROUND(SUM(metros_c),2) AS Metros_Cuadrados, ROUND(SUM(metros_c)/MONTH(CURDATE()),2) as Promedio_Mensual, ROUND(SUM(metros_c)/WEEK(CURDATE()),2) as Promedio_Semanal FROM `tbl_orden`
          WHERE dated BETWEEN '01/01/2015' AND '01/01/2016' GROUP BY obrero ORDER BY metros_c asc

and this query results in other column of the above table:

此查询导致上表的其他列：

SELECT SUM(costo_semanal) from tbl_costos GROUP BY obrero

with the first query i get 4 columns and with the 2nd i get 1. i want to have a result table with the 5 columns

第一个查询我得到4列，第二个得到1.我希望有一个5列的结果表

1 个解决方案

#1

0  

You can put each into an inline view and then join them to do this:

您可以将每个放入内联视图，然后将它们连接起来执行此操作：

select x.*,
       y.sum_costo_semanal
  from (select obrero as maestro,
               round(sum(metros_c), 2) as metros_cuadrados,
               round(sum(metros_c) / month(curdate()), 2) as promedio_mensual,
               round(sum(metros_c) / week(curdate()), 2) as promedio_semanal
          from tbl_orden
         where dated between '01/01/2015' and '01/01/2016'
         group by obrero) x
  join (select obrero,
               sum(costo_semanal) as sum_costo_semanal
          from tbl_costos
         group by obrero) y
    on x.obrero = y.obrero

#1

0  

You can put each into an inline view and then join them to do this:

您可以将每个放入内联视图，然后将它们连接起来执行此操作：

select x.*,
       y.sum_costo_semanal
  from (select obrero as maestro,
               round(sum(metros_c), 2) as metros_cuadrados,
               round(sum(metros_c) / month(curdate()), 2) as promedio_mensual,
               round(sum(metros_c) / week(curdate()), 2) as promedio_semanal
          from tbl_orden
         where dated between '01/01/2015' and '01/01/2016'
         group by obrero) x
  join (select obrero,
               sum(costo_semanal) as sum_costo_semanal
          from tbl_costos
         group by obrero) y
    on x.obrero = y.obrero
标签：

相关文章