将不具有 6 个或更多同名不同地址的记录放入新表中

发布于 2024-09-29 13:07:51 字数 730 浏览 3 评论 0原文

如果重复的名称超过 6 个，并且同一名称有 6 个不同的地址，那么我不想将它们输出到 KeepThese 表：

harry baker 1 street
harry baker 2 street
harry baker 3 street
harry baker 4 street
harry baker 5 street
harry baker 6 street
donald ross 11th street

因此应该从新表中排除 harry Baker。唐纳德·罗斯应包含在新表中。

这是我现有的查询：

SELECT F.* INTO KeepThese
FROM final_output AS F 
INNER JOIN (SELECT DISTINCT F.fullName FROM final_output 
AS F LEFT JOIN (SELECT fullName FROM final_output 
AS F GROUP BY fullName HAVING COUNT(*) >=6)  
AS NamesToReject ON NamesToReject.fullName = F.fullName WHERE NamesToReject.ID IS NULL)  
AS NamesToKeep ON NamesToKeep.fullName  = F.fullName;

这返回“输入参数值 NamesToReject.ID” 但这是我能想到的最好的。

原文

If the names repeated is more than 6 and there are six different addresses for that same name, then I don't want to output them to the KeepThese table:

harry baker 1 street
harry baker 2 street
harry baker 3 street
harry baker 4 street
harry baker 5 street
harry baker 6 street
donald ross 11th street

So harry baker should be excluded from new table. donald ross should be included in new table.

This is existing query that I have:

SELECT F.* INTO KeepThese
FROM final_output AS F 
INNER JOIN (SELECT DISTINCT F.fullName FROM final_output 
AS F LEFT JOIN (SELECT fullName FROM final_output 
AS F GROUP BY fullName HAVING COUNT(*) >=6)  
AS NamesToReject ON NamesToReject.fullName = F.fullName WHERE NamesToReject.ID IS NULL)  
AS NamesToKeep ON NamesToKeep.fullName  = F.fullName;

This returns "Enter Parameter Value NamesToReject.ID"
But this is best I could come up with.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

悲喜皆因你 2024-10-06 13:07:52

怎么样：

SELECT *
FROM final_output AS f 
LEFT JOIN (
     SELECT t.fullname 
     FROM final_output AS t 
     GROUP BY t.fullname 
     HAVING Count(t.fullname)>=6)  AS Ex 
ON f.fullname=Ex.fullname
WHERE Ex.fullname Is Null

到目前为止，任何少于六个相同名称的名称和地址组合都会显示。

如果添加下一条语句，则将仅显示每个名称中的一个以及随机选择的该名称的一个地址。

AND Nz([address],"None") In (
     SELECT TOP 1 Nz(address,"None") 
     FROM  final_output t 
     WHERE t.fullname = f.fullname 
     ORDER BY fullname, Nz(address,"None") )

How about :

SELECT *
FROM final_output AS f 
LEFT JOIN (
     SELECT t.fullname 
     FROM final_output AS t 
     GROUP BY t.fullname 
     HAVING Count(t.fullname)>=6)  AS Ex 
ON f.fullname=Ex.fullname
WHERE Ex.fullname Is Null

Up to this point, any name and address combination with less than six of the same name will be shown.

If this next statement is added, only one of each name will be shown with one address for that name, chosen at random.

AND Nz([address],"None") In (
     SELECT TOP 1 Nz(address,"None") 
     FROM  final_output t 
     WHERE t.fullname = f.fullname 
     ORDER BY fullname, Nz(address,"None") )

回复收藏 0 原文

~没有更多了~