一道面试题碰到两次了,一直不知道怎么答
题目:
#
# Structure for the `学生表` table :
#
#CREATE TABLE `xuesheng` (
`xueshengId` int(11) NOT NULL,
`name` char(20) default NULL,
PRIMARY KEY (`xueshengId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#
# Structure for the `成绩表` table :
#
#
CREATE TABLE `chengji` (
`chengjiid` int(11) NOT NULL,
`xueshengId` int(11) default NULL,
`chengji` int(11) default NULL,
`kecheng` varchar(30) default NULL,
PRIMARY KEY (`chengjiid`),
KEY `FK_Reference_1` (`xueshengId`),
CONSTRAINT `FK_Reference_1` FOREIGN KEY (`xueshengId`) REFERENCES `xuesheng` (`xueshengId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#
# 模拟数据
#INSERT INTO `xuesheng` (`xueshengId`, `name`) VALUES
(1,'张三'),
(2,'李四');COMMIT;#
# 模拟数据
#INSERT INTO `chengji` (`chengjiid`, `xueshengId`, `chengji`, `kecheng`) VALUES
(1,1,50,'语文'),
(2,1,60,'数学'),
(3,1,70,'物理'),
(4,2,70,'语文'),
(5,2,80,'数学'),
(6,2,90,'物理');COMMIT;求学生评价之大于70的学生?
望各位高人指点,
题目:
#
# Structure for the `学生表` table :
#
#CREATE TABLE `xuesheng` (
`xueshengId` int(11) NOT NULL,
`name` char(20) default NULL,
PRIMARY KEY (`xueshengId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#
# Structure for the `成绩表` table :
#
#
CREATE TABLE `chengji` (
`chengjiid` int(11) NOT NULL,
`xueshengId` int(11) default NULL,
`chengji` int(11) default NULL,
`kecheng` varchar(30) default NULL,
PRIMARY KEY (`chengjiid`),
KEY `FK_Reference_1` (`xueshengId`),
CONSTRAINT `FK_Reference_1` FOREIGN KEY (`xueshengId`) REFERENCES `xuesheng` (`xueshengId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;#
# 模拟数据
#INSERT INTO `xuesheng` (`xueshengId`, `name`) VALUES
(1,'张三'),
(2,'李四');COMMIT;#
# 模拟数据
#INSERT INTO `chengji` (`chengjiid`, `xueshengId`, `chengji`, `kecheng`) VALUES
(1,1,50,'语文'),
(2,1,60,'数学'),
(3,1,70,'物理'),
(4,2,70,'语文'),
(5,2,80,'数学'),
(6,2,90,'物理');COMMIT;求学生评价之大于70的学生?
望各位高人指点,
我还不是很懂,能讲解一下吗?
group by xueshengId having avg(chengji)>70);
having av>70 这个是条件,取大于70的