MySQL关系部门 [英] MySQL Relational Division

问题描述

我很难解决一种运动:

为哪个人提供餐厅，为他们提供所有他们最喜欢的啤酒.

For which People there is a Restaurant, that serves ALL their favorite beers.

(是的，我们实际上在学校里有这个:D)

(Yes, we actually have this in school :D)

我有2个表可以使用:

• 表1:最喜欢的啤酒(名称，姓氏，啤酒名)
• 表2:OnStock(啤酒名称，餐厅，数量)

我的解决方案是:OnStock％喜欢的啤酒

My solution would be: OnStock % Favoritebeer

MySQL中没有像DIVISION这样的东西.有什么想法可以解决这个问题吗?我在Wikipedia上找到了以下内容: http://en.wikipedia. org/wiki/Relational_algebra#Division_.28.C3.B7.29 正是我所需要的，但是我很难将其转换为SQL.

There is no such thing like DIVISION in MySQL. Any ideas how I could solve that? I found the following on Wikipedia: http://en.wikipedia.org/wiki/Relational_algebra#Division_.28.C3.B7.29 which is exactly what I need but I am having difficulties to translate it in SQL.

此处示例数据: http://www.sqlfiddle.com/#!2/34e00

结果应为:

Bucher Rolf
Mastroyanni Pepe
Meier Hans
Meier Hanspeter
Meier Hansruedi
Müller Heinrich
Peters Peter
Zarro Darween

推荐答案

尝试一下:

SELECT DISTINCT fb1.name, fb1.surname FROM favoriteBeer fb1
JOIN stock s ON fb1.beerName = s.beerName
GROUP BY fb1.name, fb1.surname, s.restaurant
HAVING COUNT(*) = (
  SELECT COUNT(*) FROM favoriteBeer fb2
  WHERE fb1.name = fb2.name AND fb1.surname = fb2.surname
)

输出:

|        NAME |   SURNAME |
|-------------|-----------|
|      Bucher |      Rolf |
| Mastroyanni |      Pepe |
|       Meier |      Hans |
|       Meier | Hanspeter |
|       Meier | Hansruedi |
|      Müller |  Heinrich |
|      Peters |     Peter |
|       Zarro |   Darween |

提琴此处.

这篇关于MySQL关系部门的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！