无法通过联接执行SQL子查询 [英] Failure To Execute SQL Subquery With Join

问题描述

请有人告诉我我做错了什么.这是我要执行的查询，但是运行命令时没有任何反应.我是SQL的新手，所以请原谅我的错误(如果有的话).

Please can someone tell me what I'm not doing right. Here is a query I want to execute, but nothing happens when I run the command. I'm new to SQL so pardon my mistake, if any.

 SELECT  t.*,
 COUNT(DISTINCT t.subjects) AS subjectenrollment,
 u.urefnumber,
 u.uresidence
 FROM 
 (
  SELECT r.*,
  @curRank := IF( @prevRank = finalscore, @curRank, @incRank ) AS position,
  @incRank := @incRank + 1,
  @prevRank = finalscore
  FROM studentsreports r, 
  (
   SELECT @curRank := 0, 
   @prevRank = NULL, 
   @incRank := 1
  ) c
  ORDER BY finalscore ASC
 ) t LEFT JOIN studentstbl u ON t.studref = u.urefnumber
 WHERE t.author = :staff
 AND t.studentname = :student
 AND t.academicyr = :year
 AND t.academicterm = :term
 AND t.program = :program
 AND t.classes = :level
 AND t.subjects = :subject;

从代码中可以看出，我正在尝试获取学生记录，并在其中包括各学科位置的列以及提供各学科的学生人数.此外，我想在每个表中包括每个学生的居住状况.

As it can be seen from the code, I'm trying to fetch students records, and include a column for position in each subject, as well as the number of students offering each subject. More over, I want to include each student's residential status, which is also in a different table.

在某个时候，我什至要添加其累计的原始分数，即每个科目的所有分数的总和，但我什至不知道该如何实现.一位朋友已经建议在单独的查询中做到这一点，但是不幸的是，我能做到这一点. 请，我将非常感谢您的帮助.预先感谢！

At a point, I even want to add their accumulated raw scores, that is, the summation of all the marks obtained in each subject, but I don't know how to even make that possible. A friend has already suggestted acheiving that in separate queries, but unfortunately, I could get that well. Please, I will be very grateful for your help. Thanks in advance!

推荐答案

使用派生表和相关子查询来考虑此调整，而无需使用@variables.下面的SQL语句满足以下需求，并在旁边列出了所使用的策略:

Consider this adjustment using derived tables and correlated subqueries without need of @variables. SQL statement below handles following needs with the employed stategy listed alongside:

1. 每个主题中排名/排名的列-最高级别的相关总计数子查询
2. 提供每门课程的学生人数-派生总计数表(内部联接子句)
3. 每个学生的居住状况-按字段分组(从"子句)
4. 每个主题中所有标记的总和-汇总总和表(来自条款)

1. Column for position/rank in each subject - Aggregate Correlated Count Subquery at Top Level
2. Number of students offering each subject - Aggregate Count Derived Table (Inner Join clause)
3. Each student's residential status - Group By Field (From clause)
4. Summation of all marks in each subject - Aggregate Sum Derived Table (From Clause)

SQL (具有绑定的参数)

SELECT main.student_number, main.subjects, main.student_residence,
       main.accum_raw_scores, 
       (SELECT COUNT(*) 
        FROM (SELECT s.studref, s.subjects, SUM(s.finalscore) AS total_score
              FROM studentsreports s
              GROUP BY s.studref, s.subjects) AS sub
        WHERE sub.subjects = main.subjects
        AND sub.total_score >= main.accum_raw_scores) AS subject_rank,
       cnt.subject_student_count
FROM
  (SELECT r.studref AS student_number, r.subjects, u.uresidence AS student_residence, 
          SUM(r.finalscore) AS accum_raw_scores       
   FROM studentreports r
   LEFT JOIN studentstbl u ON r.studref = u.urefnumber
   WHERE r.author = :staff
   AND r.studentname = :student
   AND r.academicyr = :year
   AND r.academicterm = :term
   AND r.program = :program
   AND r.classes = :level
   AND r.subjects = :subject
   GROUP BY r.studref, r.subjects, u.uresidence) main
INNER JOIN 
   (SELECT sub.subjects, COUNT(*) AS subject_student_count 
    FROM studentreports sub 
    GROUP BY sub.subjects) cnt
ON cnt.subjects = main.subjects

这篇关于无法通过联接执行SQL子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！