如果名称不止一次，如何排除名称

问题描述

这是我的桌子

This is my table

EmpID      EmpName          CompanyName                            CompanyID
123         Josep        Kramer Levin Naftalis & Frankel LLP            468
123         Josep        Thompson Hine LLP                              567
801         Simon        Ogletree Deakins International LLP             222
801         Simon        Ogletree, Deakins, Nash PC                     916
602         alen         Baker Co Ltd                                   732
602         alen         Baker Mcken  Ltd                               242

条件：输出将返回，如果公司名称的第一个单词不超过一次， Ex：Baker和Ogletree 这些单词不止一次，因此它不包含为输出



这样的输出

Condition is : Output will return, if the first word of the company name is not more than once, Ex:Baker and Ogletree these words are more then once so it is not include as Output

My Output like this

EmpID     EmpName           Company Name                            CompanyID
123        Josep       Kramer Levin Naftalis & Frankel LLP            468
123        Josep       Thompson Hine LLP                              567

推荐答案

LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1)
--gets the first word of the company name as used in solution 1 (thanks Maciej Los)

如下所示：

Like so:

with Employee as (
--set up dummy data
	select
		123 EmpID,
		'Josep' EmpName,
		'Kramer Levin Naftalis & Frankel LLP' CompanyName,
		468 CompanyID
	union all select 123, 'Josep', 'Thompson Hine LLP', 567
	union all select 801, 'Simon', 'Ogletree Deakins International LLP', 222
	union all select 801, 'Simon', 'Ogletree, Deakins, Nash PC', 916
	union all select 602, 'alen', 'Baker Co Ltd', 732
	union all select 602, 'alen', 'Baker Mcken  Ltd', 242
)
select * from (
	select
		*,
		count(*) over(partition by LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1)) CompanyFirstWordCnt
	from Employee
) EmployeeCompanyFirstWordCnt
where CompanyFirstWordCnt = 1  --filter when count is 1
;

DECLARE @tmp TABLE(EmpID INT, EmpName VARCHAR(50), CompanyName VARCHAR(155), CompanyID INT)

INSERT INTO @tmp(EmpID, EmpName, CompanyName, CompanyID)
VALUES(123, 'Josep', 'Kramer Levin Naftalis & Frankel LLP', 468),
(123, 'Josep', 'Thompson Hine LLP', 567),
(801, 'Simon', 'Ogletree Deakins International LLP', 222),
(801, 'Simon', 'Ogletree, Deakins, Nash PC', 916),
(602, 'alen', 'Baker Co Ltd', 732),
(602, 'alen', 'Baker Mcken  Ltd', 242)


SELECT EmpID, LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1) AS ShortComanyName
FROM @tmp
GROUP BY EmpID, LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1)
HAVING COUNT(EmpID)=1

以上查询返回：

Above query returns:

EmpID	ShortComanyName
123		Kramer
123		Thompson

这篇关于如果名称不止一次，如何排除名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！