SQL 통계를 구현하는 mysql의 예

테이블 진술

/*Table structure for table `stuscore` */

DROP TABLE IF EXISTS `stuscore`;

CREATE TABLE `stuscore` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` varchar(20) DEFAULT NULL,
  `stuid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `stuscore` */

insert  into `stuscore`(`id`,`name`,`subject`,`score`,`stuid`) values 
(1,'张三','数学','89','1'),
(2,'张三','语文','80','1'),
(3,'张三','英语','70','1'),
(4,'李四','数学','90','2'),
(5,'李四','语文','70','2'),
(6,'李四','英语','80','2'),
(7,'王五','数学','55','3'),
(8,'王五','语文','92','3'),
(9,'王五','英语','74','3'),
(10,'赵六','数学','62','4'),
(11,'赵六','语文','81','4'),
(12,'赵六','英语','93','4');

建表语句

질문:

1. 각 사람의 총점과 순위를 계산합니다. (필수 항목: 이름, 총점)

Answer

1 SELECT a.name, SUM(score) sum_score FROM stuscore a
2 GROUP BY a.name ORDER BY sum_score DESC

2 . 모든 사람의 총점과 순위를 계산합니다(필수 표시 항목: 학번, 이름, 총점)

Answer

1 SELECT a.stuid, a.name, SUM(score) sum_score FROM stuscore a
2 GROUP BY a.name ORDER BY sum_score DESC

3.      단일 과목에서 모든 사람의 최고 점수를 계산합니다(필수 표시 항목: 학번, 이름) , 코스 , 최고등급)

Answer

1 SELECT a.stuid, a.name, a.subject, a.score FROM stuscore a
2 JOIN (
3     SELECT stuid, MAX(score) max_score FROM stuscore GROUP BY stuid4 )b ON a.stuid=b.stuid5 WHERE a.score=b.max_score

4  개인별 평균 성적을 계산합니다. (필수 표시 항목: 학번, 이름, 평균 성적)

Answer

1 SELECT DISTINCT a.stuid, a.name, b.avg_score FROM stuscore a
2 JOIN (
3     SELECT stuid, AVG(score) avg_score FROM stuscore GROUP BY stuid
4 )b ON a.stuid=b.stuid

5. 칼럼 각 과목에서 최고 성적을 받은 학생 (필수 항목 표시: 학번, 이름, 과목, 학년)

Answer

1 SELECT DISTINCT a.stuid, a.name, a.subject, a.score FROM stuscore a
2 JOIN (
3     SELECT subject, MAX(score) max_score FROM stuscore GROUP BY subject
4 )b ON a.subject=b.subject5 WHERE a.score=b.max_score

6. 각 과목에서 최고 성적을 받은 학생 2명을 기재합니다. (필수) 표시) 항목 : 학생번호, 이름, 과목, 학년)

Answer

1 SELECT a.stuid, a.name, a.subject, a.score FROM stuscore a
2 WHERE (
3     SELECT COUNT(1) FROM stuscore b
4     WHERE a.subject=b.subject AND b.score>=a.score
5 ) <= 2
6 ORDER BY a.subject ASC, a.score DESC

7. 통계는 다음과 같습니다.

학생ID	이름	중국어	수학	영어	총점	평균점수

Answer

1 SELECT stuid 学号, NAME 姓名,
2     SUM(CASE WHEN SUBJECT='语文' THEN score ELSE 0 END) 语文,
3     SUM(CASE WHEN SUBJECT='数学' THEN score ELSE 0 END) 数学,
4     SUM(CASE WHEN SUBJECT='英语' THEN score ELSE 0 END) 英语,
5     SUM(score) 总分, (SUM(score)/COUNT(1)) 平均分
6 FROM stuscore GROUP BY 学号

8． 각 과목의 평균 성적을 나열합니다. (필수 표시 항목: 과목, 평균 성적)

Answer

1 SELECT SUBJECT, AVG(score) avg_score FROM stuscore GROUP BY SUBJECT

9. 수학 점수 순위를 나열합니다. (필수 표시 항목: 학번, 이름, 성적, 순위)

Answer

1 SELECT a.*, @var:=@var+1 rank 
2 FROM(
3     SELECT stuid, NAME, score FROM stuscore 
4     WHERE SUBJECT='数学' ORDER BY score DESC
5 )a, (SELECT @var:=0)b

10. 수학 2~3위 학생을 나열합니다. (필수 표시 항목: 학번, 이름, 과목, 학년)

Answer

1 SELECT b.* FROM(
2     SELECT a.* FROM(
3         SELECT stuid, NAME, score FROM stuscore 
4         WHERE SUBJECT='数学' ORDER BY score DESC 
5         LIMIT 3
6     )a ORDER BY score ASC LIMIT 2
7 )b ORDER BY score DESC
8 
9 #注：当数学成绩只有2条以下数据时，此方法失效！

11. John Doe의 수학 점수 순위를 알아보세요

Answer

1 SELECT a.*, @var:=@var+1 rank 
2 FROM(
3     SELECT stuid, NAME, score FROM stuscore 
4     WHERE SUBJECT='数学' ORDER BY score DESC5 )a, (SELECT @var:=0)b
6 WHERE a.name='李四'

12. 통계는 다음과 같습니다:

courses	fail (0-59)	good (60-80)	excellent (81-100)

위 내용은 SQL 통계를 구현하는 mysql의 예의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!