思扣网SQLintern取数师能力测评

SELECT o1.* FROM orders o1, orders o2 WHERE o1.user_id=o2.user_id GROUP BY o1.id HAVING o1.amount > AVG(o2.amount);
SELECT o.* FROM orders o JOIN (SELECT user_id, AVG(amount) avg_amt FROM orders GROUP BY user_id) t ON o.user_id=t.user_id WHERE o.amount > t.avg_amt;
以上全部
SELECT * FROM orders o WHERE amount > (SELECT AVG(amount) FROM orders WHERE user_id=o.user_id);

SELECT u.user_id, SUM(oi.quantity * oi.price) AS total
FROM users u, orders o, order_items oi
WHERE u.user_id = o.user_id AND o.order_id = oi.order_id
GROUP BY u.user_id;
SELECT u.user_id, SUM(oi.quantity * oi.price) AS total
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY u.user_id;
SELECT u.user_id, SUM(IFNULL(oi.quantity * oi.price, 0)) AS total
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY u.user_id;
SELECT u.user_id, IFNULL(SUM(oi.quantity * oi.price), 0) AS total
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY u.user_id;

SELECT s.name FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE c.name = '数学' AND sc.score > 80;
SELECT s.name FROM students s
WHERE s.id IN (SELECT sc.student_id FROM scores sc WHERE sc.score > 80 AND sc.course_id IN (SELECT id FROM courses WHERE name = '数学'));
SELECT s.name FROM students s, scores sc, courses c
WHERE s.id = sc.student_id AND sc.course_id = c.id AND c.name = '数学' AND sc.score > 80;
SELECT s.name FROM students s
WHERE EXISTS (SELECT 1 FROM scores sc JOIN courses c ON sc.course_id = c.id WHERE sc.student_id = s.id AND c.name = '数学' AND sc.score > 80);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id AND table2.status=1;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id AND table2.status=1 WHERE table2.id IS NOT NULL;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.status=1 OR table2.id IS NULL;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.status=1;

马上就要面试了 你到底行不行