我有以下情况:
表员工:
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