如何在SQL中创建视图？

发布于 2025-01-28 19:15:42 字数 874 浏览 3 评论 0原文

我是数据库和SQL的新手，我遇到了这个问题。任何帮助都会被杏液。先感谢您。

我有以下架构：

问题是创建一个带有战斗名称的视图，其中至少3次枪支少于9支枪参加了>这3艘枪支中至少有少于9艘船的结果“沉没”。我尝试解决这个问题，这是我的解决方案：

create view v_battles
as
    select distinct battle 
    from outcomes o1
    where battle in (select battle 
                     from outcomes 
                     where ship in (select name 
                                    from ships 
                                    join classes on classes.class = ships.class
                                    where numguns < 9)
                     group by battle 
                     having count(ship) >= 3)

问题是我的解决方案尚未完成，我不确定如何检查这些船是否有“沉没”。

原文

I am new to databases and SQL and I am stuck with this problem.
Any help would be apriciated. Thank you in advance.

I have the following schema:

The problem is to create a view with the names of the battles, where at least 3 ships with less than 9 guns took part and at least one of these 3 ships with guns less than 9, has a result 'sunk'.
I tried solving this problem, here is my solution:

create view v_battles
as
    select distinct battle 
    from outcomes o1
    where battle in (select battle 
                     from outcomes 
                     where ship in (select name 
                                    from ships 
                                    join classes on classes.class = ships.class
                                    where numguns < 9)
                     group by battle 
                     having count(ship) >= 3)

The problem is that my solution is not finished and I am not sure how to check if any of these ships has result 'sunk'.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

逐鹿 2025-02-04 19:15:42

我认为您只是错过了检查结果“沉没”的条款，所以我只是遵循您一直在做的事情，并在其中添加了另一个条款，该条款检查了战斗是否具有“沉没”的结果。因此，如果战斗都在两个条款中，则将在您的视野中显示。希望它有帮助！


create view v_battles
as
    select distinct battle 
    from outcomes o1
    where battle in (select battle 
                     from outcomes 
                     where ship in (select name 
                                    from ships 
                                    join classes on classes.class = ships.class
                                    where numguns < 9)
                     group by battle 
                     having count(*) >= 3)
   and battle in (select battle 
                   from outcomes
                   where result = 'sunk')

I think you were just missing the clause that checks the outcome 'sunk', so I just followed what you had been doing and add another where clause that checks that the battle has the 'sunk' outcome. So if the battle is in both where clauses it will be displayed in your view. Hope it helps!


create view v_battles
as
    select distinct battle 
    from outcomes o1
    where battle in (select battle 
                     from outcomes 
                     where ship in (select name 
                                    from ships 
                                    join classes on classes.class = ships.class
                                    where numguns < 9)
                     group by battle 
                     having count(*) >= 3)
   and battle in (select battle 
                   from outcomes
                   where result = 'sunk')

回复收藏 0 原文

~没有更多了~