个性实用的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