mysql - 选择 SQL 中最常见的项目

Question

我有一个 mysql 数据库，我想选择所有在“name”和“postcode”上相等的值。并且查询需要选择其他字段中最常见的数据。

如果我有:

name postcode test  test2
a    a        1     2
a    a        1     2
a    a        2     1
a    a        1     1
a    a        1     1

那么这个需要返回

a    a        1     1

因为表中(test)1出现了4次，而(test2)1出现了3次。所以我需要名称和邮政编码相同的列中最常见的数据。

Answer 1

这是我的第一种方法:

select distinct
    name, 
    postcode, 
    (select 
       s.test
    from 
       your_table s
    where
       name s.name = m.name, s.postcode = m.postcode
    group by 
       s.name, s.postcode, s.test
    order by count(*) desc
    limit 1 ) as test,
    (select 
       s.test2
    from 
       your_table s
    where
       name s.name = m.name, s.postcode = m.postcode
    group by 
       s.name, s.postcode, s.test2
    order by  count(*) desc
    limit 1 ) as test2
from your_table m

如果您不需要高性能，这是一个解决方案。如果经常执行此查询，那么您应该寻找另一种方法。

已编辑

如果您需要更高的性能并且还需要不同的行，您可以删除 distinct 并在查询末尾附加 group by name, postcode 子句。查询看起来像:

select ... group by name, postcode

这不是标准的 SQL，但 mysql 允许这样做以获得更好的性能:

Quoting MySQL doc :

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping.