选择“按单个列的多个值过滤内部联接查询时不同”？ [英] Select Distinct on Inner Join Query filtering by multiple values on a single column?

问题描述

所以，我不得不说，到目前为止，SQL是我作为开发人员最弱的一面。也许我想要实现的目标很简单。我有一个类似的东西（它不是真正的模型，但是为了使它易于理解而又不浪费太多时间来解释它，我想出一个例子来模仿我必须使用的表关系）。
一方面，我们将其称为用户。诸如主键 UserId， UserName之类的列。

下一步，另一个表为许可证。与用户1：N相关，每个许可证属于一个用户，一个用户可能有多个许可证。因此，我们将拥有一个PK IdLicense，一个FK IdUser，等等。

接下来，有一个表叫做 Equipments。这里我们定义了不同的设备。 IdEquipment，设备名称。

最后，一个表在许可证和设备之间建立了N：M关系。当用户定义许可证时，他们可以指定许可证允许他们操作的不同设备。因此，一个许可证可以涉及多个设备，一个设备可以涉及多个许可证。

真正的交易就在这里。我已下令实施某种搜索系统，使您可以根据他们有资格使用的设备来吸引用户。可以进行多项选择，以便搜索有资格使用设备的用户。 ， B和 C。例如，我们有2个用户，James和Dave。詹姆斯有2个许可证，戴夫有1个。詹姆斯有资格在第一个许可证上使用机器 A和 D，在第二个许可证上使用 B和 C。戴夫有资格在其唯一的许可证上使用 B和 C类型的设备。如果有人试图搜索能够处理设备 A， B和 C的用户，则只有James是返回记录。

到目前为止，我假设我必须要做

  SELECT DISTINCT IdUser 
 FROM Users 
 INNER JOIN Licenses b $ b ON Licenses.IdUser = Users.idUser 
内部联接LicensesEquipments 
 ON LicensesEquipments.IdLicense = Licenses.IdLicense 
内部联接Equipment 
 Equipment.IdEquipment = LicensesEquipments.IdEquipment 
 
 WHERE Equipment.IdEquipment = ?? 
  

我如何在 A， B和 C的所有3种不同id上对此进行过滤。？
显然，我不能使
WHERE Equipment.IdEquipment = 1和Equipment.IdEquipment = 2，因为一个像元不能等于2个不同的值。
我也不可以在
WHERE Equipment.IdEquipment = 1或Equipment.IdEquipment = 2上进行操作，因为使用OR意味着持有至少一种可能性的任何用户都将被视为有效结果，而我我正在寻找一个在这种情况下拥有所有3种不同设备的设备。

起初，我假设我必须使用别名使用表设备创建多个内部联接，进行次数与设备上的过滤器次数相同，并且使用不同的过滤器使用每个别名。但是后来我的代码主管来了，告诉我忘了它，因为他说添加更多的过滤器会增加成本（尽管他没有给我更好的选择...）

解决方案

  SELECT Users.idUser 
 FROM Users 
 INNER JOIN许可证ON Licenses.IdUser =用户。 idUser 
内部联接LicensesEquipments on LicensesEquipments.IdLicense = Licenses.IdLicense 
内部联接Equipment onId.Equipment = LicensesEquipments.IdEquipment 
 WHERE Equipment.IdEquipment IN（'A'，'B' C'）
 IdUser的组
拥有的数量（DISTINCT Equipment.IdEquipment）= 3<-必须是IN子句
 中的唯一项数＃ pre> 
 
 
如果您只需要用户ID，则可以删除 Users 和您查询中的许可证表：
  SELECT Licenses.IdUser 
 FROM许可证
 INNER JOIN LicensesEquipments on LicensesEquipments.IdLicense = Licenses.IdLicense 
 WHERE LicensesEquipments.IdEquipment in（'A'，'B'，'C'）
 GROUP BY Licenses.IdUser 
 HAVING COUNT（DISTINCT LicensesEquipments.IdEquipment）= 3 
  
此外，一些别名使阅读更容易：
  SELECT l.IdUser 
从许可证l 
内部联接LicensesEquipments le ON le.IdLicense = l.IdLicense 
 WHERE le.IdEquipment in（'A'，' B'，'C'）
