MySQL使用DISTINCT返回其他列

Need a little bit of help with a query.

需要一些查询帮助。

SELECT id,email FROM user_info WHERE username!='' 
     AND email='example@gmail.com' 
     GROUP BY email ORDER BY id DESC LIMIT 1

What this does currently is fetch a distinct email but not the newest ID for an account under this email.

目前这样做的目的是获取不同的电子邮件,但不是此电子邮件下帐户的最新ID。

2 个解决方案

#1

It sounds like you can apply an aggregate to the id field and it will return the most recent id for each email:

听起来您可以将聚合应用于id字段,它将返回每封电子邮件的最新ID:

SELECT Max(id), email 
FROM user_info 
WHERE username!='' 
  AND email='example@gmail.com' 
GROUP BY email

Unfortunately, when you apply a GROUP BY without an aggregate there is no guarantee what id will be returned unless you specify to select the max.

遗憾的是,当您在没有聚合的情况下应用GROUP BY时,除非您指定选择最大值,否则无法保证将返回哪个ID。

If you want to return the username that is associated with the max(id), then you can use a subquery:

如果要返回与max(id)关联的用户名,则可以使用子查询:

SELECT i.MaxId, 
  i.email, 
  u.username
FROM user_info u
inner join
(
  select max(id) MaxId, email
  from user_info
  WHERE username!='' 
    AND email='example@gmail.com' 
  group by email
) i
  on u.id = i.maxid
  and u.email = i.email
WHERE username!='' 
  AND email='example@gmail.com';

#2

-1

If by "newest id" you mean the largest value, then here's one approach that makes use of MySQL user variables to retain the value from previous rows, so a comparison can be made:

如果“最新id”表示最大值,那么这里有一种方法可以利用MySQL用户变量保留前一行的值,因此可以进行比较:

SELECT IF(u.email=@prev_email,1,0) AS dup_email_ind
     , u.id
     , u.username
     , @prev_email := u.email AS email
  FROM user_info u
 CROSS
  JOIN ( SELECT @prev_email := NULL) i
 WHERE u.username != ''
   AND u.email = 'example@gmail.com'
 GROUP 
    BY u.email    DESC
     , u.id       DESC
     , u.username DESC

This returns all of the rows, with an indicator of whether the row is considered an older "duplicate" email or not. Rows that have dup_email_ind = 1 are identified as older duplicates, dup_email_ind = 0 indicates that this row is the latest row (the row with the largest id value) for a given email value.

这将返回所有行,并指示该行是否被视为较旧的“重复”电子邮件。具有dup_email_ind = 1的行被标识为较旧的重复项,dup_email_ind = 0指示该行是给定电子邮件值的最新行(具有最大id值的行)。

(Usually, when I'm looking for duplicates like this, it's helpful for me to return both, or all, of the rows that are "duplicates".)

(通常,当我在寻找像这样的重复项时,返回“重复”的两行或全部行会对我有帮助。)

To return only the rows with the "newest id", wrap the query above (as an inline view) in another query: the output from the query is used a row source for the outer query.)

要仅返回具有“最新id”的行,请将上面的查询(作为内联视图)包装在另一个查询中:查询的输出用作外部查询的行源。)

SELECT d.*
  FROM (
         -- the query above gets put here
       ) d
 WHERE d.dup_mail_ind = 0

Another approach is to use a correlated subqueries in the SELECT list, although this is really only suitable for returning small sets. (This approach can have serious performance issues with large sets.)

另一种方法是在SELECT列表中使用相关子查询,尽管这实际上仅适用于返回小集合。 (对于大型集合,此方法可能会出现严重的性能问题。)

SELECT ( SELECT u1.id
           FROM user_info u1
          WHERE u1.email = e.email
            AND u1.username != ''
          ORDER BY u1.id DESC
          LIMIT 1
        ) AS id
      , ( SELECT u2.username
           FROM user_info u2
          WHERE u2.email = e.email
            AND u2.username != ''
          ORDER BY u2.id DESC
          LIMIT 1
        ) AS username
      , e.email
   FROM ( SELECT u.email
            FROM user_info u
           WHERE u.email = 'example@gmail.com'
             AND u.username != '' 
           GROUP BY u.email
        ) e

#1