mysql 嵌套查询

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE scores (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

SELECT name 
FROM students 
WHERE id IN (
    SELECT student_id 
    FROM scores 
    WHERE score > (
        SELECT AVG(score) 
        FROM scores
    )
);

SELECT s.name, sc.subject, sc.score 
FROM students s 
JOIN (
    SELECT student_id, subject, score 
    FROM scores
) sc ON s.id = sc.student_id;

-- 使用连接代替子查询
SELECT s.name 
FROM students s 
JOIN scores sc ON s.id = sc.student_id 
WHERE sc.score > (
    SELECT AVG(score) 
    FROM scores
);

SELECT name 
FROM students 
WHERE id IN (
    SELECT student_id 
    FROM scores 
    WHERE score > 80
);

SELECT name 
FROM students 
WHERE id = (
    SELECT student_id 
    FROM scores 
    WHERE score > 80 
    LIMIT 1
);