时间戳

首页 > 解决方案 > 根据sql中的条件创建新列？

sql - 根据sql中的条件创建新列？

问题描述

   PARENT_ID    ID   YR_MONTH    REWARD
1    1          11   201601        3
2    1          11   201605        9
3    1          13   201609        9
4    2          21   201601        6
5    2          21   201605        15
6    2          21   201609        9
7    3          31   201601        8
8    3          31   201605        9
9    3          32   201609        9
10   3          32   201610        9

我需要创建一个基于奖励列的新列。根据条件，奖励为 9 放置 1 否则为 0。

条件是：

  1. 对于特定的 parent_id，id 检查上一年是否没有高于 9 的奖励如果是，则为 0，否则为 1

  2. 只有前 9 个将被标记为 1，否则为 0

预期成绩：

   PARENT_ID    ID   YR_MONTH    REWARD  REWARD_STATUS
1    1          11   201601        3         0
2    1          11   201605        9         1
3    1          13   201609        9         1
4    2          21   201601        6         0
5    2          21   201605        15        0
6    2          21   201609        9         0
7    3          31   201601        8         0
8    3          31   201605        9         1
9    3          32   201609        9         1
10   3          32   201610        9         0

标签： sqlsql-servertsqlwindow-functions

解决方案

我会使用窗口函数：

select 
    t.*,
    case 
        when reward = 9 
        and sum(case when reward >= 9 then 1 else 0 end)  over(partition by parent_id order by yr_month) = 1
    then 1 else 0 end reward_status
from mytable t

如果满足以下条件，case则表达式返回1：

  • 当前reward记录的是9

  • 并且没有相同parent_id的“先前”记录reward等于或大于9

如果您正在运行 MySQL，则case表达式可以简化为：

(
    reward = 9 
    and sum(reward >= 9)  over(partition by parent_id order by yr_month) = 1
) reward_status

DB Fiddle 上的演示：

父 ID | 身份证 | YR_MONTH | 奖励 | REWARD_STATUS
--------: | -: | --------: | -----: | ------------:
        1 | 11 | 201601 | 3 | 0
        1 | 12 | 201605 | 9 | 1
        1 | 13 | 201609 | 12 | 0
        2 | 21 | 201601 | 6 | 0
        2 | 22 | 201605 | 9 | 1
        2 | 23 | 201609 | 9 | 0
        3 | 31 | 201601 | 15 | 0
        3 | 32 | 201605 | 9 | 0
        3 | 33 | 201609 | 12 | 0
        3 | 34 | 201610 | 9 | 0

推荐阅读