时间戳

首页 > 解决方案 > 选择具有值的一行并保持具有空值的相同行

sql - 选择具有值的一行并保持具有空值的相同行

问题描述

我有这样的数据：

Date_time OS       Login    Action    Download  Action_detail      Download_detail
09-09-19  Android  User_id  Download  50        Download - Games   20
09-09-19  Android  User_id  Download  50        Download - App     15
09-09-19  Android  User_id  Download  50        Download - Music   30  
09-09-19  Android  User_id  Download  50        Download - Others  20
09-09-19  Android  Guest    Download  20        Download - Games   20
09-09-19  iOS      User_id  Download  10        Download - Others  15
09-09-19  iOS      Guest    Uninstall 15        Download - Games   20
09-09-19  iOS      Guest    Uninstall 15        Download - App     5
10-09-19  iOS      Guest    Uninstall 20        Download - App     15
10-09-19  iOS      Guest    Uninstall 20        Download - App     10

无论如何我可以像这样返回查询：

Date_time OS       Login    Action    Download  Action_detail      Download_detail
09-09-19  Android  User_id  Download  50        Download - Games   20
09-09-19  Android  User_id  Download  (null)    Download - App     15
09-09-19  Android  User_id  Download  (null)    Download - Music   30  
09-09-19  Android  User_id  Download  (null)    Download - Others  20
09-09-19  Android  Guest    Download  20        Download - Games   20
09-09-19  iOS      User_id  Download  10        Download - Others  15
09-09-19  iOS      Guest    Uninstall 15        Download - Games   20
09-09-19  iOS      Guest    Uninstall (null)    Download - App     5
10-09-19  iOS      Guest    Uninstall 20        Download - App     15
10-09-19  iOS      Guest    Uninstall (null)    Download - App     10

下载编号列是下载操作的唯一编号，最后，我想要的是用唯一的日期时间、操作系统、登录、操作作为切片器来总结它。

标签： sqloraclenull

解决方案

您可以使用 LAG 函数来获得所需的结果，如下所示 -

在这里演示

SELECT Date_time,OS,Login,Action,Download,Action_detail,Download_detail, 
CASE 
    WHEN Download = LAG(Download,1) OVER (ORDER BY (SELECT NULL FROM DUAL)) THEN NULL
    ELSE Download
END New_Column
FROM your_table

WINDOW 函数的 ORDER 存在问题，如您所见，我使用“SELECT NULL FROM DUAL”来保持数据的正常 ORDER。但这并不能确认数据的排序以这种方式总是相同的。如果表中有任何列可用于订购，那将是正确的方法。

推荐阅读