In the student performance chart, the number of courses with more than 85 grades is shown, and the numbers of courses that fail to pass the number of students who are 10 or more are counted.

From: http://topic.csdn.net/u/20091221/22/240B2929-8F9E-430C-824C-7A4865954E33.html

In the student performance chart, the number of courses with more than 85 grades is shown, and the numbers of courses that fail to pass the number of students who are 10 or more are counted.

Use tempdb if object_id (' TB ') are NOT null drop table TB go CREATE TABLE TB ([stud_id] varchar (m), [course_id] int,[grade] I NT) INSERT INTO TB Select ' 001 ', 1,45 UNION ALL SELECT ' 002 ', 1,88 UNION ALL SELECT ' 003 ', 1,90 UNION ALL SELECT ' 001 ', 2,30 u Nion all SELECT ' 002 ', the 2,50 union ALL SELECT ' 003 ', the 2,30 union ALL SELECT ' 004 ', the 2,30 union ALL SELECT ' 005 ', the 2,50 union all s Elect ' 006 ', 2,55 UNION ALL SELECT ' 007 ', 2,30 UNION ALL Select ' 008 ', 2,95 UNION ALL SELECT ' 009 ', 2,30 UNION ALL SELECT ' 010 ', 2,44 UNION ALL SELECT ' 011 ', 2,33 UNION ALL Select ' 012 ', 2,30 go--select * from TB Select course_id to TB GROUP by Cou RSE_ID has Max (grade) >/* course_id-----------1 2 * intersect select course_id from TB GROUP by course_id hav ing sum (case when grade < then 1 else 0 end) >/* * course_id----------------------2 11 (1 rows affected) * * Meet select course_id from TB GROUP by COURSE_ID have Max (grade) > intersect select course_id from TB GROUP by Cour SE_ID has sum (case whEn grade < then 1 else 0 end) > Ten/* course_id-----------2 */