我该如何使用嵌套的select语句来查找与ID为11834的人同名的所有人

Question

这是我迄今为止只为一个人准备的。我需要做什么才能使用ID 找到所有同名的人

输入：

SELECT given_name || ' ' || family_name AS "NAME"
FROM customer 
WHERE customer_id = '112524';

输出：

James

我们将非常感谢您的回复谢谢

Answer 1

您可以使用exists:

select c.*
from customers c
where exists (select 1
from customers c2
where c2.given_name = c.given_name and c2.family_name = c.family_name
);

我看不出有什么特别的理由把这些名字连在一起来解决这个问题。

Answer 2

您可以使用自联接：

SELECT c1.* 
FROM customer c1 INNER JOIN customer c2
ON c2.given_name = c1.given_name AND c2.family_name = c1.family_name   
WHERE c2.customer_id = '112524' AND c1.customer_id <> '112524'

如果希望c1.customer_id = '112524'出现在结果中，则可以删除AND c1.customer_id <> '112524'。

Answer 3

您可以使用分析函数来完成此操作，这样您就不需要使用自联接：

SELECT customer_id,
given_name,
family_name
FROM   (
SELECT customer_id,
given_name,
family_name,
COUNT(CASE WHEN customer_id = '112524' THEN 1 END)
OVER ( PARTITION BY given_name, family_name ) AS matches_name
FROM   customer
)
WHERE  matches_name > 0;

对于样本数据：

CREATE TABLE customer ( customer_id, given_name, family_name ) AS
SELECT 112524, 'James', 'Smith' FROM DUAL UNION ALL
SELECT 112523, 'John',  'Smith' FROM DUAL UNION ALL
SELECT 112522, 'James', 'Small' FROM DUAL UNION ALL
SELECT 112521, 'James', 'Smith' FROM DUAL;

输出：

CUSTOMER_ID|GIVEN_NAME|FAMILY_NAME----------：|：--------|：----------詹姆斯|史密斯112521|James | Smith

db<gt；小提琴这里

Answer 4

如果想要嵌套选择，则可以使用exists或IN。

select c.*
from customers c
where (coalesce(given_name,'####'), coalesce(family_name,'####')) in 
-- nested query starts from here 
(select coalesce(c2.given_name,'####'), coalesce(c2.family_name,'####')
from customers c2
where c2.customer_id = '112524' )
And c.customer_id  <> '112524' ;

Db<gt；小提琴

Answer 5

此脚本显示所有共享相同名称的人，包括一个null值。

SELECT * FROM customer b  
WHERE b.given_name ||'##'|| b.family_name IN (
--this is nested query
SELECT a.given_name ||'##' a.family_name FROM customer a WHERE customer_id = '112524')
AND B.customer_id <> '112524'