来自OpenSQL中另一个表的计算字段

Question

下面的示例在MySql中工作，我如何在OpenSql中实现它？

 SELECT StudentID, Name,
     ( SELECT COUNT(*) FROM StudentExam
      WHERE StudentExam.StudentID = Student.StudentID )
      AS ExamsTaken
   FROM Student
   ORDER BY ExamsTaken DESC;

SAP_BASIS版本: 740-13

Answer 1

为什么不直接使用INNER JOIN和GROUP BY呢？如果您还想显示没有参加考试的学生，那么您可以使用UNION来实现。

SELECT StudentID, Name, COUNT(*) AS ExamsTaken
  FROM Student INNER JOIN StudentExam
  ON Student.StudentID = StudentExam.StudentID
  GROUP BY StudentID, Name
UNION
SELECT StudentID, Name, 0 AS ExamsTaken
  FROM Student
  WHERE StudentID NOT IN (SELECT StudentID FROM StudentExam WHERE StudentID =
    Student.StudentID)
  ORDER BY ExamsTaken DESC.

下面是OpenSQL中的一些真实示例。

REPORT zzz.

START-OF-SELECTION.
  SELECT scarr~carrid, scarr~carrname, COUNT(*) AS examstaken
    FROM scarr INNER JOIN sflight
    ON scarr~carrid = sflight~carrid
    GROUP BY scarr~carrid, scarr~carrname
  UNION
  SELECT scarr~carrid, scarr~carrname, 0 AS examstaken
    FROM scarr
    WHERE carrid NOT IN ( SELECT carrid FROM sflight WHERE carrid = scarr~carrid )
  ORDER BY examstaken DESCENDING " <== applies to the union of both SELECT
  INTO TABLE @DATA(g_tab_result).
BREAK-POINT.

结果如下所示。

​

​