mysql ORDER BY MIN（）与id不匹配

I have a database that has the following columns:

我有一个包含以下列的数据库:

-------------------
id|domain|hit_count
-------------------

And I would like to perform this query on it:

我想对它执行此查询:

SELECT id,MIN(hit_count)
  FROM table WHERE domain='$domain'
 GROUP BY domain ORDER BY MIN(hit_count)

I would like this query to give me the id of the row that had the smallest hit_count for $domain. The only problem is that if I have two rows that have the same domain, say www.bestbuy.com, the query will just group by whichever one came first, and then although I will get the correct lowest hit_count, the id may or may not be the id of the row that has the lowest hit_count.

我想这个查询给我一个具有$ domain最小hit_count的行的id。唯一的问题是,如果我有两个具有相同域的行,比如说www.bestbuy.com,那么查询将只按先出先后的那个进行分组,然后虽然我会得到正确的最低hit_count,但id可能或者可能不是hit_count最低的行的id。

Does anyone know of a way for me to perform this query and to get the id that matches up with MIN(hit_count)? Thanks!

有没有人知道我执行此查询并获取与MIN(hit_count)匹配的ID的方法?谢谢!

7 个解决方案

#1

Try this:

SELECT id,MIN(hit_count),domain FROM table GROUP BY domain HAVING domain='$domain'

#2

See, when you're using aggregates, either via aggregate functions (and min() is such a function) or via GROUP BY or HAVING operators, your data is being grouped. In your case it is grouped by domain. You have 2 fields in your select list, id and min(hit_count).

当您使用聚合时,通过聚合函数(和min()是这样的函数)或通过GROUP BY或HAVING运算符,您的数据被分组。在您的情况下,它按域分组。您的选择列表中有2个字段,id和min(hit_count)。

Now, for each group database knows which hit_count to pick, as you've specified this explicitly via the aggregate function. But what about id — which one should be included? MySQL internally wraps such fields into max() aggregate function, which I find an error prone approach. In all other RDBMSes you will get an error for such a query.

现在,为每个组数据库知道要选择哪个hit_count,因为您已通过aggregate函数明确指定了它。但是id怎么样 - 应该包含哪一个? MySQL内部将这些字段包装成max()聚合函数,我发现这是一种容易出错的方法。在所有其他RDBMS中,您将收到此类查询的错误。

The rule is: if you use aggregates, then all columns should be either arguments of aggregate functions or arguments of GROUP BY operator.

规则是:如果使用聚合,则所有列应该是聚合函数的参数或GROUP BY运算符的参数。

To achieve the desired result, you need a subquery:

要获得所需的结果,您需要一个子查询:

SELECT id, domain, hit_count
  FROM `table`
 WHERE domain = '$domain'
   AND hit_count = (SELECT min(hit_count) FROM `table` WHERE domain = '$domain');

I've used backticks, as table is a reserved word in SQL.

我使用了反引号,因为表是SQL中的保留字。

#3

SELECT 
    id, 
    hit_count
FROM 
    table
WHERE 
    domain='$domain'
    AND hit_count = (SELECT MIN(hit_count) FROM table WHERE domain='$domain')

#4

Try this:

SELECT id,hit_count
  FROM table WHERE domain='$domain'
 GROUP BY domain ORDER BY hit_count ASC;

This should also work:

这应该也有效:

select id, MIN(hit_count) from table where domain="$domain";

#5

I had same question. Please see that question below.

我有同样的问题。请在下面看到这个问题。

min(column) is not returning me correct data of other columns

min(列)没有返回其他列的正确数据

You are using a GROPU BY. Which means each row in result represents a group of values. One of those values is the group name (the value of the field you grouped by). The rest are arbitrary values from within that group.
For example the following table:

您使用的是GROPU BY。这意味着结果中的每一行代表一组值。其中一个值是组名称(按组分组的字段的值)。其余的是该组内的任意值。例如下表:

F1   |   F2
1         aa
1         bb
1         cc
2         gg
2         hh

If u will group by F1: SELECT F1,F2 from T GROUP BY F1 You will get two rows:

如果您将按F1分组:从T GROUP BY F1中选择F1,F2您将获得两行:

1    and one value from (aa,bb,cc)
2    and one value from (gg,hh)

If u want a deterministic result set, you need to tell the software what algorithem to apply to the group. Several for example:

如果你想要一个确定性的结果集,你需要告诉软件什么算法适用于该组。例如:

1. MIN
2. MAX
3. COUNT
4. SUM

etc etc

#6

There is a most simplist way your query is OK just modify it with DESC keyword after GROUP BY domain

您的查询是最简单的方式,只需在GROUP BY域之后使用DESC关键字进行修改即可

SELECT
  id,
  MIN(hit_count)
FROM table
WHERE domain = '$domain'
GROUP BY domain DESC
ORDER BY MIN(hit_count) 

Explanation:
When you use group by with aggregate function it always selects the first record but if you restrict it with desc keyword it will select the lowest or last record of that group. For testing puspose use this query that has only group_concat added.

说明:当您使用group by和aggregate函数时,它总是选择第一条记录,但如果使用desc关键字对其进行限制,它将选择该组的最低或最后一条记录。对于测试puspose,请使用仅添加了group_concat的查询。

SELECT
  group_concat(id),
  MIN(hit_count)
FROM table
WHERE domain = '$domain'
GROUP BY domain DESC
ORDER BY MIN(hit_count) 

#7

If you can have duplicated domains group by id:

如果您可以通过ID拥有重复的域组:

SELECT id,MIN(hit_count)
FROM domain WHERE domain='$domain'
GROUP BY id ORDER BY MIN(hit_count)