sql - SQL 中避免重复子查询的最佳方法

Question

我有一个简单的两列表。例如，我们可以使用以下内容来构建数据:

CREATE TABLE Duplicates
  (assignmentid varchar(5), questionid varchar(5));

INSERT INTO Duplicates
  (assignmentid, questionid)
VALUES
  ('aaaaa', '11111'),
  ('aaaaa', '22222'),
  ('bbbbb', '22222'),
  ('bbbbb', '33333'),
  ('bbbbb', '33333');

有两行是相同的。还有一个问题出现在多项作业中。后者是一个有效的场景，我正在尝试查询属于多个作业的所有问题。所以我想要的输出是:

  aaaaa, 22222
  bbbbb, 22222

我得到了这个结果:

SELECT main.questionid, sub.assignmentid 
FROM (
   SELECT questionid, count(assignmentid) AS AssignmentCount 
   FROM ( 
      SELECT DISTINCT questionid, assignmentid 
      FROM Duplicates
   ) sub 
   GROUP BY questionid
   HAVING AssignmentCount > 1
) main
INNER JOIN (
     SELECT DISTINCT questionid, assignmentid 
     FROM Duplicates
) sub ON main.questionid = sub.questionid;

如您所见，DISTINCT 子查询被重复了两次。我可以通过使用 WITH 命令来避免这种情况，但我的理解是这并不一定意味着子查询将只执行一次。所以现在我在 StackOverflow 中，想问问是否有人知道运行此查询的更有效方法。

Answer 1

您可以将其简化为:

select *
from duplicates
where questionid in (select questionid
                     from duplicates
                     group by questionid
                     having count(distinct assignmentid) > 1);

子查询返回分配给多个assignmentid 的所有questionid。