sql - 选择相同计数的位置

Question

如何计算作者数量相同的论文数量？

我想返回作者数量，论文数量，按作者数量升序排序。

这是我的 SQL：

CREATE TABLE Papers(
  PKey INT PRIMARY KEY,
  Title VARCHAR(512) NOT NULL,
  Pages VARCHAR(64),
  CKey INT REFERENCES Conferences,
  JKey INT REFERENCES Journals,
  CHECK((CKey IS NOT NULL AND JKey IS NULL)
    OR (CKey IS NULL AND JKey IS NOT NULL))
);

CREATE TABLE AuthPapers(
  PKey INT REFERENCES Papers,
  AKey INT REFERENCES Persons,
  Rank INT NOT NULL,
  PRIMARY KEY(PKey, AKey),
  CHECK(Rank >= 1)
);

CREATE TABLE Persons(
  AKey INT PRIMARY KEY,
  Name VARCHAR(128) UNIQUE NOT NULL,
  Website VARCHAR(256),
  IKey INT REFERENCES Institutions
);

我如何实现这一目标？

结果

标签： sqlpostgresql

Answer 1

select 
  AuthCount,
  COUNT(Pkey) as PaperCount 
FROM (
      select 
      PKey, 
      COUNT(AKey) as AuthCount 
      FROM AuthPapers 
      GROUP BY PKey) as t 
GROUP BY AuthCount 
ORDER BY AuthCount