sql - 在列的一部分上应用 group by 并得到矛盾

Question

请考虑这张表:

Id           FullName           Gender
---------------------------------------
1            Tom Hanksi Junior     1
2            Tom Cruisi            2
3            Meril Strippi         2
4            Leo  Dicaprioi        1
5            Robert Deniroi        1
6            Al Pcinoi             1
7            Chanrilize theroni    2
8            Robert Green          1
9            Nicole Kidmani        2
10           Nicole Wagner         2
11           Peter Pan Green       1
12           Peter Viera           1
13           Peter J. Dark         2
14           Tom Henry             1

性别值为:1 代表男性，2 代表女性。

现在我想为名字和性别创建一个表(假设每个名字都应该有一个对应的性别)。

Name       Gender
-----------------
Tom         1
Meril       2
Leo         1
Rebert      1
Al          1
Charlize    2
Nicole      2
Peter       1

1) 我怎样才能将 GROUP BY 应用于全名和性别的名称部分？

2)如何得到名字和性别的矛盾。例如，对于 Tom，我们有 male 和 female 值。

谢谢

Answer 1

我在这里做的一个错误假设是名字是名称中第一个空格之前的部分。

然后从名字中找到名字为

select left(Name, charindex(' ',Name)-1)

你可以根据这个和性别分组

select 
Name=left(Name, charindex(' ',Name)-1),Gender
from
yourTableName
group by
left(Name, charindex(' ',Name)-1),Gender
order by left(Name, charindex(' ',Name)-1),Gender

找到具有两种性别和相同姓名的人，您可以使用

select 
     Name=left(Name, charindex(' ',Name)-1)
from
     yourTableName
group by
     left(Name, charindex(' ',Name)-1)
having count(distinct gender)>1

如果您想同时使用两者，可能在您想要丢弃具有两个性别关联的名称的情况下，您可以执行如下操作

; with NnG as 
(
select 
Name=left(Name, charindex(' ',Name)-1),Gender
from
yourTableName
group by
left(Name, charindex(' ',Name)-1),Gender
),
N2G as 
(
select 
     Name=left(Name, charindex(' ',Name)-1)
from
     yourTableName
group by
     left(Name, charindex(' ',Name)-1)
having count(distinct gender)>1
)

select * from nng left join n2g 
on nng.Name=N2G.name 
where n2g.name is null