如何在 GROUP BY 中保留重要的列值

发布于 2024-12-05 17:29:19 字数 535 浏览 1 评论 0原文

这是我的示例数据集：

id | messageread | timestamp
 1 |       'yes' |       999
 1 |       'yes' |         0
 1 |        'no' |         0

我正在这样做：

SELECT *
FROM nvm
GROUP BY id

您会注意到数据集已经是ORDER BY timestamp DESC

正如它应该的那样，MySQL 返回第一行。

我想要的是 MySQL 返回第一行，但在其中一个分组行中使用 messageread='no' IF messageread='no' ，无论是否通常返回的行是'yes'或'no'

这对于MySQL来说可能吗？我向自己保证尽可能多地使用 MySQL 而不是 PHP :-) 谢谢！

原文

Here is the example data set i have :

id | messageread | timestamp
 1 |       'yes' |       999
 1 |       'yes' |         0
 1 |        'no' |         0

I'm doing so :

SELECT *
FROM nvm
GROUP BY id

You will note that the data set is already ORDER BY timestamp DESC

As it should, MySQL is returning the first row.

What i'd want is MySQL to return the first row, but with messageread='no' IF messageread='no' in one of the grouped rows, no matter if the normally returned row is 'yes' or 'no'

Is that possible with MySQL? I promised myself to do as much as possible with MySQL and not PHP :-) Thanks!

分享到QQ

分享到微博

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

发布评论

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

冷心人i 2024-12-12 17:29:19

为了确保所有行都来自同一列，请执行以下操作：

SELECT * FROM table1 t1 
LEFT JOIN table1 t2 ON ((t1.messageread, t1.id) < (t2.messageread, t2.id))
WHERE T2.ID IS NULL

这将从 table1 中选择最小或最大行。所有列都来自同一行。
如果它不起作用，您需要将 < 更改为 > 它已经晚了，我无法测试查询，但它应该可以完成这项工作。

警告，前面有反模式
这到处都有臭鸡蛋的味道，但如果你想快速混合搭配，可以这样做：

SELECT id, min(messageread), timestamp as random_timestamp
FROM table1
GROUP BY id

In order to make sure all rows are from the same column do:

SELECT * FROM table1 t1 
LEFT JOIN table1 t2 ON ((t1.messageread, t1.id) < (t2.messageread, t2.id))
WHERE T2.ID IS NULL

This will select the minimum or maximum row from table1. And all columns will be from the same row.
If it doesn't work you need to change the < to a > it's late here and I cannot test the query, but it should do the job.

Warning, antipattern ahead
This has the smell of rotten eggs all over it, but if you want to mix and match fast do:

SELECT id, min(messageread), timestamp as random_timestamp
FROM table1
GROUP BY id

回复收藏 0 原文

笑看君怀她人 2024-12-12 17:29:19

您需要与 ANY 一起执行 CASE 语句。

SELECT
    CASE WHEN 'no' = ANY(SELECT messageread FROM nvm t2 WHEN t2.id = t1.id)
        THEN 'no'
        ELSE 'yes'
        AS messageread,
    timestamp,
FROM nvm t1
GROUP BY t1.id

注意：你基本上在这里得到一个随机时间戳，你想要 MAX(timestamp) 还是其他什么？

You'll need to do a CASE statement along with ANY.

SELECT
    CASE WHEN 'no' = ANY(SELECT messageread FROM nvm t2 WHEN t2.id = t1.id)
        THEN 'no'
        ELSE 'yes'
        AS messageread,
    timestamp,
FROM nvm t1
GROUP BY t1.id

Note: you're basically getting a random timestamp here, do you want MAX(timestamp) or something?

回复收藏 0 原文

~没有更多了~