3张表的SQL初学者查询逻辑 [英] SQL Beginner Query Logic With 3 Tables

问题描述

sql 表如下:likes 用drinker 和beer 列，sells 用bar 和beer 列，fququences 用drinker 和bar 列.

The sql tables are as follows: likes with columns drinker and beer, sells with columns bar and beer and frequents with columns drinker and bar.

我之前已经回答了以下声明:

I have previously answered the following statement:

"Drinkers who frequent bars which serve some beer they like"

使用 SQL 查询:

SELECT DISTINCT y.drinker
FROM likes a, sells, frequents y
WHERE a.beer = sells.beer
AND sells.bar = y.bar
AND y.drinker = a.drinker;

现在，我正在尝试修改上面列出的查询以回答类似但不同的陈述:

Now, I am trying to modify the query listed above to answer a similar, yet different statement:

"Drinkers who only frequent bars which serve beers they like"

从逻辑上讲，修改是只包括那些经常光顾供应他们喜欢的啤酒的酒吧的人，并排除那些光顾不供应他们喜欢的啤酒的酒吧的人.

The modification, logically, is to include only those who frequent bars that serves beers they like and to eliminate those that visit any bar that doesn't serve a beer they like.

我最难的是修改上述查询以满足第二个语句.

I am having the hardest time modifying the above query to satisfy the second statement.

我的思考过程可能是进行双重否定，例如: 获取以下组中不存在的饮酒者名单:不常去不出售他们喜欢的啤酒的酒吧的饮酒者.但正确的实现却让我望而却步.

My thought process was to perhaps do a double negation, such as: Get the list of drinkers who do not exist in the following group: drinkers who do not frequent bars that don't sell beers they like. But the correct implementation is escaping me.

这是我解决此查询的最佳尝试，但我很清楚这是不正确的，因为此查询仍然返回经常光顾一些酒吧(不仅)供应他们喜欢的啤酒的饮酒者:

Here's is my best attempt at solving this query, but I am well aware it is not correct, for this query still returns drinkers who frequent some bars (not only) that serves beer they like:

SELECT distinct x.drinker
FROM frequents x
WHEREexists (SELECT* 
             FROM frequents y, sells z, likes u
             WHERE x.drinker=y.drinker
             AND y.bar = z.bar and z.beer = u.beer
             AND y.drinker = u.drinker);

任何帮助都会很棒，感谢您提供的任何见解.

Any help would be great, thank you for any insight you can provide.

推荐答案

经常喝酒的人的酒吧数量与他们经常光顾的酒吧数量相同:

Drinkers who frequently exactly the number of bars as those they frequent which sell their favorites:

select drinker
from frequents
group by drinker
having count(bar) = (
    select count(distinct f.bar)
    from
        sells s
        inner join likes l on l.beer = s.beer
        inner join frequents f on f.bar = s.bar and f.drinker = l.drinker
    where f.drinker = frequents.drinker
)

当您无法匹配所有三个关系三角形时，内部计数会更低.(注意啤酒、酒吧和饮料的每个测试如何在内部查询中出现一次.)如果您想查找相反的一组人，您只需将等式测试更改为大于.

The inner count will be lower when you can't match up all three of the relationship triangle. (Notice how each of the tests for beer, bar, and drink each appear once in the inner query.) If you wanted to look for the opposite set of people you'd just change the equality test to greater than.

我正在考虑解决此问题的其他方法.有趣的是，在这种情况下，您可能会发现正确的外连接很有用(而不是连接表周围的括号.)

I was thinking about alternate ways of approaching this. Interestingly this is one where you might find a right outer join to be useful (rather than the parentheses around the joined tables.)

frequents 表仍然是下面查询的焦点，逻辑是由找出哪些经常光顾的酒吧也是可以出售喜欢/最喜欢的酒吧的想法驱动的.由于外部连接，频繁"方永远不会为空，但喜欢-销售"方只有在一对不能与该频繁匹配时才会为空.have 子句中的最后一个测试是多余的，但我想展示能够匹配 `frequents' 两列上的计数的对称性，即使只有一个是绝对必要的.

The frequents table is still the focus of the query below and the logic is driven by the idea of finding out which frequented bars are also bars where a like/favorite is available for sale. Because of the outer join the "frequents" side will never be null but the "likes-sells" side will have nulls only when a pair can't be matched for that frequent. The final test in the having clause is redundant but I wanted to show the symmetry in being able to match the counts on both the columns of `frequents' even though only one is strictly necessary.

select f.drinker
from
    frequents f left outer join (
        likes l inner join sells s on s.beer = l.beer
    ) on f.drinker = l.drinker and f.bar = s.bar
group by f.drinker
having count(f.drinker) = count(l.drinker) and count(f.bar) = count(s.bar)

这篇关于3张表的SQL初学者查询逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！