MySQL GROUP BY 命令

Question

请考虑以下表结构和数据:

+--------------------+-------------+
|     venue_name     |  listed_by  |
+--------------------+-------------+
| My Venue Name      |      1      |
| Another Venue      |      2      |
| My Venue Name      |      5      |
+--------------------+-------------+ 

我目前正在使用 MySQL 的 GROUP BY 功能来仅选择唯一的 field 名称。但是，这只会返回 My Venue Name 的第一次出现，但我想根据条件返回它(在这种情况下 listed_by 字段有一个值 > 2.

基本上这是我想要实现的一些伪代码:

Select all records
Group by name
if grouped, return the occurance with the higher value in listed_by

是否有允许此功能的 SQL 语句？

编辑:我应该提到查询中还涉及其他字段，listed_by 字段也需要在查询的其他地方使用。这是我们使用的原始查询:

SELECT  l1.field_value AS venue_name, 
    base.ID AS listing_id,
        base.user_ID AS user_id,
        IF(base.user_ID > 1, 'b', 'a') AS flag,
        COUNT(img.ID) AS img_num
        FROM ( listingsDBElements l1, listingsDB base )
        LEFT JOIN listingsImages img ON (base.ID = img.listing_id AND base.user_ID = img.user_id and img.active = 'yes')
        WHERE  l1.field_name = 'venue_name'
               AND l1.field_value LIKE '%name%'
               AND base.ID = l1.listing_id
               AND base.user_ID  = l1.user_id
               AND base.ID = l1.listing_id
               AND base.user_ID  = l1.user_id
               AND base.active = 'yes'
    GROUP BY  base.Title  ORDER BY  flag desc,img_num desc

Answer 1

只要您没有提及其他字段 - 这是最简单的解决方案:

  SELECT venue_name,
         MAX(listed_by)
    FROM tblname
   WHERE listed_by > 2
GROUP BY venue_name

对于其他字段，它可能看起来像(假设 venue_name + listed_by 对中没有重复项):

    SELECT *
      FROM tblname t1
INNER JOIN (SELECT venue_name,
                   MAX(listed_by) max_listed_by
              FROM tblname
             WHERE listed_by > 2
          GROUP BY venue_name) t2 ON t1.venue_name = t2.venue_name
                                 AND t1.listed_by = t2.max_listed_by