php - 基数违规 : 1222 The used SELECT statements have a different number of columns

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 9 年前。

Improve this question

为什么我会收到此错误基数违规:1222 使用的 SELECT 语句具有不同的列数

$query = "
SELECT DISTINCT s.studentid, s.fname, s.lname, s.studentcode, s.courseid, u.id 
FROM students as s,
courses as c, users as u, studentgrade as sg 
WHERE s.courseid = '$id' and u.id = (SELECT id FROM courses WHERE courseid = '$id') and u.id = (SELECT id FROM users WHERE username = '$nombre')
UNION ALL
SELECT SUM(sg.assignmentpoints) as sum
           FROM assignments as sg
           WHERE sg.courseid = 185
           GROUP BY sg.courseid
 ";

Answer 1

(
        SELECT DISTINCT s.studentid, s.fname, s.lname, s.studentcode, s.courseid, u.id 
        FROM students as s,
        courses as c, users as u, studentgrade as sg 
        WHERE s.courseid = '$id' and u.id = (SELECT id FROM courses WHERE courseid = '$id') and u.id = (SELECT id FROM users WHERE username = '$nombre')
)

- 查询 A。

(
SELECT SUM(sg.assignmentpoints) as sum
           FROM assignments as sg
           WHERE sg.courseid = 185
           GROUP BY sg.courseid
)

- 查询 B。

查询 A 应返回与请求 B 相同的列数。所以在你的情况下:

SELECT DISTINCT s.studentid, s.fname, s.lname, s.studentcode, s.courseid, u.id 
FROM students as s,
courses as c, users as u, studentgrade as sg 
WHERE s.courseid = '$id' and u.id = (SELECT id FROM courses WHERE courseid = '$id') and u.id = (SELECT id FROM users WHERE username = '$nombre')
UNION ALL
SELECT SUM(sg.assignmentpoints) as sum, '', '', '', '', '', '', '', '', ''
           FROM assignments as sg
           WHERE sg.courseid = 185
           GROUP BY sg.courseid

我认为你需要Join ，不是Union .

SELECT DISTINCT s.studentid, s.fname, s.lname, s.studentcode, s.courseid, u.id 
    FROM students as s,
    courses as c, users as u, studentgrade as sg 
    LEFT JOIN 
    (SELECT * 
    FROM assignments 
    GROUP BY courseid
    ) as asg on asg.courseid=s.courseid
    WHERE s.courseid = '$id' and u.id = (SELECT id FROM courses WHERE courseid = '$id') and u.id = (SELECT id FROM users WHERE username = '$nombre')