sql—复制在另一列的某些条件下具有空值的列

deyfvvtc 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(378)

使用的数据库是sql server。
我的表中有以下值：

ID  SESSION_ID  UID CREATED_DATE            MESSAGE_TYPE    MESSAGE                 QID
A10001  s1      U1  2020-05-25 06:14:23.430     U       fjdlasjfljdlajfljda         NULL
A10002  s1      U1  2020-05-25 06:14:29.480     B       fjdlasjfljdlajfljda         NULL
A10003  s1      U1  2020-05-25 06:14:32.317     U       fjdlasjfljdlajfljda         NULL
A10004  s1      U1  2020-05-25 06:14:39.297     B       fjdlasjfljdlajfljda         NULL
A10005  s1      U1  2020-05-25 06:14:43.123     B       fjdlasjfljdlajfljda         NULL
A10006  s1      U1  2020-05-25 06:14:47.420     U       fjdlasjfljdlajfljda         NULL
A10007  s1      U1  2020-05-25 06:15:19.237     B       fjdlasjfljdlajfljda         NULL
A10008  s1      U1  2020-05-25 06:15:29.467     B       fjdlasjfljdlajfljda         NULL
A10009  s1      U1  2020-05-25 06:15:29.467     B       fjdlasjfljdlajfljda         NULL
A10010  s2      U1  2020-05-25 06:15:37.040     U       fjdlasjfljdlajfljda         NULL
A10011  s2      U1  2020-05-25 06:15:53.583     B       fjdlasjfljdlajfljda         NULL
A10012  s2      U1  2020-05-25 06:15:56.520     B       fjdlasjfljdlajfljda         NULL
A10013  s3      U2  2020-05-25 06:16:00.213     U       fjdlasjfljdlajfljda         NULL
A10014  s3      U2  2020-05-25 06:16:03.547     B       fjdlasjfljdlajfljda         NULL
A10015  s3      U2  2020-05-25 06:16:06.430     U       fjdlasjfljdlajfljda         NULL

我正在尝试根据以下条件将id的值插入我的qid列：
消息类型为u-qid不能为空
对于所有子提示消息类型“b”，qid将具有来自id列的值，其中消息类型为“u”
以下是示例输出：

ID  SESSION_ID  UID CREATED_DATE            MESSAGE_TYPE    MESSAGE                 QID
    A10001  s1      U1  2020-05-25 06:14:23.430     U       fjdlasjfljdlajfljda         NULL
    A10002  s1      U1  2020-05-25 06:14:29.480     B       fjdlasjfljdlajfljda         A10001
    A10003  s1      U1  2020-05-25 06:14:32.317     U       fjdlasjfljdlajfljda         NULL
    A10004  s1      U1  2020-05-25 06:14:39.297     B       fjdlasjfljdlajfljda         A10003
    A10005  s1      U1  2020-05-25 06:14:43.123     B       fjdlasjfljdlajfljda         A10003
    A10006  s1      U1  2020-05-25 06:14:47.420     U       fjdlasjfljdlajfljda         NULL
    A10007  s1      U1  2020-05-25 06:15:19.237     B       fjdlasjfljdlajfljda         A10006
    A10008  s1      U1  2020-05-25 06:15:29.467     B       fjdlasjfljdlajfljda         A10006
    A10009  s1      U1  2020-05-25 06:15:29.467     B       fjdlasjfljdlajfljda         A10006
    A10010  s2      U1  2020-05-25 06:15:37.040     U       fjdlasjfljdlajfljda         NULL
    A10011  s2      U1  2020-05-25 06:15:53.583     B       fjdlasjfljdlajfljda         A10010
    A10012  s2      U1  2020-05-25 06:15:56.520     B       fjdlasjfljdlajfljda         A10010
    A10013  s3      U2  2020-05-25 06:16:00.213     U       fjdlasjfljdlajfljda         NULL
    A10014  s3      U2  2020-05-25 06:16:03.547     B       fjdlasjfljdlajfljda         A10013
    A10015  s3      U2  2020-05-25 06:16:06.430     U       fjdlasjfljdlajfljda         NULL

我试过使用 row_number() , ranking . 但运气不好。请帮我解决这个问题。

sql window-functions sql-server tsql gaps-and-islands

来源：https://stackoverflow.com/questions/62013824/replicate-the-a-column-which-has-null-values-on-certain-conditions-on-another-co

1条答案

按热度按时间

hmtdttj41#

您可以使用窗口函数来实现这一点。我建议使用窗口总和，每次
message_type 'U' 来定义组，然后 first_value() :

select
    id, session_id, uid, created_date, message_type, message,
    case when message_type = 'B' 
        then first_value(id) over(partition by grp order by created_date) 
    end qid
from (
    select 
        t.*,
        sum(case when message_type = 'U' then 1 else 0 end) 
            over(order by created_date) grp
    from mytable t
) t

db小提琴演示：

id     | session_id | uid | created_date            | message_type | message             | qid   
:----- | :--------- | :-- | :---------------------- | :----------- | :------------------ | :-----
A10001 | s1         | U1  | 2020-05-25 06:14:23.430 | U            | fjdlasjfljdlajfljda | null  
A10002 | s1         | U1  | 2020-05-25 06:14:29.480 | B            | fjdlasjfljdlajfljda | A10001
A10003 | s1         | U1  | 2020-05-25 06:14:32.317 | U            | fjdlasjfljdlajfljda | null  
A10004 | s1         | U1  | 2020-05-25 06:14:39.297 | B            | fjdlasjfljdlajfljda | A10003
A10005 | s1         | U1  | 2020-05-25 06:14:43.123 | B            | fjdlasjfljdlajfljda | A10003
A10006 | s1         | U1  | 2020-05-25 06:14:47.420 | U            | fjdlasjfljdlajfljda | null  
A10007 | s1         | U1  | 2020-05-25 06:15:19.237 | B            | fjdlasjfljdlajfljda | A10006
A10008 | s1         | U1  | 2020-05-25 06:15:29.467 | B            | fjdlasjfljdlajfljda | A10006
A10009 | s1         | U1  | 2020-05-25 06:15:29.467 | B            | fjdlasjfljdlajfljda | A10006
A10010 | s2         | U1  | 2020-05-25 06:15:37.040 | U            | fjdlasjfljdlajfljda | null  
A10011 | s2         | U1  | 2020-05-25 06:15:53.583 | B            | fjdlasjfljdlajfljda | A10010
A10012 | s2         | U1  | 2020-05-25 06:15:56.520 | B            | fjdlasjfljdlajfljda | A10010
A10013 | s3         | U2  | 2020-05-25 06:16:00.213 | U            | fjdlasjfljdlajfljda | null  
A10014 | s3         | U2  | 2020-05-25 06:16:03.547 | B            | fjdlasjfljdlajfljda | A10013
A10015 | s3         | U2  | 2020-05-25 06:16:06.430 | U            | fjdlasjfljdlajfljda | null

赞(0）回复(0）举报 2021-07-26

我来回答

sql—复制在另一列的某些条件下具有空值的列

1条答案

相关问题

热门标签

最新问答