SQL 测试一组数据中是否存在某个值|极客教程

SQL 测试一组数据中是否存在某个值，你想根据某一组行记录里是否包含某个特定值来生成一个布尔值。试想这样一个例子，一个学生在一段时间内会参加若干场考试。假设他每 3 个月会参加 3 场考试。只要他通过了任何一场，将返回一个标志（flag）以表示考试通过。如果 3 场都没有通过，也会返回一个标志以表示考试未通过。

SQL 测试一组数据中是否存在某个值问题描述

你想根据某一组行记录里是否包含某个特定值来生成一个布尔值。试想这样一个例子，一个学生在一段时间内会参加若干场考试。假设他每 3 个月会参加 3 场考试。只要他通过了任何一场，将返回一个标志（flag）以表示考试通过。如果 3 场都没有通过，也会返回一个标志以表示考试未通过。我们看一下下面的例子。（这里使用 Oracle 语法生成所需的示例数据；对于 DB2 和 SQL Server，略微调整即可，因为它们也支持窗口函数。）

create view V
as
select 1 student_id,
       1 test_id,
       2 grade_id,
       1 period_id,
       to_date('02/01/2005','MM/DD/YYYY') test_date,
       0 pass_fail
  from dual union all
select 1, 2, 2, 1, to_date('03/01/2005','MM/DD/YYYY'),  1 from dual union all
select 1, 3, 2, 1, to_date('04/01/2005','MM/DD/YYYY'),  0 from dual union all
select 1, 4, 2, 2, to_date('05/01/2005','MM/DD/YYYY'),  0 from dual union all
select 1, 5, 2, 2, to_date('06/01/2005','MM/DD/YYYY'),  0 from dual union all
select 1, 6, 2, 2, to_date('07/01/2005','MM/DD/YYYY'),  0 from dual
　
select *
from V
　
STUDENT_ID TEST_ID GRADE_ID PERIOD_ID TEST_DATE   PASS_FAIL
---------- ------- -------- --------- ----------- ---------
         1       1        2         1 01-FEB-2005         0
         1       2        2         1 01-MAR-2005         1
         1       3        2         1 01-APR-2005         0
         1       4        2         2 01-MAY-2005         0
         1       5        2         2 01-JUN-2005         0
         1       6        2         2 01-JUL-2005         0

仔细观察以上结果集，可以看到这个学生在两个学期里共参加了 6 场考试。他通过了其中一场（1 表示“通过”，0 表示“未通过”），因此他第一个学期的学习成绩算是过关了。他在第二个学期（接下来的 3 个月）没有通过任何一场考试，因此 3 场考试的 PASS_FAIL 列都是 0。你希望返回一个结果集表示这个学生某个学期是否通过了考试。最终你希望得到如下所示的结果集。

STUDENT_ID TEST_ID GRADE_ID PERIOD_ID TEST_DATE   METREQ IN_PROGRESS
---------- ------- -------- --------- ----------- ------ -----------
         1       1        2         1 01-FEB-2005      +           0
         1       2        2         1 01-MAR-2005      +           0
         1       3        2         1 01-APR-2005      +           0
         1       4        2         2 01-MAY-2005      -           0
         1       5        2         2 01-JUN-2005      -           0
         1       6        2         2 01-JUL-2005      -           1

METREQ（表示是否通过）的值是“+”和“-”，表示学生在一个学期（3 个月）内是否通过了至少一场考试。如果一个学生在一个学期内通过了至少一场考试，则 IN_PROGRESS 值为 0。如果没有通过任何一场考试，那么他参加的最后一场考试对应的 IN_PROGRESS 值应该是 1。

SQL 测试一组数据中是否存在某个值解决方案

