blocks|key|211660|text|实际上，DISTINCT不会将自身限制为1列，基本上当您说：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|211661|选择不同的a，b|211662|你的意思是，“给我a和b的不同值”..就像多列唯一索引一样|211663|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

actually, DISTINCT does not limit itself to 1 column, basically when you say:

SELECT DISTINCT a, b

What you're saying is, "give me the distinct value of a and b combined" .. just like a multi-column UNIQUE index

blocks|key|211691|text|distinct将确保select子句中的所有值都是唯一的，而不仅仅是user_id。如果您希望将结果限制为单个user_ids，则应按user_id进行分组。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|211692|也许你想要的是：|211693|SELECT+
`c`.`user_id`,
`u`.`username`,
`u`.`avatar_path`,
(SELECT+COUNT(*)+FROM+profiles_comments+c2+WHERE+c2.user_id+=+c.user_id+AND+c2.profile_id+=+1)+AS+`comments_count`+
FROM+`profiles_comments`+AS+`c`+INNER+JOIN+`users`+AS+`u`+ON+u.id+=+c.user_id+
WHERE+(c.profile_id+=+1)+
GROUP+BY+`c`.`user_id`,
`u`.`username`,
`u`.`avatar_path`
ORDER+BY+`u`.`id`+DESC;|code-block|syntax|javascript|211694|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

distinct will ensure that ALL values in your select clause are unique, not just user_id. If you want to limit the results to individual user_ids, you should group by user_id.

Perhaps what you want is:

<pre><code>SELECT 
`c`.`user_id`,
`u`.`username`,
`u`.`avatar_path`,
(SELECT COUNT(*) FROM profiles_comments c2 WHERE c2.user_id = c.user_id AND c2.profile_id = 1) AS `comments_count` 
FROM `profiles_comments` AS `c` INNER JOIN `users` AS `u` ON u.id = c.user_id 
WHERE (c.profile_id = 1) 
GROUP BY `c`.`user_id`,
`u`.`username`,
`u`.`avatar_path`
ORDER BY `u`.`id` DESC;
</code></pre>

blocks|key|211707|text|DISTINCT工作在行级别，而不仅仅是列级别|type|unstyled|depth|inlineStyleRanges|entityRanges|data|211708|如果你只想要一个列的DISTiNCT，那么你必须聚合返回的其余列(MIN，MAX，SUM，AVG等)|211709|SELECT+DISTINCT+(Name),+Min+(ID)
From+MyTable|code-block|syntax|javascript|211710|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

DISTINCT works at a row level, not just a column level

If you want the DISTiNCT of only one column then you will have to aggregate the rest of the columns returned (MIN, MAX, SUM, AVG, etc)

<pre><code>SELECT DISTINCT (Name), Min (ID)
From MyTable
</code></pre>

blocks|key|1618181|text|Distinct将尝试只返回唯一行，在您的示例中，它不会为每个用户id仅返回1行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1618182|http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html|offset|length|1618183|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1T|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

Distinct will try to return only unique rows, it will not return only 1 row per user id in your example.

<a href="http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html</a>

blocks|key|371798|text|你误会了。DISTINCT修饰符应用于整个行-它规定结果集中不会返回两个相同的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|371799|查看您的SQL，您希望在created_at列中看到返回的几个可用值中的哪些值(例如)？预测查询的结果是不可能的。|371800|此外，您还在SELECT中使用了两次profile_comments。看起来您正在尝试获取每个用户评论次数的计数。如果是这样的话，您要做的就是使用聚合查询，该查询在user_id上进行分组，并且只包含那些唯一标识用户的列以及注释计数：|371801|SELECT+user_id，COUNT(*)+FROM+profile_comments+WHERE+profile_id+=1+GROUP+BY+user_id|371802|如果需要，您可以将连接添加到用户以获取用户名，但是，从逻辑上讲，您的结果集不能包括来自profile_comments的其他列，并且仍然只为每个user_id生成一行，除非这些列也以某种方式聚合：|371803|选择user_id，MIN(created_at)为最早，MAX(created_at)为最新，COUNT(*)+FROM+profile_comments+WHERE+profile_id+=1+GROUP+BY+user_id|371804|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

You misunderstand. The DISTINCT modifier applies to the entire row — it states that no two identical ROWS will be returned in the result set.

Looking at your SQL, what value of the several available do you expect to see returned in the created_at column (for instance)? It would be impossible to predict the results of the query as written.

Also, you're using profile_comments twice in your SELECT. It appears that you're trying to obtain a count of how many times each user has commented. If so, what you want to do is use an AGGREGATE query, grouped on user_id and including only those columns that uniquely identify a user along with a COUNT of the comments:

SELECT user_id, COUNT(*) FROM profile_comments WHERE profile_id = 1 GROUP BY user_id

You can add the join to users to get the user name if you want but, logically, your result set cannot include other columns from profile_comments and still produce only a single row per user_id unless those columns are also aggregated in some way:

SELECT user_id, MIN(created_at) AS Earliest, MAX(created_at) AS Latest, COUNT(*) FROM profile_comments WHERE profile_id = 1 GROUP BY user_id

Here is my query:

<pre><code>SELECT 
DISTINCT `c`.`user_id`,
`c`.`created_at`,
`c`.`body`,
(SELECT COUNT(*) FROM profiles_comments c2 WHERE c2.user_id = c.user_id AND c2.profile_id = 1) AS `comments_count`,
`u`.`username`,
`u`.`avatar_path` 
FROM `profiles_comments` AS `c` INNER JOIN `users` AS `u` ON u.id = c.user_id 
WHERE (c.profile_id = 1) ORDER BY `u`.`id` DESC;
</code></pre>

It works. The problem though is with the DISTINCT word. As I understand it, it should select only one row per c.user_id.

But what I get is even 4-5 rows with the same c.user_id column. Where is the problem?

Problem With DISTINCT!

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

下面是我的问题：SELECT DISTINCT `c`.`user_id`,`c`.`created_at`,`c`.`body`,(SELECT COUNT(*) FROM profiles_comments c2 WHERE c2.user_id = c.user_id AND c2.profile_id = 1) AS `comments_count`,`u`.`username`,`u`.

问DISTINCT的问题！
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问DISTINCT的问题！EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问DISTINCT的问题！
EN