SQL查找连续季度

Question

对于在最后一季修读课程的议员，我想就该班的费用给予二折优惠(如果一名会员连续上三节课，而她最后两季则可获折扣)。把我给出的所有折扣($)加起来。

以下是数据库中的表：

注册 (class_id，member_id，成本)

类 (class_id，季节，年份)和属性季节可能是春、夏、秋、冬。

数据应该是这样的：

注册

Class_id  Member_id  Cost
--------  ---------  ----
3         3          20 
1         9          15 
2         9          20 
4         10         30 
3         10         10 
5         5          10 
4         9          30 
...

类

class_id  Season  Year 
--------  ------  ----
1         Spring  2008 
2         Fall    2008 
3         Spring  2009 
4         Winter  2008 
5         Summer  2008 
...

Answer 1

首先，您的数据模型给您带来了困难。你需要一种简单的方法来识别连续几个季度，所以，你需要一个表格来保存这些信息，用一个正在增长的键:不然你怎么能指望电脑知道2009年春天是2008年冬季之后的呢？

不管怎么说，这是我的测试数据版本。我用名字来让你更容易看到发生了什么事：

SQL> select s.name as student
  2         , c.name as class
  3         , q.season||' '||q.year as quarter
  4         , q.q_id
  5         , c.base_cost
  6  from  enrolments e
  7          join students s
  8              on (s.s_id = e.s_id)
  9          join classes c
 10              on (c.c_id = e.c_id)
 11          join quarters q
 12              on (q.q_id = c.q_id)
 13  order by s.s_id, q.q_id
 14  /

STUDENT    CLASS                QUARTER               Q_ID  BASE_COST
---------- -------------------- --------------- ---------- ----------
Sheldon    Introduction to SQL  Spring 2008            100        100
Sheldon    Advanced SQL         Spring 2009            104        150
Howard     Introduction to SQL  Spring 2008            100        100
Howard     Information Theory   Summer 2008            101         75
Rajesh     Information Theory   Summer 2008            101         75
Leonard    Crypto Foundation    Autumn 2008            102        120
Leonard    PHP for Dummies      Winter 2008            103         90
Leonard    Advanced SQL         Spring 2009            104        150

8 rows selected.

SQL>

正如您所看到的，我有一个表季度，其主键Q_ID按日历顺序递增一个。

我将使用Oracle语法来解决这个问题，特别是滞后分析函数：

SQL> select s.name as student
  2         , c.name as class
  3         , q.season||' '||q.year as quarter
  4         , q.q_id
  5         , c.base_cost
  6         , lag (q.q_id) over (partition by s.s_id order by q.q_id) prev_q_id
  7  from  enrolments e
  8          join students s
  9              on (s.s_id = e.s_id)
 10          join classes c
 11              on (c.c_id = e.c_id)
 12          join quarters q
 13              on (q.q_id = c.q_id)
 14  order by s.s_id, q.q_id
 15  /

STUDENT    CLASS                QUARTER               Q_ID  BASE_COST  PREV_Q_ID
---------- -------------------- --------------- ---------- ---------- ----------
Sheldon    Introduction to SQL  Spring 2008            100        100
Sheldon    Advanced SQL         Spring 2009            104        150        100
Howard     Introduction to SQL  Spring 2008            100        100
Howard     Information Theory   Summer 2008            101         75        100
Rajesh     Information Theory   Summer 2008            101         75
Leonard    Crypto Foundation    Autumn 2008            102        120
Leonard    PHP for Dummies      Winter 2008            103         90        102
Leonard    Advanced SQL         Spring 2009            104        150        103

8 rows selected.

SQL>

因此，通过查看PREV_Q_ID专栏，我们可以看到霍华德、谢尔顿和伦纳德都上过不止一门课。只有莱纳德上过三门课。通过比较PREV_Q_ID和Q_ID列中的值，我们可以看出霍华德的两门课程都是神圣的，而Sheldon的则不是。

现在我们可以做一些数学了：

