时间戳

首页 > 解决方案 > 删除行中的重复项

sql - 删除行中的重复项

问题描述

我需要一个查询来从下面列出的数据类型中删除重复项。

SurveyDate    Rentaldate    outbound    inbound  vehiclesize  lolarate   kayrate
1/24/19        2/7/19         5          5         small       832        null
1/24/19        2/7/19         5          5         small    **855**       null
1/24/19        2/7/19         5          5         large       831        null
1/24/19        2/7/19         5          5         large    **851**       null
1/24/19        2/7/19         5          5         small       null       432
1/24/19        2/7/19         5          5         small       null     **455**
1/24/19        2/7/19         5          5         large       null       431
1/24/19        2/7/19         5          5         large       null     **451**

要求它获取具有相同车辆大小的所有行的最大值。例如，输出应如下所示：

SurveyDate    Rentaldate    outbound    inbound  vehiclesize  lolarate   kayrate
1/24/19        2/7/19         5          5         small    **855**       null
1/24/19        2/7/19         5          5         large    **851**       null
1/24/19        2/7/19         5          5         small       null     **455**
1/24/19        2/7/19         5          5         large       null     **451**

以下是我为消除重复而整理的查询，让我知道您对此的看法：

select 
    SURVEY_DATE, 
    OUTBOUND, INBOUND, 
    MAX(lola_RATE) AS lola_SM,
    MAX(kay_RATE) AS kay_SM
from TABLE1
group by  OUTBOUND, INBOUND, SURVEY_DATE;

标签： sqloracle

解决方案

很近。但是现在您的查询只返回一行：

SURVEYDATE RENTALDATE   OUTBOUND    INBOUND         LR         KR
---------- ---------- ---------- ---------- ---------- ----------
1/24/19    2/7/19              5          5        855        455

如果您添加vehiclesize到选定的列和 ingroup by子句，它将显示：

SURVEYDATE RENTALDATE   OUTBOUND    INBOUND VEHICLESIZE         LR         KR
---------- ---------- ---------- ---------- ----------- ---------- ----------
1/24/19    2/7/19              5          5 small              855        455
1/24/19    2/7/19              5          5 large              851        451

如果你想单独显示空列，你可以使用这个条件分组：

select SurveyDate, Rentaldate, outbound, inbound, vehiclesize, 
       max(lolarate) lr, max(kayrate) kr
 from table1 
 group by 
       SurveyDate, Rentaldate, outbound, inbound, vehiclesize, 
       case when lolarate is null then 1 end,
       case when kayrate  is null then 1 end

演示

SURVEYDATE RENTALDATE   OUTBOUND    INBOUND VEHICLESIZE         LR         KR
---------- ---------- ---------- ---------- ----------- ---------- ----------
1/24/19    2/7/19              5          5 small              855 
1/24/19    2/7/19              5          5 small                         455
1/24/19    2/7/19              5          5 large              851 
1/24/19    2/7/19              5          5 large                         451

推荐阅读