SQL CREATE VIEW 必须是批处理中的唯一语句

CREATE VIEW must be the only statement in the batch

提问by Kadaj13

I'm trying to make a view. So far, I have written this:

我正在尝试发表看法。到目前为止，我已经写了这个：

with ExpAndCheapMedicine(MostMoney, MinMoney) as
(
    select max(unitprice), min(unitprice)
    from Medicine
)
,
findmostexpensive(nameOfExpensive) as
(
    select tradename
    from Medicine, ExpAndCheapMedicine
    where UnitPrice = MostMoney
)
,
findCheapest(nameOfCheapest) as
(
    select tradename
    from Medicine, ExpAndCheapMedicine
    where UnitPrice = MinMoney
)

CREATE VIEW showing
as
select tradename, unitprice, GenericFlag
from Medicine;

Unfortunately, I get an error on the line containing CREATE VIEW showing

不幸的是，我在包含 CREATE VIEW showing

"CREATE VIEW must be the only statement in the batch"

“CREATE VIEW 必须是批处理中的唯一语句”

How can I fix this?!

我怎样才能解决这个问题？！

回答by Radu Gheorghiu

Just as the error says, the CREATE VIEWstatement needs to be the only statement in the query batch.

正如错误所说，该CREATE VIEW语句必须是查询批处理中的唯一语句。

You have two option in this scenario, depending on the functionality you want to achieve:

在这种情况下，您有两种选择，具体取决于您要实现的功能：

1. Place the CREATE VIEWquery at the beginning

   CREATE VIEW showing
   as
   select tradename, unitprice, GenericFlag
   from Medicine;
   
   with ExpAndCheapMedicine(MostMoney, MinMoney) as
   (
       select max(unitprice), min(unitprice)
       from Medicine
   )
   ,
   findmostexpensive(nameOfExpensive) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MostMoney
   )
   ,
   findCheapest(nameOfCheapest) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
           where UnitPrice = MinMoney
       )
   

2. Use GOafter the CTE and before the CREATE VIEWquery

   -- Option #2

   with ExpAndCheapMedicine(MostMoney, MinMoney) as
   (
       select max(unitprice), min(unitprice)
       from Medicine
   )
   ,
   findmostexpensive(nameOfExpensive) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MostMoney
   )
   ,
   findCheapest(nameOfCheapest) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MinMoney
   )
   
   GO    
   
   CREATE VIEW showing
   as
   select tradename, unitprice, GenericFlag
   from Medicine;
   

1. 将CREATE VIEW查询放在开头

   CREATE VIEW showing
   as
   select tradename, unitprice, GenericFlag
   from Medicine;
   
   with ExpAndCheapMedicine(MostMoney, MinMoney) as
   (
       select max(unitprice), min(unitprice)
       from Medicine
   )
   ,
   findmostexpensive(nameOfExpensive) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MostMoney
   )
   ,
   findCheapest(nameOfCheapest) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
           where UnitPrice = MinMoney
       )
   

2. 使用GO热膨胀系数之后和之前CREATE VIEW查询

   -- 选项#2

   with ExpAndCheapMedicine(MostMoney, MinMoney) as
   (
       select max(unitprice), min(unitprice)
       from Medicine
   )
   ,
   findmostexpensive(nameOfExpensive) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MostMoney
   )
   ,
   findCheapest(nameOfCheapest) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MinMoney
   )
   
   GO    
   
   CREATE VIEW showing
   as
   select tradename, unitprice, GenericFlag
   from Medicine;
   

回答by Mozart Al Khateeb

I came across this question when I was trying to create a couple of views within the same statement, what worked well for me is using dynamic SQL.

当我试图在同一个语句中创建几个视图时，我遇到了这个问题，对我来说效果很好的是使用动态 SQL。

    EXEC('CREATE VIEW V1 as SELECT * FROM [T1];');
    EXEC('CREATE VIEW V2 as SELECT * FROM [T2];');