使用 SQL 对某些行进行平均排序

发布于 2024-11-24 02:04:34 字数 836 浏览 3 评论 0原文

好吧，这是对所有 SQL 专家的挑战：我有一个表，其中有两列兴趣，组和生日。只有某些行分配有组。我现在想要打印按生日排序的所有行，但我也希望具有同一组的所有行最终彼此相邻。执行此操作的唯一半明智的方法是在排序时对组中的所有行使用组的平均出生日期。问题是，这可以用纯 SQL（本例中为 MySQL）来完成，还是需要一些脚本逻辑？

为了说明这一点，使用给定的表：

id | group | birthdate
---+-------+-----------
1  | 1     | 1989-12-07
2  | NULL  | 1990-03-14
3  | 1     | 1987-05-25
4  | NULL  | 1985-09-29
5  | NULL  | 1988-11-11

假设 1987-05-25 和 1989-12-07 的“平均值”是 1988-08-30（这可以通过对日期的 UNIX 时间戳等效项进行平均来找到，然后转换回日期。这个平均值不必完全正确！）。输出应该是：

id | group | birthdate  | [sort_by_birthdate]
---+-------+------------+--------------------
4  | NULL  | 1985-09-29 | 1985-09-29
3  | 1     | 1987-05-25 | 1988-08-30
1  | 1     | 1989-12-07 | 1988-08-30
5  | NULL  | 1988-11-11 | 1988-11-11
2  | NULL  | 1990-03-14 | 1990-03-14

有什么想法吗？

干杯，乔恩

原文

All right, so here's a challenge for all you SQL pros:
I have a table with two columns of interest, group and birthdate. Only some rows have a group assigned to them.
I now want to print all rows sorted by birthdate, but I also want all rows with the same group to end up next to each other. The only semi-sensible way of doing this would be to use the groups' average birthdates for all the rows in the group when sorting. The question is, can this be done with pure SQL (MySQL in this instance), or will some scripting logic be required?

To illustrate, with the given table:

id | group | birthdate
---+-------+-----------
1  | 1     | 1989-12-07
2  | NULL  | 1990-03-14
3  | 1     | 1987-05-25
4  | NULL  | 1985-09-29
5  | NULL  | 1988-11-11

and let's say that the "average" of 1987-05-25 and 1989-12-07 is 1988-08-30 (this can be found by averaging the UNIX timestamp equivalents of the dates and then converting back to a date. This average doesn't have to be completely correct!).
The output should then be:

id | group | birthdate  | [sort_by_birthdate]
---+-------+------------+--------------------
4  | NULL  | 1985-09-29 | 1985-09-29
3  | 1     | 1987-05-25 | 1988-08-30
1  | 1     | 1989-12-07 | 1988-08-30
5  | NULL  | 1988-11-11 | 1988-11-11
2  | NULL  | 1990-03-14 | 1990-03-14

Any ideas?

Cheers,
Jon

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

っ左 2024-12-01 02:04:34

我通常使用 T-SQL 编程，所以如果我没有将日期函数完美地转换为 MySQL，请原谅我：

SELECT
    T.id,
    T.group
FROM
    Some_Table T
LEFT OUTER JOIN (
    SELECT
        group,
        '1970-01-01' +
            INTERVAL AVG(DATEDIFF('1970-01-01', birthdate)) DAY AS avg_birthdate
    FROM
        Some_Table T2
    GROUP BY
        group
    ) SQ ON SQ.group = T.group
ORDER BY
    COALESCE(SQ.avg_birthdate, T.birthdate),
    T.group

I normally program in T-SQL, so please forgive me if I don't translate the date functions perfectly to MySQL:

SELECT
    T.id,
    T.group
FROM
    Some_Table T
LEFT OUTER JOIN (
    SELECT
        group,
        '1970-01-01' +
            INTERVAL AVG(DATEDIFF('1970-01-01', birthdate)) DAY AS avg_birthdate
    FROM
        Some_Table T2
    GROUP BY
        group
    ) SQ ON SQ.group = T.group
ORDER BY
    COALESCE(SQ.avg_birthdate, T.birthdate),
    T.group

回复收藏 0 原文

~没有更多了~