SQL/mySQL -差异平均数

Question

我是mySQL的新手，我试图找出MySQL每个家庭4个孩子的平均出生间隔。我有以下表格：

• 家庭(id，last_name)
• 查找(family_id，child_id)
• 儿童(id，first_name，year_of_birth)

我使用了以下查询，但想知道是否有人可以在mySQL中提出更简单的建议(不使用视图)--如果可能的话？也许是一些我没想过的公式/技巧？

SELECT
  AVG(span)
FROM (SELECT
  family_id,
  MAX(year_of_birth) - MIN(year_of_birth) span
FROM (family
INNER JOIN lookup
  ON family.id = lookup.family_id)
INNER JOIN children
  ON children.id = lookup.child_id
WHERE family_id IN (SELECT
  family_id
FROM (family
INNER JOIN lookup
  ON family.id = lookup.family_id)
INNER JOIN children
  ON children.id = lookup.child_id
GROUP BY family_id
HAVING COUNT(first_name) = 4)
GROUP BY family_id) AS derivedTable;

谢谢！

Answer 1

根据属性名称的解释，这应该足够了。在你在生产中使用它之前先测试一下它，我实际上是在猜测这些名字的意思。

SELECT AVG(*)
FROM (
    SELECT MAX(year_of_birth) - MIN(year_of_birth)
    FROM lookup INNER JOIN children ON child_id = id
    GROUP BY family_id
    HAVING COUNT(*) = 4);