sql - Access 数据库，通过最大问题选择组

Question

在 Access 数据库中...我需要提取下表中 desc 使用最频繁的每个 itemcode 的 itemcode/desc 组合。

最频繁可能意味着只有一个版本(查看 pear 添加的记录)

如果是商品代码 777，我必须稍后决定使用哪个描述版本。如果有多个记录，每个记录都包含描述的单个版本，那肯定会产生额外的问题。

原始问题可能还应该包括返回商品代码的第一行，例如 777，其中商品代码的所有现有记录都包含一个唯一的描述(以便计数始终为 1)。第一行可能并不总是正确的版本 - 但无论如何我都无法自动执行该阶段。

---------------------
itemcode  | desc
---------------------
123       | apple
123       | apple
123       | apple
123       | apple 2
123       | apple-2
001       | orange
001       | orange
001       | ORANGE 1
001       | orange-1
666       | pear
777       | bananananana
777       | banana

所以 - 我希望得到以下结果:

---------------------
itemcode  | desc
---------------------
123       | apple
001       | orange
666       | pear
777       | bananananana

我想我很接近，但下面只获取数据库中出现频率最高的描述，并且只返回一行。

SELECT itemcode, desc, count(desc)
from table
group by itemcode, desc
having count(desc) = 
(
 select max(ct) from 
 (
  select itemcode, desc, count(desc) as ct
  from table
  group by itemcode, desc
  )
);

返回:

---------------------
itemcode  | desc
---------------------
123       | apple

Answer 1

这将通过相关的子查询来工作:

SELECT 
  t.itemcode, t.desc, Count(t.desc) AS CountOfdesc
FROM 
  [table] AS t
GROUP BY 
  t.itemcode, t.desc
HAVING 
  Count(t.desc) IN (
    SELECT TOP 1 
      Count(i.desc)
    FROM 
      [table] AS i
    WHERE 
      i.itemcode = t.itemcode
    GROUP BY 
      i.itemcode, i.desc
    ORDER BY 
      Count(i.desc) DESC
  )
  AND t.desc = (
    SELECT TOP 1 
      i.desc
    FROM 
      [table] AS i
    WHERE 
      i.itemcode = t.itemcode
    GROUP BY 
      i.itemcode, i.desc
    ORDER BY 
      i.desc
  )
;

返回(使用 Access 2003 测试):

itemcode  desc          CountOfdesc001       orange        2123       apple         3666       pear          1777       banana        1

顺便说一句，你真的不应该称一个表为“table”和一个列为“desc”。这些是保留的 SQL 关键字，请避免使用它们以使您的生活更轻松。