SELECT stu.* FROM ( SELECT stu_id, COUNT(*) AS num FROM ( SELECT '001' AS stu_id, 'A' AS study_id FROM dual UNION ALL SELECT '001' AS stu_id, 'B' AS study_id FROM dual UNION ALL SELECT '002' AS stu_id, 'B' AS study_id FROM dual UNION ALL SELECT '003' AS stu_id, 'A' AS study_id FROM dual ) GROUP BY stu_id ) stu_a , ( SELECT COUNT(*) AS num FROM ( SELECT 'A'AS study_id, '语文' AS study_name FROM dual UNION ALL SELECT 'B'AS study_id, '数学' AS study_name FROM dual ) ) study , ( SELECT '001' AS stu_id, '王明' AS stu_name FROM dual UNION ALL SELECT '002' AS stu_id, '李三' AS stu_name FROM dual UNION ALL SELECT '003' AS stu_id, '罗斯' AS stu_name FROM dual ) stu WHERE stu_a.num = study.num AND stu_a.stu_id = stu.stu_id
FROM (
SELECT stu_id, COUNT(*) AS num
FROM (
SELECT '001' AS stu_id, 'A' AS study_id FROM dual
UNION ALL
SELECT '001' AS stu_id, 'B' AS study_id FROM dual
UNION ALL
SELECT '002' AS stu_id, 'B' AS study_id FROM dual
UNION ALL
SELECT '003' AS stu_id, 'A' AS study_id FROM dual
)
GROUP BY stu_id
) stu_a
, (
SELECT COUNT(*) AS num
FROM (
SELECT 'A'AS study_id, '语文' AS study_name FROM dual
UNION ALL
SELECT 'B'AS study_id, '数学' AS study_name FROM dual
)
) study
, (
SELECT '001' AS stu_id, '王明' AS stu_name FROM dual
UNION ALL
SELECT '002' AS stu_id, '李三' AS stu_name FROM dual
UNION ALL
SELECT '003' AS stu_id, '罗斯' AS stu_name FROM dual
) stu
WHERE stu_a.num = study.num
AND stu_a.stu_id = stu.stu_id