我有两个表,类似于下面的例子。我写了一个查询,把这两个表组合起来,得到学生的总分。总分由(caone+catwo+examscore)组成。我正在寻找其他更好的方法来解决这个问题,包括性能和语法方面。谢谢
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语句,或者使用子查询和联合?
我想从这里的每一个答案中吸取教训。
发布于 2020-07-02 06:15:20
下面是用于BigQuery标准SQL的
#standardSQL
SELECT name, id, course, caone, catwo, exam,
caone + catwo + exam AS totalscore
FROM (
SELECT name, id, course,
MAX(IF(ca_cat = 1, t2.score, NULL)) AS caone,
MAX(IF(ca_cat = 2, t2.score, NULL)) AS catwo,
ANY_VALUE(t1.score) AS exam
FROM `project.dataset.exam` t1
JOIN `project.dataset.ca` t2
USING (name, id, course)
GROUP BY name, id, course
) 如果要应用于问题输出中的样本数据,请参见
Row name id course caone catwo exam totalscore
1 one 1 maths 10 6 50 66
2 two 2 maths 9 7 49 65 https://stackoverflow.com/questions/62677476
复制相似问题