本问题比较麻烦的地方是，我们必须把同一组的行看作一个整体，而非互相独立的个体。先看一下“问题”部分给出的 PASS_FAIL 值。如果我们逐行做判断，则除了 TEST_ID 2，其余每行的 METREQ 值都是“-”。但事实并非如此。我们必须基于一组行记录做出判断。借助窗口函数 MAX OVER，很容易确认一个学生在一个学期内是否通过了至少一场考试。一旦掌握了这样的信息，布尔值计算就变成了简单的 CASE 表达式问题。

 1  select student_id,
 2         test_id,
 3         grade_id,
 4         period_id,
 5         test_date,
 6         decode( grp_p_f,1,lpad('+',6),lpad('-',6) ) metreq,
 7         decode( grp_p_f,1,0,
 8                 decode( test_date,last_test,1,0 ) ) in_progress
 9    from (
10  select V.*,
11         max(pass_fail)over(partition by
12                       student_id,grade_id,period_id) grp_p_f,
13         max(test_date)over(partition by
14                       student_id,grade_id,period_id) last_test
15    from V
16         ) x

SQL 测试一组数据中是否存在某个值扩展知识

本解决方案的关键在于使用窗口函数 MAX OVER 为每个分组返回最大的 PASS_FAIL 值。因为 PASS_FAIL 值只能是 1 或 0，那么只要学生通过了至少一场考试，则 MAX OVER 就会返回 1。下面展示了具体的查询结果。

select V.*,
       max(pass_fail)over(partition by
                      student_id,grade_id,period_id) grp_pass_fail
  from V
　
STUDENT_ID TEST_ID GRADE_ID PERIOD_ID TEST_DATE   PASS_FAIL GRP_PASS_FAIL
---------- ------- -------- --------- ----------- --------- -------------
         1       1        2         1 01-FEB-2005         0             1
         1       2        2         1 01-MAR-2005         1             1
         1       3        2         1 01-APR-2005         0             1
         1       4        2         2 01-MAY-2005         0             0
         1       5        2         2 01-JUN-2005         0             0
         1       6        2         2 01-JUL-2005         0             0

以上结果集显示该学生在第一个学期通过了至少一场考试，因此第一个学期对应的 GRP_PASS_FAIL 值都被设置成了 1。除此之外，如果学生在一个学期内没有通过任何一场考试，那么该学期最后一场考试对应的 IN_PROGRESS 值应该被设置为 1。我们也可以使用窗口函数 MAX OVER 来实现这一要求。

select V.*,
       max(pass_fail)over(partition by
                      student_id,grade_id,period_id) grp_p_f,
       max(test_date)over(partition by
                      student_id,grade_id,period_id) last_test
  from V
　
STUDENT_ID TEST_ID GRADE_ID PERIOD_ID TEST_DATE   PASS_FAIL GRP_P_F LAST_TEST
---------- ------- -------- --------- ----------- --------- ------- -----------
         1       1        2         1 01-FEB-2005         0       1 01-APR-2005
         1       2        2         1 01-MAR-2005         1       1 01-APR-2005
         1       3        2         1 01-APR-2005         0       1 01-APR-2005
         1       4        2         2 01-MAY-2005         0       0 01-JUL-2005
         1       5        2         2 01-JUN-2005         0       0 01-JUL-2005
         1       6        2         2 01-JUL-2005         0       0 01-JUL-2005

现在，我们已经计算出了这名学生通过了哪个学期的考试以及每个学期最后一场考试的日期，最后只要格式化结果集即可。使用 Oracle 的 DECODE 函数（功能类似 CASE 表达式）计算出最终的 METREQ 和 IN_PROGRESS 列。使用 LPAD 函数确保计算出来的 METREQ 值右对齐。

select student_id,
       test_id,
       grade_id,
       period_id,
       test_date,
       decode( grp_p_f,1,lpad('+',6),lpad('-',6) ) metreq,
       decode( grp_p_f,1,0,
               decode( test_date,last_test,1,0 ) ) in_progress
  from (
select V.*,
       max(pass_fail)over(partition by
                      student_id,grade_id,period_id) grp_p_f,
       max(test_date)over(partition by
                      student_id,grade_id,period_id) last_test
  from V
       ) x
　
STUDENT_ID TEST_ID GRADE_ID PERIOD_ID TEST_DATE   METREQ IN_PROGRESS
---------- ------- -------- --------- ----------- ------ -----------
         1       1        2         1 01-FEB-2005      +           0
         1       2        2         1 01-MAR-2005      +           0
         1       3        2         1 01-APR-2005      +           0
         1       4        2         2 01-MAY-2005      -           0
         1       5        2         2 01-JUN-2005      -           0
         1       6        2         2 01-JUL-2005      -           1