sql - 使用 Postgres 8.3 或更低版本在另一个字段中选择最大值和对应值

Question

所以我必须执行上述操作并且我使用的是 Postgres 8.3，所以我不能使用窗口函数。我不能使用 LIMIT 函数(这是一道作业题)。

我有这样的查询(我省略了 desc 条件):

SELECT MAX(count)
FROM 
(
  SELECT id, count(*) as count FROM memberlist
  WHERE desc = ...some conditions...
  GROUP BY id
) AS STT

成员列表包含每个成员都有描述和工作。成员可以有多个工作。所以上面的查询对每个成员进行计数以查看他们有多少工作(因为每个成员都有自己的 ID)。

现在，我的问题是上面的查询可以很好地获取 1 个人拥有的最大工作数量，但是当我尝试获取该 1 个人的相应姓名时，我遇到了问题。

SELECT mi.name, MAX(count)
FROM 
(
  SELECT id, count(*) as count FROM memberlist
  WHERE desc = ...some conditions...
  GROUP BY id
) STT JOIN memberinfo mi ON (stt.id = mi.id)
GROUP BY mi.name;

上面给出了每个人的所有名字(我认为)，而不仅仅是那些拥有最多工作岗位的人(应该是 3 名成员，每人有 25 个工作岗位)。我使用了 memberlist 中的 id 并将其与 memberinfo 结合起来以获取 memberinfo 中的名称。

那么我怎样才能得到每个拥有最大工作数量的人的名字，所以我有 3 行两列(姓名，工作数量)作为我的解决方案？

我确实做了一个丑陋但有效的替代查询。在这里:

SELECT mi.name, stt.count
FROM 
(
    SELECT id, count(*) as count FROM memberlist 
    WHERE desc = ...some conditions...
    GROUP BY id
) stt
JOIN memberinfo mi ON (mi.id = stt.id)
WHERE stt.count = 
(
  SELECT MAX(count)
  FROM 
  (
    SELECT id, count(*) as count FROM memberlist 
    WHERE desc = ...some conditions...
    GROUP BY id
  ) AS MAXSTT
)

我认为这个解决方案不是很好而且效率低下，所以我希望改进它或使用其他方法。

Answer 1

您可以修改内部查询以加入 memberlist，这样您就可以检索所需的列。然后，您可以通过 having count(*) = (subquery)

将查询限制为那些具有最大计数的查询

SELECT m.id, mi.name, count(*) as count 
FROM memberlist m
JOIN memberinfo mi ON m.id = mi.id
WHERE desc = ...some conditions...
GROUP BY m.id, mi.name
HAVING count(*) = (
    SELECT MAX(count)
    FROM (
        SELECT id, count(*) as count 
        FROM memberlist 
        WHERE desc = ...some conditions...
        GROUP BY id
    ) AS MAXSTT
)