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

Question

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

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;

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

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

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

Answer 1

就像错误所言，该CREATE VIEW语句必须是查询批处理中的唯一语句。

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

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;