DROP TABLE IF EXISTS
`score`;
CREATE TABLE
`score` (
`s_id` varchar
(20
) NOT NULL DEFAULT
'',
`c_id` varchar
(20
) NOT NULL DEFAULT
'',
`s_score` int
(3
) DEFAULT NULL,
PRIMARY KEY
(`s_id`,
`c_id`)
) ENGINE
=InnoDB DEFAULT CHARSET
=utf8
;
/*Data
for the table
`score` */
insert into
`score`(`s_id`,
`c_id`,
`s_score`) values
('01',
'01',80
),
('01',
'02',90
),
('01',
'03',99
),
('02',
'01',70
),
('02',
'02',60
),
('02',
'03',80
),
('03',
'01',80
),
('03',
'02',80
),
('03',
'03',80
),
('04',
'01',50
),
('04',
'02',30
),
('04',
'03',20
),
('05',
'01',76
),
('05',
'02',87
),
('06',
'01',31
),
('06',
'03',34
),
('07',
'02',89
),
('07',
'03',98
);
SELECT s1.* FROM score s1 WHERE
(
SELECT COUNT
(1
) FROM score s2 WHERE
s1.c_id
=s2.c_id AND s2.s_score
>=s1.s_score
)<=2
ORDER BY s1.c_id,s1.s_score DESC
;
