“CREATE VIEW” 必须是查询批处理中的第一条语句。

Question

基本上就是标题所说的。这是我的代码。

USE Assignment2;
GO

/* Player View (2 marks)
    Create a view which shows the following details of all players:
        • The ID number of the player
        • The first name and surname of the player concatenated and given an alias of “full_name”
        • The team ID number of the player (if applicable)
        • The team name of the player (if applicable)
        • The coach ID number of the player (if applicable)
        • The name of the player’s coach (if applicable)

   Creating this view requires a select statement using multiple joins and concatenation of names.  
   Make sure that you use the appropriate type of join to ensure that players without teams or coaches are still included in the results.

*/


-- Write your Player View here
PRINT 'Creating Player View'

CREATE VIEW playerView AS 
SELECT player.id, player.firstName + ' ' + player.surname AS 'Full name', player.team, team.name, player.coach, coach.firstName, coach.surname 
FROM player
LEFT OUTER JOIN team
    ON player.team = team.id
    LEFT OUTER JOIN player as coach
        ON player.coach = coach.id;



GO
/* Race View (3 marks)
   Create a view which shows the following details of all races:
        • All of the columns in the race table
        • The name of the race type, course and team involved in the race
        • The full name of the player observing the race and the full name of the MVP (if applicable)
        • A calculated column with an alias of “unpenalised_score”, which adds the points penalised to the final score

   Creating this view requires a select statement using multiple joins and concatenation of names.  
   Make sure that you use the appropriate type of join to ensure that races without MVPs are still included in the results.
*/

-- Write your Race View here
PRINT 'Creating Race View'

CREATE VIEW raceView AS 
SELECT race.id, race.dateOfRace, race.raceType, raceType.name AS raceTypeName, race.course, course.name AS courseName, race.team, team.name AS teamName, race.observer, obs.firstName + ' ' + obs.surname AS observer_name, race.mvp, mvp.firstName + ' ' + mvp.surname AS mvp_name, race.pointsPenalised, race.finalScore + race.pointsPenalised AS unpenalised_score, race.finalScore
FROM race
INNER JOIN raceType
    ON race.raceType = raceType.id
    INNER JOIN course
        ON race.course = course.id
        INNER JOIN team
            ON race.team = team.id
            LEFT OUTER JOIN player AS mvp
                ON race.mvp = mvp.id
                LEFT OUTER JOIN player AS obs
                    ON race.observer = obs.id;
GO 

SELECT * 
FROM playerView

SELECT *
FROM raceView


/* Additional Information:
   The views are very convenient replacements for the tables they represent, as they include the names and calculated values that you will often need in queries.
   You are very much encouraged to use the views to simplify the queries that follow.  You can use a view in a SELECT statement in exactly the same way as you can use a table.

   If you wish to create additional views to simplify the queries which follow, include them in this file.
*/

当我单独运行每个CREATE VIEW时，似乎没有错误地运行。但是当我尝试运行整个脚本时，它会给我这个错误：
Msg 111, Level 15, State 1, Line 20
'CREATE VIEW' 必须是查询批处理中的第一个语句。
Msg 111, Level 15, State 1, Line 15
'CREATE VIEW' 必须是查询批处理中的第一个语句。
Msg 208, Level 16, State 1, Line 2
无效的对象名称 'playerView'。
在尝试运行此脚本之前，我首先删除数据库，重新创建表，填充它们，然后运行此脚本。 您有任何想法我做错了什么吗？

- Shivarn

---

在语句“create view…”之前加上“go”。 - sventevit

@Shivarn，您从未选择答案。 - DiverseAndRemote.com

@Omar Jackman，你的答案和Damien_The_Unbeliever的理论都是正确的。虽然你的答案让我更清楚地理解了它，并帮助我修复了我的代码。非常感谢！ - Shivarn

Answer 1

在PRINT 'Creating Player View'之后加入GO即可使其正常工作：

PRINT 'Creating Player View'
GO

CREATE VIEW playerView AS

- DiverseAndRemote.com

---

非常简单，哈哈。抱歉给你带来不便。感谢你的帮助。现在它可以运行了。 - Shivarn

这还不够。你需要设置 SET ANSI_NULLS ON 和 SET QUOTED_IDENTIFIER ON，最好将它们都放在一个 TRANSACTION 中。 - Fandango68

