mysql - 当数据不完整时，如何按出生日期和死亡日期对人员列表进行排序

Question

我有一份可能有或可能没有出生日期和/或死亡日期的人员名单。我希望能够根据出生日期对它们进行有意义的排序(这是一个主观术语)。

但是 - 如果他们没有出生日期但有死亡日期，我希望将他们整理到与当时去世的其他人最接近的列表中。

我认识到这不是一个离散的操作 - 当某人的出生日期丢失时应该去哪里是不明确的。但大多数时候我正在寻找一个很好的近似值。

这是我想要的示例列表:

Alice     1800     1830
Bob       1805     1845
Carol              1847
Don       1820     1846
Esther    1825     1860

在这个例子中，我很高兴卡罗尔出现在唐之前或之后 - 这是我准备接受的模糊性。重要的结果是，卡罗尔在列表中按照她的死亡日期作为死亡日期进行排序，而不是将死亡日期与出生日期一起排序。

如果我将出生日期和死亡日期合并或以其他方式映射在一起，则不起作用。例如，ORDER BYbirth_date, Death_date 会将 Carol 放在 Esther 之后，这在我看来是很不合适的。

Answer 1

我认为你必须计算人们最终的平均生命周期(对于那些有出生日期和死亡日期的人)。对于没有另一个日期的人，可以从死亡日期中减去它们，或者将它们添加到出生日期中。

在一个查询中执行此操作可能效率不高，而且可能很丑陋，因为 mysql 没有窗口函数。您最好提前计算平均居住年龄。但无论如何，让我们尝试在一个查询中完成此操作:

SELECT   name, birth_date, death_date
FROM     people
ORDER BY COALESCE(
    birth_date,
    DATE_SUB(death_date, INTERVAL (
        SELECT AVG(DATEDIFF(death_date, birth_date))
        FROM people 
        WHERE birth_date IS NOT NULL AND death_date IS NOT NULL
    ) DAY)
)