当前位置:  数据库>sqlserver

having与子查询 查询各门课程超过80分的学生姓名

    来源: 互联网  发布时间:2014-08-29

    本文导语:  一,表结构及内容   代码示例: CREATE TABLE IF NOT EXISTS `student` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(10) NOT NULL,   `subject` varchar(10) NOT NULL,   `score` int(11) NOT NULL,   PRIMARY KEY (`id`) ) #--插入数据 INSERT INTO `student` (`id`, `n...

一,表结构及内容
 

代码示例:

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `subject` varchar(10) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

#--插入数据
INSERT INTO `student` (`id`, `name`, `subject`, `score`) VALUES
(1, '小明', '英文', 80),
(2, '小明', '数学', 79),
(3, '小明', '语文', 81),
(4, '小刚', '英文', 80),
(5, '小刚', '数学', 80),
(6, '小刚', '语文', 80),
(7, '小红', '英文', 90),
(8, '小红', '数学', 90),
(9, '小红', '语文', 81);

二,查询各门课程超过80分的学生姓名,使用having子句、子查询。
 

代码示例:

#方法一
select name from student group by name having in(score)>80

#方法二
select distinct name from student where name not in (
    select name from student where score80 group by name having COUNT(*)>1


    
 
 

您可能感兴趣的文章:

 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • 菜鸟发问:HAVE_SYS_TYPES_H
  • 关于HAVE_XXX的疑问?
  • sql筛选记录的语句之where、having的区别
  • you do not have vmware tools installed
  • telnet进去显示you have new mail
  • ”You do not have UNIX manpages installed“是什么问题??
  • You don't have pkg.m4 properly installed
  • group by,having,order by的用法详解
  • The following packages have unmet dependencies:问题
  • eth0: You don't have permission to capture on that device
  • have a good day! easy question.
  • 求助:he program does not have an entry point or
  • ERROR 1222 (21000): The used SELECT statements have a different number of columns
  • 硬盘安装提示Must have a / partition to install on
  • JB7制作exe时:Exectuable must have a main class.
  • SQL 中having 和where的区别分析
  • 用useBean jdbc 解决数据库问题编译总出错!!!!please,have a look!
  • helpwhy my solaris users do not have its own dirctory ?
  • I have two question about linux9!
  • 简单问题:You don't have permission to access /index.htm on this server?


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3