SQL语句翻译
SQL语句翻译
提问于 2013-02-26 12:50:09
我对SQL很陌生，这是我第一次尝试将这种语句转换为SQL。
找那些经常喝啤酒的人约翰史密斯喜欢的酒吧
我有三张表格来回答这个问题：
FREQUENTS(DRINKER, BAR)
SERVES(BAR, BEER)
LIKES(DRINKER, BEER)这是我失败的方法：
SELECT DISTINCT F1.DRINKER
FROM FREQUENTS F1
WHERE F1.DRINKER<>'John Smith'
AND F1.DRINKER NOT IN(SELECT F2.DRINKER
FROM FREQUENTS F2
WHERE F2.BAR NOT IN (SELECT S.BAR
FROM SERVES S, LIKES L
WHERE L.DRINKER='John Smith'
AND S.BEER=L.BEER
AND L.DRINKER=F2.DRINKER))有人能帮我想办法解决吗？
回答 2
Stack Overflow用户
发布于 2013-02-26 12:56:31
尝试这个查询
SELECT DISTINCT F1.DRINKER
FROM FREQUENTS F1
WHERE F1.BAR IN (SELECT S.BAR FROM SERVES S, LIKES L
WHERE L.DRINKER='John Smith'
AND S.BEER=L.BEER)我已经删除了您创建的额外子查询。
Stack Overflow用户
发布于 2013-02-26 13:43:57
好的，我知道了。它不太优雅，如果数据在执行此代码期间被更新，那么这可能无法工作，但如下所示
DECLARE @bars TABLE (bar nvarchar(max))
DECLARE @drinkers TABLE (drinker nvarchar(max))
DECLARE @drinkerCounts TABLE (count int, drinker nvarchar(max))
DECLARE @barCount int
insert into @bars
select distinct bar from serves where beer in
(select beer from likes where drinker = 'John Smith')
select @barCount = COUNT(*) from @bars
insert into @drinkers
select drinker from frequents f
inner join @bars b on f.bar = b.bar
where drinker <> 'John Smith'
insert into @drinkerCounts
select count(drinker), drinker
from @drinkers
group by drinker
select * from @drinkerCounts dc
where dc.Count = @barCount页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15089566
复制相关文章
相似问题
腾讯云开发者