SQL> select student
  2          , class
  3          , quarter
  4          , base_cost
  5          , discount*100 as discount_pct
  6          , base_cost - (base_cost*discount) as actual_cost
  7  from
  8          ( select student
  9                  , class
 10                  , quarter
 11                  , base_cost
 12                  , case
 13                      when prev_q_id is not null
 14                           and q_id - prev_q_id = 1
 15                      then 0.2
 16                      else 0
 17                    end       as discount
 18                 , s_id
 19                 , q_id
 20            from
 21                  (
 22                  select s.name as student
 23                         , c.name as class
 24                         , q.season||' '||q.year as quarter
 25                         , q.q_id
 26                         , c.base_cost
 27                         , lag (q.q_id) over (partition by s.s_id order by q.q_id) prev_q_id
 28                         , s.s_id
 29                  from  enrolments e
 30                          join students s
 31                              on (s.s_id = e.s_id)
 32                          join classes c
 33                              on (c.c_id = e.c_id)
 34                          join quarters q
 35                              on (q.q_id = c.q_id)
 36                  )
 37          )
 38  order by s_id, q_id
 39  /

(人为中断以避免需要向下滚动以查看结果)

STUDENT    CLASS                QUARTER      BASE_COST DISCOUNT_PCT ACTUAL_COST
---------- -------------------- ----------- ---------- ------------ -----------
Sheldon    Introduction to SQL  Spring 2008        100            0         100
Sheldon    Advanced SQL         Spring 2009        150            0         150
Howard     Introduction to SQL  Spring 2008        100            0         100
Howard     Information Theory   Summer 2008         75           20          60
Rajesh     Information Theory   Summer 2008         75            0          75
Leonard    Crypto Foundation    Autumn 2008        120            0         120
Leonard    PHP for Dummies      Winter 2008         90           20          72
Leonard    Advanced SQL         Spring 2009        150           20         120

8 rows selected.

SQL>

霍华德和莱纳德连续上课都有折扣，谢尔顿和拉杰没有。

Answer 2

由于您的季度是在几个月内完成的，因此找出“最后两个”是不容易的，所以我假设它们是在查询中指定的。

像这样的事情应该有效：

SET @q0_season = 'Fall';
SET @q0_year = 2011;
SET @q1_season = 'Summer';
SET @q1_year = 2011;
SET @q2_season = 'Spring';
SET @q2_year = 2011;


SELECT DISTINCT e.class_id, e.member_id, e.cost, if (max(e1.member_id) is null, 0, 0.20) * if(max(e2.member_id) is null, 1, 2) discount
FROM enrollment e
INNER JOIN class c 
    ON c.class_id = e.class_id 
    AND c.season = @q0_season AND c.year = @q0_year
LEFT JOIN (enrollment e1 
    INNER JOIN class c1 ON c1.class_id = e1.class_id
        AND c1.season = @q1_season AND c1.year = @q1_year)
    ON e1.member_id = e.member_id
LEFT JOIN (enrollment e2
    INNER JOIN class c2 ON c2.class_id = e2.class_id
        AND c2.season = @q2_season AND c2.year = @q2_year)
    ON e2.member_id = e.member_id
GROUP BY e.class_id, e.member_id, e.cost;

Answer 3

试着做这个

select member_id, cost*0.8 as cost_discount, cost
from Enrollment e inner join
(select member_id, 
    group_concat(season ORDER BY season SEPARATOR ',') as group_season, year as year1 
    from class as c inner join ENROLLMENT as e on (c.class_id=e.class_id) group by Member_id, year
    having group_season='winter,spring,summer' or group_season='spring,summer,fall'
     or LOCATE('fall',group_season) <> 0 
) as t
on t.member_id=e.member_id inner join class as c on (c.class_id=e.class_id)
where year1='2011' and locate(season, substr(group_season, 7))

不幸的是，我没有检查这个语法错误。折扣总额：

select sum(cost - cost_discount) from (first_query);