mysql - SQL查询以获取特定 parent 的 child 的平均值

Question

我不确定问题标题。

让我们解释一下我的问题。

我有两张 table 1. Cows 和 2. Children

CREATE TABLE Cows (
cow_id int,
Name varchar(255));

CREATE TABLE Children (
child_id int,
cow_id int,
weight double,
time time,
fr_score double);

我将它们与左连接结合起来

select 
    cow.cow_id
    , cow.Name
    , child.child_id
    , Child.DOB
FROM(
    select cow_id
        , Name
    From cows
) as cow
left join (
    select child_id
        , cow_id
        , weight
        , time
        , fr_score
    From Children
) as child
on child.cow_id = cows.cow_id
order by cow.cow_id, child.child_id

我得到的结果是这样但是......

cow_id   child_id    weight       time      fr_score
------   --------    ------     --------    --------
 601       6011        78       05:00:15       2.5
 601       6012        58       05:00:15       2.6
 601       6013        88       07:12:50       0.0
 601       6014        77       09:10:50       4.5
 602       6021        98       13:12:53       1.6
 602       6022        85       15:00:12       1.8
 602       6023        68       17:22:35       2.9
 603       6031        73       20:22:12       3.8
 603       6032        72       21:04:52       2.6
 604       6041        78       23:43:45       4.1

但我想要的是得到这个结果(来自特定奶牛的 child 的平均体重，这将适用于每一头奶牛及其 child )。 "xx/xxx"表示平均值

cow_id   child_id    weight       time      fr_score
------   --------    ------     --------    --------
 601       6011        78       05:00:15       2.5
 601       6012        58       05:00:15       2.6
 601       6013        88       07:12:50       0.0
 601       6014        77       09:10:50       4.5
           avg-        XX                      XXX
 602       6021        98       13:12:53       1.6
 602       6022        85       15:00:12       1.8
 602       6023        68       17:22:35       2.9
           avg-        XX                      XXX
 603       6031        73       20:22:12       3.8
 603       6032        72       21:04:52       2.6
           avg-        XX                      XXX
 604       6041        78       23:43:45       4.1

Answer 1

我们在这里所做的只是合并一组包含平均值的数据。将其包装在外部查询中，以便我们可以按照您想要的方式进行排序，并添加了一个列 zorder 来控制排序，以确保平均值出现在 child 之后

  SELECT cow_id
     , Name
     , child_id
     , weight
     , time
     , fr_Score
FROM (SELECT cow.cow_id
           , cow.Name
           , child.child_id
           , child.weight
           , child.time
           , child.fr_Score
           , 0 zorder
      FROM cows as cow
      LEFT JOIN Children as child
        on child.cow_id = cow.cow_id
      UNION ALL
     SELECT cow_ID
           , NULL
           , NULL
           , avg(weight) 
           , NULL
           , avg(fr_Score) afs
           , 1 zorder 
      FROM children 
      GROUP BY cow_id) C
ORDER BY cow_id, zorder, child_id

注意:这确实包括子行上的父 cow_ID w/average 如果需要，我们可以找到一种方法来省略该数据；但这通常是在 UI 层完成的。

正如 bamar 指出的那样，这也可以通过汇总来完成:http://sqlfiddle.com/#!9/885370/2/1

Select * from (
SELECT cow.cow_id
           , cow.Name
           , child.child_id
           , avg(child.weight)
           , child.time
           , avg(child.fr_Score)
      FROM cows as cow
      LEFT JOIN Children as child
        on child.cow_id = cow.cow_id
Group by cow_ID, child_Id with rollup) z
WHERE Cow_ID is not null