我得到一个错误，列不匹配，但我检查了，他们是。 以下是SQL脚本:
SELECT student_no
, firstname
, lastname
, middlename
, sub_classes.name as sub_class_name
FROM
(SELECT payments.students_student_no as student_no
, SUM(payments.amount) as amount
FROM students
JOIN payments
ON payments.students_student_no = students.student_no
JOIN sub_classes
ON students.sub_classes_id = sub_classes.id
WHERE payment_type_id = ?
AND academic_year = ?
AND term = ?
GROUP
BY student_no
UNION ALL
SELECT student_no
, firstname
, lastname
, middlename
, sub_classes.name as sub_class_name
FROM students
JOIN sub_classes
ON students.sub_classes_id = sub_classes.id
GROUP
BY student_no
) results
GROUP
BY student_no
问题在于2个select语句中的列数。 请见下文。
SELECT payments.students_student_no as student_no, SUM(payments.amount) as amount --> number of columns should be same as the below select statement. Also the datatype of each column
FROM students
...
...
GROUP BY student_no
UNION ALL
SELECT student_no, firstname, lastname, middlename, sub_classes.name as sub_class_name --> Number of columns here should be same as the upper select statement. Also the datatype of each column
FROM students
...
GROUP BY student_no
在第二个选项中，为UNION ALL选择9ng列，repect第一个选项。 在下面的代码中，已将（firstname，lastname，middlename，sub_class.name)更改为null
SELECT student_no, firstname, lastname, middlename, sub_classes.name as sub_class_name
FROM (
SELECT payments.students_student_no as student_no, SUM(payments.amount) as amount
FROM students INNER JOIN payments ON payments.students_student_no=students.student_no
INNER JOIN sub_classes ON(students.sub_classes_id=sub_classes.id) WHERE payment_type_id=? AND academic_year=? AND term=? GROUP BY student_no
UNION ALL
SELECT student_no,null
FROM students
INNER JOIN sub_classes ON students.sub_classes_id=sub_classes.id
) results
