SQL查询将重复项替换为NULL或空字符串ORACLE

Question

我使用的是oracle，表中有重复的行，如下所示：

Month   Product

March   ENVOY & External Keyboard (22)
March   ENVOY & External Keyboard (22)
March   ENVOY & External Keyboard (22)
March   ENVOY & External Keyboard (22)
March   ENVOY & External Keyboard (22)
March   ENVOY & External Keyboard (22)
April   ENVOY & External Keyboard (22)
April   ENVOY & External Keyboard (22)
April   ENVOY & External Keyboard (22)
April   ENVOY & External Keyboard (22)
April   ENVOY & External Keyboard (22)

我需要把它变成这样的东西：

Month   Product

March   ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)
April   ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)
        ENVOY & External Keyboard (22)

我试着用SELECT (case when row_number() over (partition by times.calendar_month_name order by (select NULL)) = 1 then times.calendar_month_name end) Month，但不工作.有什么想法吗？

Answer 1

您可以使用LAG()窗口函数：

select 
  nullif(Month, lag(Month) over (order by null)) Month,
  Product
from tablename

见演示。

结果：

> MONTH | PRODUCT                       
> :---- | :-----------------------------
> March | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)
> April | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)
>       | ENVOY & External Keyboard (22)

Answer 2

SQL*Plus中的小菜一碟：

SQL> break on month
SQL>
SQL> select month, product from test order by month desc;

MONTH      PRODUCT
---------- ------------------------------
March      ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)
April      ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)
           ENVOY & External Keyboard (22)

11 rows selected.

SQL>

Answer 3

这种类型的操作在应用程序级别确实做得更好。您可以使用row_number()，但是您需要小心：

select (case when seqnum = 1 then month end) as month, product
from (select t.*,
             row_number() over (partition by month order by month) as seqnum
      from t
     ) t
order by month, seqnum;

注意，SQL查询返回的结果是不确定的顺序。因此，您需要一个外部order by来保证排序(即“第一个”行具有该值)。因为您处理的是精确的重复，所以没有唯一的排序键，所以即使是对row_number()的两次调用也会产生不同的结果--因此可以使用子查询。