获取表A中几个SQL行(a_ID和avg_data)的平均值，其中在表B中(a_ID按b_ID分组)

Question

我有2个表。表A、表b

Table A:
a_ID (313, 314, 315, 322) avg_data (50, 920, 12, 16)
Table B:
a_ID (313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323) 
b_ID (204, 204, 204, 205, 205, 206, 207, 208, 209, 210, 210)

多个a_ID属于一个b_ID。

我想得到avg_data在表A的平均值为所有属于表b中的b_ID的a_ID。我试图在php中做到这一点。

类似:

 GET the list of a_ID in table B that have the same b_ID, then search table_A for all these ID's in the list and get the average of their respective avg_data column.

这可能在一个SQL语句中做吗?

到目前为止我已经试过了

 $qry="SELECT b_ID
    FROM table_B";              
 $result= mysql_query($qry);
 while($row = mysql_fetch_assoc($result)) {
    $[b_ID] =$row['b_ID'];
  }
  foreach($b_ID as $value) {
  $qry="SELECT a_ID
    FROM table_B WHERE b_ID = '$value'";                
  $result= mysql_query($qry);

  while($row = mysql_fetch_assoc($result)) {
      $a_ID[] =$row['a_ID'];

   }
   $search = implode(', ', $a_ID); 
   echo $search; 
  }   

那么输出应该是

 313, 314, 315 <br> 316, 317 <br> 318 <br> 319 etc

，但它实际上输出:

 313, 314, 315, 322, 313, 314, 315
 313, 314, 315, 322, 313, 314, 315, 316, 317
 313, 314, 315, 322, 313, 314, 315, 316, 317, 318

这意味着获得属于每个b_ID的a_ID的列表，以便我可以找到平均值，但是，输出不正确并且包含比它应该包含的更多的a_ID。

我已经尽力解释了，但似乎还是不太明白。

Answer 1

好的-在评论之后，我想我理解你的最终目标是avg_data的平均值。这可以通过在两个表之间执行INNER JOIN并按b_ID分组的单个相当简单的查询来完成。

SELECT
  Tableb.b_ID,
  AVG(TableA.avg_data) AS average
FROM
  TableB
  INNER JOIN TableA ON TableA.a_ID = TableB.a_ID
GROUP BY TableB.b_ID

http://sqlfiddle.com/!2/349ba/12

现在，如果您还希望在输出中列出a_ID，则会变得有点复杂。您需要将该结果作为对TableB的子查询返回，以获得a_ID列表，然后它们可以通过GROUP_CONCAT()分组为具有相同平均值的逗号分隔值列表:

SELECT 
  GROUP_CONCAT(b.a_ID), 
  avgs.average
FROM 
  TableB b
  /* Makes sure only a_ID existing in TableA appear in the output */
  INNER JOIN TableA a ON b.a_ID = a.a_ID
  INNER JOIN (
    /* Retrieves average avg_data for like b_ID values */
    SELECT
      Tableb.b_ID,
      AVG(TableA.avg_data) AS average 
    FROM
      TableB
      INNER JOIN TableA ON TableA.a_ID = TableB.a_ID
    GROUP BY TableB.b_ID
  ) avgs ON b.b_ID = avgs.b_ID
GROUP BY avgs.b_ID

http://sqlfiddle.com/!2/349ba/21

Answer 2

我只是在浏览你的php代码…

1. 在第一个查询中首先使用distinct .
2. 当你创建$a_ID时，你需要根据b_id ..将它们分组。

我对你的代码做了一些修改。请看

 <?php
        mysql_connect('localhost','root','');
        mysql_select_db('test');
        $qry="SELECT distinct b_ID
            FROM Table_B";              
         $result= mysql_query($qry);
         while($row = mysql_fetch_assoc($result)) {
            $b_ID[] =$row['b_ID'];
          }

          foreach($b_ID as $value) {
          $qry="SELECT a_ID
            FROM yable_B WHERE b_ID = '$value'";                
          $result= mysql_query($qry);

          while($row = mysql_fetch_assoc($result)) {
              $a_ID[$value][] =$row['a_ID'];

           }
          }   

          foreach( $a_ID as $value)
           {
             $search = implode(',',$value);
             echo $search ."<br>"; 
           }
        ?>

Answer 3

select distinct b_ID, (select avg(avg_data) from table_A where a_ID in (select a_ID from table_B y where y.b_ID=x.b_ID) ) from table_B x