如果名称不止一次，如何排除名称 [英] How to get exclude the name IF the name is more than once

问题描述

这是我的桌子

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

推荐答案

有趣的是使用聚合函数和over子句 -

a计数可以在

Interestingly using an aggregate function and over clause -
a count can be performed on

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
;

As Duncan Edwards Jones [ ^ ]在问题评论中提到，您必须修复 CompanyName中的数据字段。



此刻，我最好的猜测是：

As Duncan Edwards Jones[^] mentioned in the comment to the question, you have to repair data in a CompanyName field.

At this moment, my best guess is:

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

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