有三张表:
学生表student(stu_id, name)
成绩表scores(stu_id, subject, score)
补考成绩表(stu_id, subject, score)
其中:
stu_id:ID
name:姓名
subject:课程
score:成绩
输出:
姓名、课程和成绩 (没有补考,以成绩表为准,有补考,以成绩表和补考成绩表的较大者为准)
1. 准备表和数据
这里以MySQL数据库为例
-- 创建学生表
CREATE TABLE `student` (
`stu_id` TINYINT NOT NULL,
`name` varchar(8) NOT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 创建成绩表
CREATE TABLE `scores` (
`stu_id` TINYINT NOT NULL,
`subject` varchar(16) NOT NULL,
`score` TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 创建补考成绩表
CREATE TABLE `scores_sup` (
`stu_id` TINYINT NOT NULL,
`subject` varchar(16) NOT NULL,
`score` TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 插入测试数据
INSERT INTO `student`(
stu_id,
name
)
VALUES
('1', '张三'),
('2', '李四'),
('3', '王五');
INSERT INTO `scores`(
stu_id,
subject,
score
)
VALUES
('1', '语文', 60),
('2', '数学', 65),
('1', '英语', 70),
('1', '数学', 56),
('2', '语文', 49),
('2', '英语', 58),
('3', '语文', 57),
('3', '数学', 54),
('3', '英语', 79);
INSERT INTO `scores_sup`(
stu_id,
subject,
score
)
VALUES
('1', '数学', 78),
('2', '语文', 80),
('2', '英语', 68),
('3', '语文', 53),
('3', '数学', 59);
2. 查询语句:
第一种思路:
a. 成绩表和补考成绩表左联接,利用CASE WHEN来决定输出哪个成绩。
b. 然后和学生表左联接,获取对应的姓名
SELECT
s.name,
a.subject,
CASE
WHEN a.score >= 60 -- 成绩通过,以成绩表为准
THEN a.score
WHEN a.score < b.score -- 成绩没有补考的成绩高,以补考成绩为准
THEN b.score
ELSE a.score -- 成绩比补考的成绩高,以成绩表为准
END AS score
FROM
scores a
LEFT JOIN
scores_sup b -- 成绩表左联接补考表(补考表中可能没有成绩表中的记录)
ON
a.stu_id = b.stu_id
AND a.subject = b.subject
LEFT JOIN
student s -- 左联接学生表
ON
a.stu_id = s.stu_id;
利用MySQL的GREATEST(value1,value2,...),注意,任意一个参数为NULL,结果即为NULL。CASE块可以改写成
CASE
WHEN b.score IS NULL
THEN a.score
ELSE GREATEST(a.score, b.score)
END AS score
第二种思路:
a. 成绩表和补考成绩表联合,利用GROUP BY对学生和课程进行分组,利用MAX函数来获取最好的成绩。
b. 将a的结果作为一个表和学生表左联接,获取对应的姓名
SELECT
t.name,
s.subject,
s.score
FROM
(
SELECT
stu_id,
subject,
MAX(score) AS score
FROM
(
SELECT
*
FROM
scores
UNION
SELECT
*
FROM
scores_sup) u -- 成绩表和补考表联合
GROUP BY
stu_id,
subject) s -- 分组
LEFT JOIN
student t -- 左联接学生表
ON
s.stu_id = t.stu_id;
3. 输出:
+----------+---------+-------+
| name | subject | score |
+----------+---------+-------+
| 张三 | 数学 | 78 |
| 李四 | 语文 | 80 |
| 李四 | 英语 | 68 |
| 王五 | 语文 | 57 |
| 王五 | 数学 | 59 |
| 张三 | 语文 | 60 |
| 李四 | 数学 | 65 |
| 张三 | 英语 | 70 |
| 王五 | 英语 | 79 |
+----------+---------+-------+
分享到:
相关推荐
本文实例讲述了mysql存储过程之case语句用法。分享给大家供大家参考,具体如下: 除了if语句,mysql提供了一个替代的条件语句CASE。 mysql的 CASE语句使代码更加可读和高效。废话不多说,我们先来看下简单case语句的...
使用CASE WHEN进行字符串替换处理 代码如下:mysql> select * from sales; 9 rows in set (0.01 sec) SELECT name AS Name, CASE category WHEN “Holiday” THEN “Seasonal” WHEN “Profession” THEN “Bi_...
首先我们创建数据库表: CREATE TABLE `t_demo` ( `id` int(32) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(2) DEFAULT NULL, `num` int(3) DEFAULT NULL, PRIMARY KEY (`id`) ...
SQL_Sever数据库语句大全.zip 具体SQL语句实例如下 获取当前日期的星期 获取某个字段排序的行号 获取数据库所有表和视图的信息 获取月份的月初月末时间 ...Case When判断语句 Cast数值转文本函数 distinct去重 等等
讲述,sql中如何实现行转列的实例,包含知道固定行数的静态转换,与未知行数的动态转换。
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']' from (select distinct 课程 from tb) as a set @sql = @sql + ' from tb group by 姓名' exec(@sql)...
在SQL查询语句行转列横向显示中access中没有CASE,要用IIF代替 select iif(sex= ‘1 ‘, ‘男 ‘, ‘女 ‘) from tablename select country, sum(case when type='A' then money end) as A, sum(case when type...
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type 显示结果: type ...
在这个例子中,我们将使用windows验证连接到本地SQL Server实例,所使用的数据库将是ImportExcel。 在Specify Table Copy or Query(指定表复制或查询)向导界面中,选择copy data from one or more tables or ...
本文向大家介绍了使用SQL语句提取数据库所有表的表名、字段名的实例代码,在SQLserver 中进行了测试,具体内容如下: --查询所有用户表所有字段的特征 SELECT D.Name as TableName, A.colorder AS ColOrder, A.name...
代码如下: — SQL Server 2000 SELECT a.name AS 字段名, CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = ‘PK’ AND parent_obj = a.id AND name IN (SELECT name FROM sysindexes WHERE indid IN ...
对于所有的需求,当你不知道怎么处理的时候,你...,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 11 MONTH),'%Y-%m') AND `status` = 1 then money else 0 end) as '0' ,
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type 显示结果: type ...
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type 显示结果: type ...
本文实例讲述了MySQL流程函数常见用法。分享给大家供大家参考,具体如下: 流程函数是MySQL相对常用的一类函数, 用户可以使用这类函数在一个...CASE WHEN [value1] THEN[result1]… ELSE[default] END 如果value是真,
现金银行汇总表,这个是在Delphi和SQLSERVER的环境下来模拟的,主要是练习Delphi与SQLSERVER数据库的查询操作,这个查询要稍复杂一些,可参考以下的语句: SQLStr := 'Select a.系统日期,a.摘要,a.单号,a.增加金额...
1.迁移数据 进行数据库移植,SQL Server=>MySQL。SQL Server上有如下的Trigger SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON ... case when calc>= 9 then 3 when calc <9>=4 then 2 when calc <
《数据库处理:基础、设计与实现(第11版)(英文版)》在内容编排和写作风格上新颖,强调学习过程中的乐趣,围绕两个贯穿全书的项目练习,让读者从一开始就能把所学的知识用于解决具体的应用实例。 《数据库处理:基础、...
* JDBC, SQL, Java DB, MySQL * Threads and the Concurrency APIs * I/O, Types, Control Statements, Methods * Arrays, Generics, Collections * Exception Handling, Files * GUI, Graphics, GroupLayout,...