个性实用的SQL语句

1. 查询所有班级，以及所有班级里面所包含的总人数，男生人数，女生人数

SELECT
*
FROM
CLASSES
LEFT
JOIN (select CLASS_ID,Count(*) as STUDENTS_NUMBER from STUDENT group by CLASS_ID)STUDENT_COUNT
ON CLASSES.CLASS_ID = STUDENT_COUNT.CLASS_ID
LEFT
JOIN (select CLASS_ID,Count(*) as MALE_NUM from STUDENT Where STU_GENDER=0 group by CLASS_ID)STUDENT_MALE
ON CLASSES.CLASS_ID = STUDENT_MALE.CLASS_ID
LEFT
JOIN (select CLASS_ID,Count(*) as FEMALE_NUM from STUDENT Where STU_GENDER=1 group by CLASS_ID)STUDENT_FEMALE
ON CLASSES.CLASS_ID = STUDENT_FEMALE.CLASS_ID

（注：其中红色标示为实体表，蓝色标示为临时表）