当前位置:首页 > 开发 > 数据库 > 正文

SQL CASE WHEN实例

发表于: 2014-05-22   作者:czj4451   来源:转载   浏览次数:
摘要:    有三张表:       学生表student(stu_id, name)       成绩表scores(stu_id, subject, score)       补考成绩表(stu_id, subjec
   有三张表:

      学生表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 |
+----------+---------+-------+

SQL CASE WHEN实例

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
首先看我准备一张数据表(这是做一个数据测试的表,就相当于它们,一天打好几场球) 没错,就需要得
刚刚同时跑来问我一个sql语句顿时,一看我有点蒙,因为之前没有用过,所以在这里写下学习笔记加深印
我们都知道SQL中适用case when then来转化数据库中的信息 比如 select (case sex when 0 then '男'
例子一: 有一个表,数据如: mysql> select * from tid_action; +----+------+-------------+ |
-- 每月入职人数统计 CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 结果N END AS ALIAS
例子一: 有一个表,数据如: mysql> select * from tid_action; +----+------+-------------+ |
select * from biz_person_impl; --人员表 select * from dm_professional_title; --专家技术职称 s
1.查询 select case when sex = '男' then '1' when sex = '女' then '2' else '3' end 性别编号 fro
SELECT * FROM DCHN_TEAM_GROUP_RELATION T ; SELECT T.CITY_NAME, T.TEAM_NAME, T.TEAM_ID, SUM(CA
例子: select Id,FromUserId,ToUserId,[Message],RecordTime,MessageState, case when MessageStat
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号