SQL - 仅选择distinct一列 - 编程

How can I select from a few columns, but only select DISTINCT results from one of these columns? This is what I have now:

如何从几列中进行选择,但只能从其中一列中选择DISTINCT结果?这就是我现在拥有的:

SELECT DISTINCT(ur.user_by), urs.username
    FROM 
          ( SELECT *
            FROM users 
            LIMIT 10
         ) AS ur
      LEFT JOIN 
          people AS urs 
        ON 
          ur.user_by = urs.username 

    LIMIT 10

Although, the DISTINCT(user_by) doesn't work.

虽然,DISTINCT(user_by)不起作用。

3 个解决方案

#1

GROUP BY urs.username with MAX(urs.username) will give you distinct values for username:

具有MAX(urs.username)的GROUP BY urs.username将为您提供用户名的不同值:

SELECT ur.user_by, MAX(urs.username) AS username
FROM 
      ( SELECT *
        FROM users 
        LIMIT 10
     ) AS ur
  LEFT JOIN 
      people AS urs 
    ON 
      ur.user_by = urs.username 
 GROUP BY  ur.user_by
 LIMIT 10;

In order to get distinct values, you have to use GROUP BY with an aggregate function to select only one row for each group. In your case MAX will work find with username and only select one value for each group of user_by.

为了获得不同的值,您必须使用GROUP BY和聚合函数来为每个组仅选择一行。在你的情况下,MAX将使用用户名查找,并且只为每组user_by选择一个值。

Update:

To select only 10 unique users from the users table, you use DISTINCT user_by with LIMIT 10 inside the subquery itself, like this:

要从users表中仅选择10个唯一用户,请在子查询本身内部使用带有LIMIT 10的DISTINCT user_by,如下所示:

SELECT ur.user_by, MAX(urs.username) AS username
FROM 
(
        SELECT DISTINCT user_by
        FROM users 
        LIMIT 10
 ) AS ur
  LEFT JOIN 
      people AS urs 
    ON 
      ur.user_by = urs.username 
 GROUP BY  ur.user_by
 LIMIT 10;

SQL - 仅选择distinct一列

3 个解决方案

#1

更多相关文章

随机推荐