## 一、数据库结构：
```sql
CREATE TABLE `chengji` (
  `uid` int(11) NOT NULL,
  `xueke` varchar(255) DEFAULT NULL,
  `chengji` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `xuesheng` (
  `uid` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `chengji` VALUES ('1', '数学', '50.00');
INSERT INTO `chengji` VALUES ('1', '语文', '67.00');
INSERT INTO `chengji` VALUES ('2', '数学', '87.00');
INSERT INTO `chengji` VALUES ('2', '语文', '37.00');
INSERT INTO `chengji` VALUES ('3', '数学', '12.00');
INSERT INTO `chengji` VALUES ('3', '语文', '99.00');
INSERT INTO `chengji` VALUES ('4', '数学', '4.00');
INSERT INTO `chengji` VALUES ('4', '语文', '59.00');
INSERT INTO `chengji` VALUES ('1', '英语', '65.00');
INSERT INTO `chengji` VALUES ('2', '英语', '43.00');
INSERT INTO `chengji` VALUES ('3', '英语', '75.00');
INSERT INTO `chengji` VALUES ('4', '英语', '65.00');

INSERT INTO `xuesheng` VALUES ('1', '小黄牛');
INSERT INTO `xuesheng` VALUES ('2', '默许');
INSERT INTO `xuesheng` VALUES ('3', '宣言');
INSERT INTO `xuesheng` VALUES ('4', '基佬糖');
```

## 二、查询总成绩前三名同学的每科成绩
```sql
SELECT
	A.uid,
	C.name,
	B.xueke,
	B.chengji,
	A.sum
FROM
	(
		SELECT
			A.uid,
			sum(chengji) AS sum
		FROM
			chengji AS A
		GROUP BY
			A.uid
		ORDER BY
			sum DESC
		LIMIT 3
	) AS A
LEFT JOIN chengji AS B ON A.uid = B.uid
LEFT JOIN xuesheng AS C ON A.uid = C.uid
ORDER BY
	A.sum DESC;
```

## 三、查询总成绩前三名同学最高分的前两科成绩
```sql
SELECT
	*
FROM
	(
		SELECT
			A.uid,
			C.name,
			B.xueke,
			B.chengji,
			A.sum,
			count(B.uid) AS length
		FROM
			(
				SELECT
					A.uid,
					sum(chengji) AS sum
				FROM
					chengji AS A
				GROUP BY
					A.uid
				ORDER BY
					sum DESC
				LIMIT 3
			) AS A
		LEFT JOIN chengji AS B ON A.uid = B.uid
		LEFT JOIN chengji AS D ON B.uid = D.uid
		AND B.chengji >= D.chengji
		LEFT JOIN xuesheng AS C ON A.uid = C.uid
		GROUP BY
			B.uid,
			B.chengji
		ORDER BY
			A.sum DESC,
			B.chengji DESC
	) AS E
WHERE
	E.length <= 2;
```

已有0条评论

添加新评论☺

如果认为本文对您有所帮助请赞助本站

支付宝扫一扫赞助

已有0条评论

添加新评论☺