MySQL - 将查询更改为仅在 1 列上不同 [英] MySQL - Changing the query to be distinct on just 1 column

问题描述

我有以下查询，但我希望它的行为有所不同.现在它在 url 上查找所有重复的行，并按照重复 url 的数量顺序返回它.我使用 GROUP_ CONCAT 来分隔所有不同的 screen_name.

I have the following query which works except that I would like it to behave differently. Now it looks for all the duplicate rows on url and returns it in the order of number of duplicate urls. I use GROUP_ CONCAT to seperate all the different screen_name's.

但是可以有多行具有相同的 url 和相同的 screen_name.我如何使它只检索 screen_name 不同的行.

However there can be multiple rows with the same url and same screen_name. How do I make it so that it only retreives the rows where screen_name is distinct.

SELECT url, title, GROUP_CONCAT( screen_name ) , COUNT( t_uid )
            FROM `twl_links`
            WHERE twl_uid =3
            AND timestamp >= NOW( ) - INTERVAL 24 HOUR
            GROUP BY (
            url
            )
            ORDER BY COUNT( t_uid ) DESC

推荐答案

您可以使用这样的子查询来限制它:

You could limit it by using a subquery like this:

SELECT t1.url, GROUP_CONCAT( t1.screen_name ) , COUNT( * ) 
FROM (
  SELECT DISTINCT url, screen_name
  FROM mytable
) AS t1
GROUP BY t1.url

它没有给你标题，但由于标题不在 GROUP BY 中，所以我不太清楚那个字段会返回什么.如果您想要这些 URL 的所有标题，您可以将上面的查询与您的表格连接起来.

It doesn't give you the title, but since the title isn't in the GROUP BY, it wasn't really clear to me what that field would return anyway. If you want all the titles of those URLs, you could JOIN the above query with your table.

这篇关于MySQL - 将查询更改为仅在 1 列上不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！