使用CharIndex函数在列的子字符串上选择Distinct

为了明确戈登的第一点,他说“你可以重复这个表达”,他的意思是:

SELECT 
  LEFT(fullName, CHARINDEX('-',fullName,0)-1) as firstName, 
  count(*) as numOfNames
FROM 
  tblFullNames
GROUP BY 
  LEFT(fullName, CHARINDEX('-',fullName,0)-1)

你不需要 DISTINCT 如果你用的是 GROUP BY 独特的 只是创造了一个 分组依据

如果您的桌子足够大,可以考虑性能,Gordon's CROSS APPLY 会比这更好。

Falsehoods Programmers Believe About Names

cross apply :

 SELECT v.firstName, count(*) as numOfNames
FROM tblFullNames fn CROSS APPLY
     (VALUES (LEFT(fn.fullName, CHARINDEX('-', fn.fullName, 0) - 1)
             )
     ) v(firstName)
GROUP BY v.firstName;