sql - 同一列中名称第一个字符相同的对

Question

我正在寻找 SQL Query 以在同一列中显示具有相同第一个字符的名称。

Table: User
Column: Names

标签： sqlsql-server

Answer 1

您可以使用以下逻辑 -

Select T1.Names,
       T1.first_char
From
(Select 
      Names,
      substring(Names,1,1) as first_char
    From USER ) T1
 JOIN
  (Select 
      substring(Names,1,1) as first_char
      From USER
      Group by 
       substring(Names,1,1)
       having count(distinct Names) > 1) T2
  ON T1.first_char = T2.first_char;

编辑：如果您想一起显示名称并且使用 SQL Server 2017 或更高版本，您可以执行 -

Select 
       T1.first_char,
       String_Agg(T1.Names,',') within group (order by T1.Names) as Name_list
From
(Select 
      Names,
      substring(Names,1,1) as first_char
    From USER ) T1
 JOIN
  (Select 
      substring(Names,1,1) as first_char
      From USER
      Group by 
       substring(Names,1,1)
       having count(distinct Names) > 1) T2
  ON T1.first_char = T2.first_char
Group by T1.first_char;