我有两张类似于下面例子的表格。我写了一个查询来合并这两个表并得到学生的总分。总分由（caone+catwo+examcore）组成。我正在搜索，看看是否有其他更好的方法来解决这方面的性能和语法明智。谢谢
ca表

name  id  course  ca_cat  score
one   1    maths   1       10
one   1    maths   2       6
two   2    maths   1       9
two   2    maths   2       7

检查表

name    id    course     score
one     1      maths       50
two     2      maths       49

我的问题如下

WITH
  firstca AS (
  SELECT
    id,
    name,
    score,
    subject,
  FROM
    ca
  WHERE
    cacount =1 ),
  secondca AS (
  SELECT
    id,
    name,
    score,
    subject,
  FROM
    ca
  WHERE
    cacount=2),
  exam AS (
  SELECT
    id,
    name,
    score,
    subject,
  FROM
    exam),
  totalscore AS (
  SELECT
    fca.studentid,
    fca.name,
    fca.subject,
    fca.score AS firstcascore,
    sca.score AS secondcascore,
    ex.score AS examscore,
    (fca.score +sca.score) AS totalca,
    (fca.score+sca.score+ex.score) AS totalscores,
  FROM
    firstca AS fca
  JOIN
    secondca AS sca
  ON
    fca.studentid=sca.studentid
    AND fca.subject=sca.subject
  JOIN
    exam AS ex
  ON
    fca.studentid=ex.studentid
    AND fca.subject=ex.subject

最终结果表可以与此类似

name    id   course caone  catwo  exam  totalscore
one      1   maths   10     6      50        66
two      2   maths    9     7      49        65

有没有更好的方法来编写这个查询，也许不用with语句，或者使用子查询和联合？
我希望从这里的每一个答案中学习。