将列表与其他多个列表进行比较(Compare list to multiple other lists)

我没有运气寻找解决方案，部分原因是因为我不确定如何最好地解释这个问题！希望我在这里很清楚，但如果没有，请随时询问更多信息。

在顶层：在我们的数据库中，每个客户都可以拥有多项服务。服务单独分配给客户。但是，出于报告目的，我们需要将“群组”服务转换为常见服务组合的“包”。

我有一个包含包定义的数据库 - 一个名为packageServices的表，只是列出每个PackageID以及一个关联的ServiceID。因此，我可以检索每个包的服务列表。

在同一个数据库中，我可以检索特定客户的ServiceID列表。

我想要完成的是比较为该客户检索的ServiceID列表，并查看该组合是否与任何已定义的pacakges（即ServiceID的组合）匹配，如果是，则报告该客户具有哪些包。

到目前为止，我正在努力知道从哪里开始比较！我想我需要首先为每个包创建一些serviceIDs列表（而不是我当前的KeyValuePairs（在字典中），每个包有多行？）然后遍历包并将这些服务列表与列表进行比较客户有哪些服务？

这里使用的数据类型可能不是最合适的 - 在很多情况下我指的是'逻辑'列表而不是C＃List对象 - 我可以使用适当的数据类型:)

任何帮助非常感谢！

编辑 - 有人建议我在SQL中而不是在我的C＃应用程序中整理这些信息。这似乎是一个好主意，下面的答案之一让我感动。但是，有一些“业务规则”可以确定软件包是否是较低软件包或软件包本身的“升级”，这使事情变得非常复杂。

我越努力使这项工作越多，我认为我咬的越多，我就会咀嚼！我正在尝试正确地设计它，以便将来对服务或包的任何更改都将很容易处理 - 这些更改不太可能，但可能。但是，从我到目前为止看到的选项硬编码会更容易！

编辑2 - 我一直在努力解决这个问题，并建议我为每个包分配一个'权重'和'组'。这些组用于确保客户只能从每个组中获得一个包，并且权重用于确保仅返回“最高”级包。将此与@MarceloCantos的查询结合使用意味着我应该能够从每个组中返回“最高”权重包 - 这看起来可能符合应用程序的要求！

感谢所有迄今为止帮助过的人 - 我很惊讶响应的开始时间和响应的质量。我会试一试，看看我是怎么过的。

I've not had any luck searching for a solution to this, partly because I'm not exactly sure how best to explain the problem! Hopefully I'm clear here but if not please feel free to ask for more info.

At the top level: In our database, each customer can have multiple services. Services are allocated to a customer individually. However, we need 'group' services into 'packages' of common service combinations for reporting purposes.

I have a database which contains package definitions - a table called packageServices, simply listing each PackageID with an associated ServiceID. Therefore I can retrieve a list of services for each package.

From the same database I can retrieve a list of ServiceIDs for a particular customer.

What I am trying to accomplish is to compare the list of ServiceIDs retrieved for that customer, and see if that combination matches any of the defined pacakges (i.e. combinations of ServiceIDs) and if so report which package(s) that customer has.

I'm struggling to know where to begin so far as the comparison goes! I guess I need to start by creating some sort of list of serviceIDs for each package (rather than my current KeyValuePairs (in the dictionary) with multiple lines for each package?) then iterate through the packages and compare those lists of services with the list of services that customer has?

The data types used here may not be most appropriate - in many cases I'm referring to a 'logical' list rather than a C# List object - I can use whichever datatype is appropriate :)

Any help much appreciated!

EDIT - It's been suggested that I collate this information in SQL instead of in my C# application. This seems like a good idea and one of the answers below has brought me close. However, there are 'business rules' in place which determine whether a package is an 'upgrade' of a lower package or a package in itself and this is seriously complicating things.

The more I try to make this work the more I think that I've bitten off more than I can chew! I am trying to design this properly, so that any changes to services or packages in the future will be handled easily - these changes are unlikely but possible. However, it would be much easier to just to hardcode the options from what I've seen so far!

EDIT 2 - I've been working through this and it has been suggested that I assign a 'weight' and a 'group' to each package. The groups are there to ensure that a customer can only have one package from each group, and the weights are there to ensure that only the 'highest' level package is returned. Using this in conjunction with @MarceloCantos 's query will mean that I should be able to return just the 'highest' weight package from each group - this looks like it might fit the requirements of the application!

Thanks to all who have helped so far - I'm amazed how quickly the responses started coming in and at the quality of the responses given. I'll give this a go and see how I get on.

最满意答案

您可以为客户计算服务，然后，对于具有相同服务数量的每个包，计算与客户服务匹配的服务。只有完全匹配才会产生相同的计数。

您可以在一个查询中执行此操作，尽管这是一个相当棘手的问题：

SELECT CustomerID, PackageID FROM (SELECT CustomerID, COUNT(*) AS qty FROM customerServices GROUP BY CustomerID ) cs JOIN (SELECT PackageID, COUNT(*) AS qty FROM packageServices GROUP BY PackageID ) ps ON cs.qty = ps.qty WHERE (SELECT COUNT(*) FROM customerServices cs2 JOIN packageServices ps2 ON cs2.ServiceID = ps2.ServiceID WHERE cs2.CustomerID = cs.CustomerID AND ps2.PackageID = ps.PackageID ) = ps.qty

编辑：也许我误解了这个问题。如果要查找包含客户服务子集的软件包，可以执行以下操作：

... ) ps ON cs.qty >= ps.qty -- Change the test WHERE (SELECT COUNT(*) FROM customerServices cs2 JOIN packageServices ps2 ON cs2.ServiceID = ps2.ServiceID WHERE cs2.CustomerID = cs.CustomerID AND ps2.PackageID = ps.PackageID ) = ps.qty

编辑2：我改变了第二种情况的逻辑，以利用如下事实：如果两个集合的交集与较小集合的大小相同，那么较小的集合是较大集合的子集。

You can count the services for the customer and then, for each package that has the same number of services, count the services that match the customer's services. Only an exact match will yield the same count.

You can do this in a single query, albeit a rather tricksy one:

EDIT: Perhaps I misunderstood the question. If you want to find packages that contain a subset of the customer's services, you can do this instead:

EDIT 2: I changed the logic of the second case to exploit the fact that if the intersection of two sets is the same size as the smaller set, then the smaller set is a subset of the larger.

更多推荐