如何从不同的表添加不同的列？ [英] How to add different columns from different tables?

问题描述

我只想从不同的表中添加不同的列...有人对此有任何想法吗？

I just wanted to add different columns from different tables... Has anyone any idea on how to do that?

考虑一下，我有3个表，如下所示

Consider I have 3 tables as below

1. 电视销售


2. AC销售


3. 制冷器销售

表格数据如下

1）电视销售

   Id    Date        NoOfSales   Totalamount

    1    03/05/2014     10         10000
    2    04/05/2014     20         20000
    3    05/05/2014     30         30000

2）Ac销售

   Id    Date        NoOfSales   Totalamount

   1    03/05/2014     10         50000
   2    04/05/2014     20         60000
   3    05/05/2014     30         70000

3）冷却器销售

   Id    Date        NoOfSales   Totalamount

   1    03/05/2014     10         30000
   2    04/05/2014     20         60000
   3    05/05/2014     30         70000

现在我想例如，从所有表中添加特定日期
的总计，我需要在2014年5月5日将totalamount设置为90000

Now I want to add the "Totalamount" from all the tables for a particular "date" for example I need totalamount on 03/05/2014 as 90000

推荐答案

在MySQL中，最简单的方法是使用全部工会和聚合：

In MySQL, the easiest way to do this is with union all and aggregation:

select date, sum(totalamount) as TotalSales
from ((select date, totalamount from TvSales
      ) union all
      (select date, totalamount from AcSales
      ) union all
      (select date, totalamount from CoolerSales
      )
     ) t
group by date;

您要使用全部联盟的原因如果各表中的日期不同。 联接使丢失行成为可能。

The reason you want to use union all is in case the dates are different in the various tables. A join makes it possible to lose rows.

第二，三个具有相同格式的表表示数据库设计不佳。您确实应该有一张带有销售额的表，并有一列指示其所指产品的类型。

Second, having three tables with the same format is an indication of poor database design. You should really have one table with the sales and a column indicating which type of product it refers to.

这篇关于如何从不同的表添加不同的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！