MySQL SELECT DISTINCT 的行为不符合预期

发布于 2025-01-02 21:31:14 字数 445 浏览 1 评论 0原文

好吧，这真的让我很困惑。

此查询返回预期结果，尽管有重复的 car_id 数字

SELECT car_id FROM `Updates` ORDER BY `updates`.`created` DESC

。这两个查询返回相同的结果集：

SELECT distinct `Updates`.`car_id` FROM `Updates` ORDER BY `updates`.`created` DESC

SELECT car_id FROM `Updates` GROUP BY car_id ORDER BY `updates`.`created` DESC

请参阅下文了解它们有何不同：

原文

Ok this has really got me confused.

This query returns the expected results albeit with duplicate car_id numbers

SELECT car_id FROM `Updates` ORDER BY `updates`.`created` DESC

These 2 queries return the same set of results:

SELECT distinct `Updates`.`car_id` FROM `Updates` ORDER BY `updates`.`created` DESC

SELECT car_id FROM `Updates` GROUP BY car_id ORDER BY `updates`.`created` DESC

See below though as to how they differ:

分享到QQ

分享到微博

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

发布评论

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

梦明 2025-01-09 21:31:14

我不认为这与您的问题严格相关，但是：

SELECT DISTINCT car_id 
FROM Updates 
ORDER BY created DESC

不是有效的标准 SQL 语法。可能有许多行具有相同的 car_id 和不同的 created 值。订购时应该使用哪一种？

也许您想重写查询以便它返回有意义的结果：

SELECT car_id 
FROM Updates
GROUP BY car_id 
ORDER BY MAX(created) DESC              --- or MIN(created)
                                        -- whatever suits you

I don't think that this is strictly relevant with your problem but:

SELECT DISTINCT car_id 
FROM Updates 
ORDER BY created DESC

is not valid standard SQL syntax. There may be many rows with same car_id and different created values. Which one should be used for the ordering?

Perhaps you want to rewrite the query so it returns meaningful results:

SELECT car_id 
FROM Updates
GROUP BY car_id 
ORDER BY MAX(created) DESC              --- or MIN(created)
                                        -- whatever suits you

回复收藏 0 原文