我有以下情况:

表员工:

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | Doe       | Finance    | 20
John       | Doe       | R&D        | 20
John       | null      | Finance    | 20
John       | long      | Finance    | 20


我想要每个人都有1行(名字,姓氏),
除非我们的姓氏为null,然后我只想与(First Name,null)进行1行

对于上面的示例,结果是:

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | null      | Finance    | 20


但是,如果我没有该记录,那么结果应该是:

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | Doe       | R&D        | 20
John       | long      | Finance    | 20


我猜答案可能涉及一些Partition By-s,但是我不确定在哪里。

现在我来到这里:

SELECT FirstName,LastName, DEPARTMENT,Salary,RK FROM
(
select * from
    SELECT EXT.*,
    ROW_NUMBER() OVER(PARTITION BY EXT.FirstName,EXT.LastName
    ORDER BY rownum ASC) AS RK
      FROM Employees EXT
)
WHERE RK = 1 ;


谢谢 !

最佳答案

像这样的东西:

SQL> create table person
  2  (
  3    fname varchar2(10),
  4    lname varchar2(10),
  5     dept  varchar2(10),
  6     sal   number
  7  );

Table created.

SQL> insert into person values ('John', 'Doe', 'Finance', 20);

1 row created.

SQL> insert into person values ('John', 'Doe', 'R&D', 20);

1 row created.

SQL> insert into person values ('John', '', 'Finance', 20);

1 row created.

SQL> insert into person values ('John', 'Long', 'Finance', 20);

1 row created.

SQL> insert into person values ('Paul', 'Doe', 'R&D', 30);

1 row created.

SQL> insert into person values ('Paul', 'Doe', 'Finance', 30);

1 row created.

SQL> insert into person values ('Paul', 'Long', 'Finance', 30);

1 row created.

SQL> select fname, lname, dept, sal
  2    from (select fname, lname, dept, sal,has_null,
  3                  row_number() over(partition by fname,
  4                                                  case when has_null = 'N' then lname else null end
  5                                    order by lname desc nulls first) rn
  6             from (select fname, lname,
  7                           nvl(max(case  when lname is null then 'Y'
  8                               end) over(partition by fname), 'N') has_null, dept, sal
  9                      from person))
 10   where rn = 1;

FNAME      LNAME      DEPT              SAL
---------- ---------- ---------- ----------
John                  Finance            20
Paul       Doe        R&D                30
Paul       Long       Finance            30

09-30 15:01
查看更多