由l.IdUser 
拥有的组（DISTINCT le.IdEquipment）= 3 
  
 
So, i have to say, SQL is by far my weakest side as a developer. Perhaps what i'm trying to accomplish is pretty easy. I have something like this (It's not the real model, but in order to make it simple to understand without wasting too much time explaining it, i've come up with an example that mimics exactly the table relations i have to use).
On one hand, a table, let's call it, "Users". Columns such as a primary key "UserId", "UserName", and so on.

Next, another Table, "Licenses". Relates 1:N with user, each license belongs to a single user, a user may have multiple licenses. So, we'd have a PK IdLicense, a FK IdUser, and so on.

Next, a table called "Equipments". here we have defined different equipments. IdEquipment, EquipmentName. Not much more to say about it.

Lastly, a table that creates a N:M relation between "Licenses" and "Equipments". when a user defines a license, they can specify the different equipments that licence allows them to manipulate. So, a license can relate to several equipments, and a piece of equipment can relate to multiple licenses.

The real deal comes here. I've been ordered to implement some kind of search system that allows you to get users based on the equipment they are qualified to handle.It's possible to make a multiple selection, so that you search for users that are qualified to handle equipment "A", "B" and "C". For example, we have 2 users, James and, Dave. James has 2 licenses, Dave has one. James is qualified to use machines "A" and "D" on his first license, "B" and "C" on the second one. Dave is qualififed to handle equipment type "B" and "C" on his only license. If someone tries to search for users being able to handle equipment "A","B" AND "C", only James would be the returning record.

So far i assumed i'd have to do something like 
SELECT DISTINCT IdUser
FROM  Users
INNER JOIN Licenses
   ON Licenses.IdUser = Users.idUser
INNER JOIN LicensesEquipments
   ON LicensesEquipments.IdLicense = Licenses.IdLicense
INNER JOIN Equipment
   ON Equipment.IdEquipment = LicensesEquipments.IdEquipment

WHERE Equipment.IdEquipment = ??
How can i filter this on the all 3 different idEquipments for "A" "B" and "C".?
Obviously, i can't make 
WHERE Equipment.IdEquipment = 1 and Equipment.IdEquipment = 2 since a cell can't be equal to 2 different values.
Neither i can do 
WHERE Equipment.IdEquipment = 1 or Equipment.IdEquipment = 2 since using an OR would mean that any user holding at least ONE of the possibilities would be considered a valid result, and i'm looking for one that has ALL 3 different equipments on this case.

At first I assumed I'd have to create multiple inner joins with the table equipment, using alias, doing it as many times as filters on equipment i may have, and using each alias with a different filter. But then my code supervisor came by and told me to forget about it, since he said that would render too costly as more and more filters where added (though he gave me no better alternative...)
解决方案
SELECT Users.idUser
FROM Users
INNER JOIN Licenses ON Licenses.IdUser = Users.idUser
INNER JOIN LicensesEquipments ON LicensesEquipments.IdLicense = Licenses.IdLicense
INNER JOIN Equipment ON Equipment.IdEquipment = LicensesEquipments.IdEquipment
WHERE Equipment.IdEquipment IN ('A', 'B', 'C')
GROUP BY IdUser
HAVING COUNT(DISTINCT Equipment.IdEquipment) = 3 <--must be # of unique items in IN clause
If you really just need the user ID, you can drop the Users and Licenses tables from your query:
SELECT Licenses.IdUser
FROM Licenses 
INNER JOIN LicensesEquipments ON LicensesEquipments.IdLicense = Licenses.IdLicense
WHERE LicensesEquipments.IdEquipment in ('A', 'B', 'C')
GROUP BY Licenses.IdUser
HAVING COUNT(DISTINCT LicensesEquipments.IdEquipment) = 3
Also, some aliases makes it easier to read:
SELECT l.IdUser
FROM Licenses l
INNER JOIN LicensesEquipments le ON le.IdLicense = l.IdLicense
WHERE le.IdEquipment in ('A', 'B', 'C')
GROUP BY l.IdUser
HAVING COUNT(DISTINCT le.IdEquipment) = 3


                        
这篇关于选择“按单个列的多个值过滤内部联接查询时不同”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！