mysql - Knex.js MYSQL WHERE 或 UNION

Question

我很难思考这个查询。我只是不太了解MYSQL。我有两张表 - 一张包含学校信息，另一张包含评论信息。

我正在尝试返回选定国家/地区所有学校的表格列表(用户单击 map ，这会调用该查询)。该表列出了一些学校信息和平均评分。在我的评论表中，有一列用于确定评论是否“有效”。意思是用户已经完成并发布了它。

我可以获得平均总数的学校列表，其中包括没有评论的学校，但它也包括未“发布”的平均评论(它只显示空，这就是很好，因为我可以稍后处理)。我还可以获得列出学校和正确平均值的查询，但它会过滤掉没有评论的学校(因为没有值为“1”的列)。

我正在努力思考如何获取我需要的数据。我知道问题出在 WHERE active="1"部分，但我觉得我需要一个 UNION 来连接数据。

我正在尝试获取一个国家/地区所有学校的列表，以及平均分(如果有的话) - 并且只有设置为有效的平均评论，如果没有，仍然返回学校评论。

这是我正在处理的查询:

//This gives me all of the schools with an average, but filters the schools with out a review

  knex.select('schools.SID','schools.schoolName','schools.schoolCity',    
  knex.raw('ROUND(AVG(((Q1+Q2+Q3+Q4+Q5+Q6+Q7+Q8+Q9+Q10)/(52*10)*10)),2) AS average'))
    .from('reviews')
    .rightJoin('schools', 'schools.SID', 'reviews.schoolID')
    .where('schools.schoolCountryCode', countryId)
    .andWhere('reviews.active', '=', 1)
    .groupBy('schools.SID')
    .orderByRaw('average DESC, schools.schoolCity ASC')


//By commenting out the andWhere I get all of the schools and an average, but it includes the reviews in the average that are not published. 

  knex.select('schools.SID','schools.schoolName','schools.schoolCity',    
  knex.raw('ROUND(AVG(((Q1+Q2+Q3+Q4+Q5+Q6+Q7+Q8+Q9+Q10)/(52*10)*10)),2) AS average'))
    .from('reviews')
    .rightJoin('schools', 'schools.SID', 'reviews.schoolID')
    .where('schools.schoolCountryCode', countryId)
    //.andWhere('reviews.active', '=', 1)
    .groupBy('schools.SID')
    .orderByRaw('average DESC, schools.schoolCity ASC')

如果您需要数据/列:

School Table   
SID  schoolName  schoolCity  schoolCountryCode
1    ASIJ        Tokyo       JP
2    St. Mary's  Tokyo       JP

Reviews Table  
RID schoolID active  Q1  Q2  Q3 ...
1   1        1       7   8   9

Answer 1

我自己想出来了。

我可以通过在 SELECT 中包含 IF 语句来过滤并执行 ROUND/AVG，以便计算仅应用于条件。我删除了额外的 andWhere active=1，以便列出所有学校。

我使用的最终查询/knex 语句是:

    var countryId = req.params.id;
    knex.select('schools.SID','schools.schoolName','schools.schoolCity',
    knex.raw('ROUND(AVG(IF(reviews.active = 1, ((Q1+Q2+Q3+Q4+Q5+Q6+Q7+Q8+Q9+Q10+Q11+Q12+Q13+Q14+Q15+Q16+Q17+Q18+Q19+Q20+Q21+Q22+Q23+Q24+Q25+Q26+Q27+Q28+Q29+Q30+Q31+Q32+Q33+Q34+Q35+Q36+Q37+Q38+Q39+Q40+Q41+Q42+Q43+Q44+Q45+Q46+Q47+Q48+Q49+Q50+Q51+Q52)/(52*10)*10), NULL)) ,2) AS average'))
    .from('reviews')
    .rightJoin('schools', 'schools.SID', 'reviews.schoolID')
    .where('schools.schoolCountryCode', countryId)
    .groupBy('schools.SID')
    .orderByRaw('average DESC, schools.schoolCity ASC')