在mysql中连接三个表[关闭]

问题描述：

I have 3 tables. table A has two fields code and item. Table B has 3 fields code, qty_recd and recd_dt. Table C has 3 fields Code, qty_sold, sold_dt. I want to get a result in this way:

For each record in Table A, sum of qty_recd from Table B and sum of qty_sold from Table C.

TABLE-A

code      Item
-----     ------
A1       AMMONIA SOLUTION


TABLE-B

code     qty_recd       recd_dt
-----    --------       -------
A1         5            2013-06-01
A1         8            2013-04-18
A1         3            2013-05-22


TABLE-C

code     qty_sold       sold_dt
-----    --------       -------
A1         1            2013-08-10
A1         4            2013-09-01
A1         2            2013-05-11

I want result like this

code      item            tot_recd       tot_sold
-----    --------         -------       --------
A1     AMMONIA SOLUTION     16             6

I tried several constructs, but not getting desired output. Please help to construct SELECT statement

我有3个表。表A有两个字段代码和项目。表B有3个字段代码，qty_recd和recd_dt。表C有3个字段Code，qty_sold，sold_dt。我想以这种方式得到一个结果： p>

对于表A中的每条记录，表B中的qty_recd和表C中qty_sold之和的总和。 p>

  TABLE-A 
 
code Item 
 ----- ------ 
A1 AMMONIA SOLUTION 
 
 
TABLE-B 
 
code qty_recd recd_dt 
 ---  -  -------- ------- 
A1 5 2013-06-01 
A1 8 2013-04-18 
A1 3 2013-05-22 
 
 
TABLE-C \  n 
code qty_sold sold_dt 
 ----- -------- ------- 
A1 1 2013-08-10 
A1 4 2013-09-01 
A1 2 2013-05-  11 
  code>  pre> 
 
 我想要这样的结果 p> 
 
 
 代码项tot_recd tot_sold 
 -----  -  ------ ------- -------- 
A1 AMMONIA SOLUTION 16 6 
  code>  pre> 
 
 我尝试了几种结构，但是 没有得到理想的输出。 请帮助构造SELECT语句 p> 
  div>

答

You want to join your master product list to two summaries. (Don't use hyphens in table names routinely!)

One of the summaries is:

   SELECT code, SUM(qty_recd) AS qty_recd
     FROM `TABLE-B`
    GROUP BY code

You can guess the other.

Your overall query should be

   SELECT a.code, a.item, b.qty_recd, c.qty_sold
     FROM `TABLE-A` AS a
     LEFT JOIN (
         SELECT code, SUM(qty_recd) AS qty_recd
           FROM `TABLE-B`
          GROUP BY code
     ) AS b ON a.code = b.code
     LEFT JOIN (
         SELECT code, SUM(qty_sold) AS qty_sold
           FROM `TABLE-C`
          GROUP BY code
     ) AS c ON a.code = c.code

Here's a SQL Fiddle showing it. http://sqlfiddle.com/#!2/f6cb7/1/0

You might naively just JOIN all three tables and then use GROUP BY. But then you'd be summarizing this nasty cross-product derived table:

CODE      qty_recd    qty_sold
A1         5              1
A1         5              4
A1         5              2
A1         8              1
A1         8              4
A1         8              2
A1         3              1
A1         3              4
A1         3              2

For example, this incorrect query would do that.

      SELECT a.code, a.item, SUM(b.qty_recd), SUM(c.qty_sold) //WRONG!
        FROM `TABLE-A` AS a
   LEFT JOIN `TABLE-B` AS b ON a.code = b.code
   LEFT JOIN `TABLE-C` AS C ON a.code = c.code
    GROUP BY a.code, a.item

See this fiddle. http://sqlfiddle.com/#!2/f6cb7/3/0

That's not what you want. Hence the need for summary subqueries.

在mysql中连接三个表[关闭]

相关推荐