Oracle SQL using Distinct and Group By

Question

我在下面有一个表格

+-----+------+-----------+------------+
| id  | type | last_name | first_name |
+-----+------+-----------+------------+
|   1 | A    | Billy     | John       |
|   2 | B    | Bob       | Joe        |
|   3 | A    | Joe       | Zeb        |
|   4 | C    | Billy     | John       |
| ... | ...  | ...       | ...        |
+-----+------+-----------+------------+

我想返回所有具有相同LAST_NAME和FIRST_NAME但具有不同TYPE的记录。

我是否需要执行子查询以首先获取相同的名称，然后对其进行过滤以查找TYPE？

我想返回的内容：

+-----+------+-----------+------------+
| id  | type | last_name | first_name |
+-----+------+-----------+------------+
|   1 | A    | Billy     | John       |
|   4 | C    | Billy     | John       |
| ... | ...  | ...       | ...        |
+-----+------+-----------+------------+

Answer 1

以下是使用相关子查询的一种可能的方法：

select t.*
from table1 t
where exists 
(
    select 1 from table1 u
    where 
    u.last_name = t.last_name and 
    u.first_name = t.first_name and 
    u.type <> t.type
)

或者，也许使用联接：

select t.*
from table1 t inner join
(
    select u.last_name, u.first_name
    from table1 u
    group by u.last_name, u.first_name
    having min(u.type) <> max(u.type)
) q 
on t.last_name = q.last_name and t.first_name = q.first_name

更改table1以适合您的表名称。

Answer 2

也许我监督了一些东西。你怎么看：

select * from table
group by last_name, first_name
having count(type) = 1

Answer 3

使用窗口函数应该很简单，这些函数从早期版本开始就在 Oracle 中可用：

SELECT x.id, x.type, x.last_name, x.first_name
FROM (
    SELECT t.*, COUNT(DISTINCT type) OVER (PARTITION BY last_name, first_name) cnt
    FROM mytable t
) x WHERE x.cnt > 1

内部查询为

每条记录分配当前名字/姓氏元组的不同类型计数，外部查询拟合计数为 1 的行。

DB小提琴上的演示：

身份证 |类型 |LAST_NAME |FIRST_NAME-: |:--- |:-------- |:--------- 1 |一 |比利 |John      4 |C |比利 |John