mysql - SQL根据不同的表关系找到最经常出现的元素

我有一个关于如何进行SQL查询的问题。我写了一个样本数据库，我在这里使用，我试图保持简单的事情，为大家谁想帮助。

Officer              Permit               Vehicle              Dispatch

Oid | Dname | Rank   Oid | Type | Model   Vid | Type | Model   Did | Oid | Location
------------------   ------------------   ------------------   --------------
1   | John  |  Jr    1     D1     Ford    1     D1     Ford    1     1      Hill
2   | Jack  |  Sr    1     D2     Ford    2     D2     Ford    2     2      Beach
3   | Jay   |  Jr    2     D1     Ford    3     D3     Ford    3     3      Post
4   | Jim   |  Jr    3     D1     Ford    4     D4     Ford    4     1      Beach
5   | Jules |  Sr    5     D1     Ford    5     D5     Ford    5     2      Hill
                     1     D3     Ford                         6     4      Post
                     2     D2     Ford                         7     5      Hill
                     4     D1     Ford                         8     5      Beach
                     1     D5     Ford                         9     2      Post

表之间的关系是：

Officer - lists the officer by OID(officer ID)/Name/Rank where Sr is highest, Jr is lowest.
Permit - Officers are required to have a permit depending on the vehicle they will be using, Oid for Officer ID, Type for the vehicle and Model.
Vehicle - Vid for vehicle ID, Type and Model
Dispatch - Did for Dispatch ID, keeps track of which officer (Oid) was dispatched to which location (Location)

问：我需要从这里了解一些事情。
首先，我如何知道哪些警官可以驾驶所有车型？
第二个问题是，我如何知道哪个军官被派往所有被派往的地点？
编写这两个查询对我来说是一个噩梦，我试图加入不同的表，但仍然无法从这两个表中获取最经常出现的元素（我不知道怎么做！）任何帮助都将不胜感激！

最佳答案

第一个问题：

select Oid, count(*) type_count
from Permit
group by Oid
having type_count = (select count(distinct Type, Model) from Vehicle)

第二：

select Oid, count(*) location_count
from Dispatch
group by Oid
having location_count = (select count(distinct Location) from Dispatch)

看到模式了吗？

关于mysql - SQL根据不同的表关系找到最经常出现的元素，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12807072/