根据给定的成员列表选择 ID
我有一个用于其他两个表（一个客户表和一个组表）之间的链接/关系的表。一个组由一个或多个客户组成。链接表就像
APP_ID | GROUP_ID | CUSTOMER_ID
1 | 1 | 123
1 | 1 | 124
1 | 1 | 125
1 | 2 | 123
1 | 2 | 125
2 | 3 | 123
3 | 1 | 123
3 | 1 | 124
3 | 1 | 125
我现在有一个需要，给定客户 ID 列表，以便能够获取该客户 ID 列表的组 ID。组ID可能不是唯一的，相同的组ID将包含相同的客户ID列表，但该组可能存在于多个app_id中。
我认为这
SELECT APP_ID, GROUP_ID, COUNT(CUSTOMER_ID) AS COUNT
FROM GROUP_CUST_REL
WHERE CUSTOMER_ID IN ( <list of ids> )
GROUP BY APP_ID, GROUP_ID
HAVING COUNT(CUSTOMER_ID) = <number of ids in list>
将返回包含给定列表中所有客户 ID 的所有组 ID，并且仅返回那些组 ID。因此，对于 (123,125) 的列表，仅从上面的示例返回组 id 2，
然后我必须链接到应用程序表，以使用其创建的时间戳来识别该组存在的最新应用程序，以便我可以从组表中提取正确/最新的信息。
有谁知道这是否是最有效的方法？如果有另一种更快/更干净的方法，我会很感激你的想法。
I have a table for the link/relationship between two other tables, a table of customers and a table of groups. a group is made up of one or more customers. The link table is like
APP_ID | GROUP_ID | CUSTOMER_ID
1 | 1 | 123
1 | 1 | 124
1 | 1 | 125
1 | 2 | 123
1 | 2 | 125
2 | 3 | 123
3 | 1 | 123
3 | 1 | 124
3 | 1 | 125
I now have a need, given a list of customer IDs to be able to get the group ID for that list of customer IDs. Group ID may not be unique, the same group ID will contain the same list of customer IDs but this group may exist in more than one app_id.
I'm thinking that
SELECT APP_ID, GROUP_ID, COUNT(CUSTOMER_ID) AS COUNT
FROM GROUP_CUST_REL
WHERE CUSTOMER_ID IN ( <list of ids> )
GROUP BY APP_ID, GROUP_ID
HAVING COUNT(CUSTOMER_ID) = <number of ids in list>
will return me all of the group IDs that contain all of the customer ids in the given list and only those group ids. So for a list of (123,125) only group id 2 would be returned from the above example
I will then have to link with the app table to use its created timestamp to identify the most recent application that the group existed in so that I can then pull the correct/most up to date info from the group table.
Does anyone have any thoughts on whether this is the most efficient way to do this? If there is another quicker/cleaner way I'd appreciate your thoughts.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这闻起来像一个部门：
部门示例
其他相关的堆栈溢出问题
查看提供的链接，您将看到解决方案相似的从关系代数的角度来看，问题似乎并没有更快，也可以说更干净。
我一开始没有看你的解决方案，当我解决这个问题时，我发现我已经按照你的方式解决了这个问题。
实际上，我认为：
可以变成这样的东西（这样你就不需要额外的参数）：
但显然，我错了。如果我是你，我会保留你当前的解决方案。
This smells like a division:
Division sample
Other related stack overflow question
Taking a look at the provided links you'll see the solution to similar issues from relational alegebra's point of view, doesn't seem to be quicker and arguably cleaner.
I didn't look at your solution at first, and when I solved this I turned out to have solved this the same way you did.
Actually, I thought this:
Could be turned into something like this (so that you don't need the extra parameter):
But clearly, I was wrong. I'd stay with your current solution if I were you.