为什么可以CREATE TABLE，但不允许CREATE VIEW呢？ - IngoB

Answer 2

批处理是由单词 GO 分隔的 - 这是针对客户端工具的指令，而不是针对SQL Server的指令，特别是告诉这些工具如何将您的查询分成批处理。

错误提示您 CREATE VIEW 必须是批处理中的第一条语句：

USE Assignment2;
GO

/* Player View (2 marks)
    Create a view which shows the following details of all players:
        • The ID number of the player
        • The first name and surname of the player concatenated and given an alias of “full_name”
        • The team ID number of the player (if applicable)
        • The team name of the player (if applicable)
        • The coach ID number of the player (if applicable)
        • The name of the player’s coach (if applicable)

   Creating this view requires a select statement using multiple joins and concatenation of names.  
   Make sure that you use the appropriate type of join to ensure that players without teams or coaches are still included in the results.

*/


-- Write your Player View here
PRINT 'Creating Player View'

GO -->-- New GO here

CREATE VIEW playerView AS 

所以我在CREATE VIEW前面添加了一个GO

- Damien_The_Unbeliever

Answer 3

如果尝试在 Entity Framework 迁移中执行脚本，您可能会遇到此问题。我认为这是因为 EF 在事务中运行这些脚本（如在此问题的另一个答案中提到的）。您可以使用以下语法解决此问题：

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_MovieActors]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[V_MovieActors]
AS
SELECT       NEWID() AS Id, dbo.Movie.Title, dbo.Movie.ReleaseDate, dbo.Actor.FirstName + '' '' + dbo.Actor.LastName AS Actor, dbo.Actor.DateOfBirth
FROM            dbo.Actor INNER JOIN
                         dbo.Movie ON dbo.Actor.Id = dbo.Movie.Actor_Id
'

将所有内容转换成单个SQL命令以供执行。这种方法来自于这篇非常有用的文章《使用SQL视图与Entity Framework Code First》Morgan Kamoga。

- tomRedox

---

当您想在多个块中使用变量时，包括CREATE TABLE语句时，这也非常有用，因为变量是局部于声明它们的批处理的。 - Clon

感谢tomRedox的建议！我通过添加EXEC dbo.sp_executesql @statement = N'视图的创建代码'成功修复了EFCore 6导出的.sql脚本的问题。这种方法的问题在于需要手动刮取和修改脚本，因为MS生成的方式适用于所有情况，但对于视图则失败了。我看到另一种基于迁移包的方法也可以解决问题，但问题是你最终会得到一个.exe文件，无法预览将要执行的内容。 https://youtu.be/mBxSONeKbPk - Gerardo Verrone

我可能会更改视图的使用，转而使用存储过程，因为它们可以像视图一样工作，并且可以正确导出。PS：不幸的是，您提供的链接现在已经失效了。 - Gerardo Verrone

Answer 4

将CREATE VIEW代码放在EXECUTE内部。

SOME CONDITION..

EXECUTE('CREATE  VIEW vwName...')

- kirk

Answer 5

这通常发生是因为要能够创建视图或任何DBO，需要将整个脚本放在事务内部，或者需要打开SET QUOTED_IDENTIFIER。

即

USE [yourdatabase]
GO

SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
SET ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRANSACTION
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

...
...

-- Write your Race View here
PRINT 'Creating Race View'
GO

CREATE VIEW raceView AS 
SELECT race.id, race.dateOfRace, race.raceType, raceType.name AS raceTypeName, race.course, course.name AS courseName, race.team, team.name AS teamName, race.observer, obs.firstName + ' ' + obs.surname AS observer_name, race.mvp, mvp.firstName + ' ' + mvp.surname AS mvp_name, race.pointsPenalised, race.finalScore + race.pointsPenalised AS unpenalised_score, race.finalScore
FROM race
INNER JOIN raceType
    ON race.raceType = raceType.id
    INNER JOIN course
        ON race.course = course.id
        INNER JOIN team
            ON race.team = team.id
            LEFT OUTER JOIN player AS mvp
                ON race.mvp = mvp.id
                LEFT OUTER JOIN player AS obs
                    ON race.observer = obs.id;
GO 

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END
GO

IF @@TRANCOUNT>0 COMMIT TRANSACTION
GO

SET NOEXEC OFF

- Fandango68