如何在列中找到第二个值

Question

如何列出所有姓氏相同的乐队成员的姓名？

该列的值如下所示：

band_NAME
-------------------
Carla Thomas
Stephen E. Rice
Cynthia P. Tree
Richard Anthony Paul
Ann Frances Smith
Lorace Black
Timothy Adam Paul

我知道我们需要使用instr和substr。我只是不明白如何确定位置。

我知道基本格式会像这样：

SELECT band_NAME 
FROM TABLE 
where substr(band_name, ?, instr( ) IN 
    (select substr(band_name, ?, instr( )-1)  
     from table 
     group by SUBSTR(band_NAME , ?, INSTR( )-1 ) 
     HAVING COUNT(* ) > 1 );

但是问题是什么？在instr里面填什么？

非常感谢您的帮助！

- Bob

---

这个应该怎么工作？在“Jerry Lee Lewis”中，名字是“Jerry Lee”，姓氏是“Lewis”，因此您需要在最后一个空格处拆分名字和姓氏。在“Gabriel García Márquez”中，名字是“Gabriel”，姓氏是“García Márquez”，即您需要在第一个空格处拆分。还有像“Juan Luis García Perales”这样的名字，既不是第一个空格也不是最后一个空格来分离名字和姓氏。然后考虑一下中文名字，其中姓氏排在名字前面。 - Thorsten Kettner

是的，我确实不明白如何进行拆分。上面列出的一些名字有一个中间缩写，因此可以在句点后拆分，但对于其他有三个名字的人，我不知道该怎么做。只需要找到一种方法来查找姓氏匹配并显示它们即可。 - Bob

我想说的是，你不能仅凭完整的名字来确定名字和姓氏。你需要更多的信息，比如一个名字和姓氏的词典。（即使在这种情况下，“Paul”既可以是名字也可以是姓氏）。也许你可以接受一些妥协。但是你需要自己定义你可以接受的规则。 - Thorsten Kettner

还有几列相关，但它们都不能确定是名字还是姓氏... - Bob

Answer 1

我假设你的名字和姓氏之间的分隔符是单个空格。字符串中的多个空格属于姓氏的一部分。因此，你可能想要查找第一个空格字符的位置。

使用 instr(str, substr) 返回子字符串的第一次出现的位置。

然后，使用substring(str, pos) 返回从给定位置（由 instr 函数提供）开始的子字符串。

SELECT substring(band_name, instr(band_name, ' '))
FROM yourtable

- Kamil Gosciminski

---

我在发布问题之前尝试过这个方法，但不幸的是它不能正常工作 :( - Bob

@Bob：这并没有告诉我什么。"it doesn't work properly unfortunately" 是什么意思？ - Thorsten Kettner

抱歉，我完全是个新手。当我尝试上面的代码时，它显示“无效”。 - Bob

@Bob，这并不能让我重现任何东西。请在你的问题中附上期望的结果...同时提供完整的代码和错误信息。 - Kamil Gosciminski

Answer 2

试试这个：

SELECT t1.band_NAME
FROM TABLE t1 LEFT JOIN TABLE t2
 ON SUBSTRING_INDEX(t1.band_name, ' ', - 1) = SUBSTRING_INDEX(t2.band_name, ' ', - 1)
WHERE t1.band_name <> t2.band_name

这就像你的伪代码MySQL一样：

SELECT band_NAME FROM TABLE
Where FIND_IN_SET (SUBSTRING_INDEX(band_name, ' ', -1),
 (Select SUBSTRING_INDEX(band_name, ' ', -1) bn
 From TABLE Group by bn
 having Count(bn) > 1
 )
)

SQL Server

SELECT band_NAME FROM TABLE
Where
 SUBSTRING(band_NAME, CHARINDEX(' ', band_NAME) + 1, LEN(band_NAME)) AS [Last Name]
IN
 (Select SUBSTRING(band_NAME, CHARINDEX(' ', band_NAME) + 1, LEN(band_NAME)) AS [Last Name]
 From TABLE Group by [Last Name] -- or SUBSTRING(band_NAME, CHARINDEX(' ', band_NAME) + 1, LEN(band_NAME)) AS [Last Name]
 having Count(*) > 1
 )
)

此外，我认为您可以从STRING_SPLIT中受益。

- Wajih

---

我尝试了一下，对我来说运行得很好。你能确保括号正确关闭了吗？并且你将2个地方的“TABLE”更改为你真实的表名了吗？@Bob - Wajih

使用您提供的查询，它会显示“SQL命令意外结束”。我正在使用SQL Developer，不知道是否有所不同？它无法识别“substring index”，因此我在添加另一个括号后将其替换为substr。然后它说“bn”是一个“无效运算符--不存在”。 - Bob

哦，你在问题中标记了“mysql”@Bob。 - Wajih

@Bob 抱歉，我没有 sql server，所以我尝试按照我的预期编写了代码。请尝试修改后的答案并告诉我结果。 - Wajih

使用更新后的代码，它显示无效的关系运算符。我尝试了两种方法。Sql开发人员不认识“len”，所以我将其更改为length。还将substring更改为substr，charindex更改为instr。不明白为什么它不起作用... - Bob

Answer 3

试一下

 with cte as
(
select band_name, ROW_NUMBER() over(partition by SUBSTRING(band_name,CHARINDEX(' ',band_name),LEN(band_name))  order by band_name) as cnt,
        SUBSTRING(band_name,CHARINDEX(' ',band_name),LEN(band_name)) as lastname
from your_table
)
select band_name 
from cte
where lastname in (select lastname from cte where cnt > 1)

- Akshey Bhat

Answer 4

好的，

最好的解决方案是更改您的模式并将姓氏存储在单独的列中。

同时，您可以通过以下方式获取姓氏，

SELECT
            [band_NAME],
            CASE WHEN CHARINDEX(' ', [band_NAME]) > 0
                THEN
                    RIGHT([band_NAME], CHARINDEX(' ', REVERSE([band_NAME])))
                ELSE
                    [band_NAME]
            END [LastName]
    FROM
            [TABLE]

您可以像这样将它们分组

SELECT
            [LastName],
            COUNT(*)
    FROM
        (
            SELECT
                     [band_NAME],
                     CASE WHEN CHARINDEX(' ', [band_NAME]) > 0
                        THEN
                            RIGHT([band_NAME], CHARINDEX(' ', REVERSE([band_NAME])))
                        ELSE
                            [band_NAME]
                   END [LastName]
                FROM
                   [TABLE]
        ) [TABLEWithLastName]
    GROUP BY
            [LastName];

- Jodrell