我不能在它上声明表时创建视图

Question

这段代码在我的软件中显示了提醒工具，但是在我的程序中我使用了EF，现在我想在C#中创建视图并使用它。

CREATE VIEW s AS
(
DECLARE @ta AS TABLE(NAME NVARCHAR(30),qty INT,so INT,tar INT) 
INSERT INTO @ta 
SELECT p.Name,t.Qty,t.Source,t.target FROM dbo.Products p
JOIN dbo.TradeOff t ON t.ProductId = p.ProductID 
DECLARE @source AS TABLE(NAME NVARCHAR(30),qty INT,so INT) 
DECLARE @tar AS TABLE(NAME NVARCHAR(30),qty INT,tar INT) 

INSERT INTO @source 
SELECT t1.NAME,SUM(t1.qty),t1.so  FROM @ta t1
GROUP BY t1.so,t1.NAME

INSERT INTO @tar 
SELECT t1.NAME,SUM(t1.qty),t1.tar  FROM @ta t1

GROUP BY t1.tar,t1.NAME

SELECT s.NAME,ISNULL(t.Qty,0 )-ISNULL(s.qty,0),ISNULL(t.tar,s.so) FROM @tar t 
RIGHT JOIN @source s ON s.so=t.tar AND s.NAME=t.NAME 
)

Answer 1

不能在table variable中使用view。试一试这个

CREATE VIEW s
AS
  SELECT s.NAME,
         Isnull(t.Qty, 0) - Isnull(s.qty, 0),
         Isnull(t.tar, s.so)
  FROM   (SELECT t1.NAME,
                 Sum(t1.qty),
                 t1.tar
          FROM   (SELECT p.NAME,
                         t.Qty,
                         t.Source,
                         t.target
                  FROM   dbo.Products p
                         JOIN dbo.TradeOff t
                           ON t.ProductId = p.ProductID) t1
          GROUP  BY t1.tar,
                    t1.NAME) t
         RIGHT JOIN (SELECT t1.NAME,
                            Sum(t1.qty),
                            t1.so
                     FROM   (SELECT p.NAME,
                                    t.Qty,
                                    t.Source,
                                    t.target
                             FROM   dbo.Products p
                                    JOIN dbo.TradeOff t
                                      ON t.ProductId = p.ProductID) t1
                     GROUP  BY t1.so,
                               t1.NAME) s
                 ON s.so = t.tar
                    AND s.NAME = t.NAME 

Answer 2

如果您需要使用表变量，您可以使用一个存储过程来实现表值函数，但是这些功能要慢一些，然后是视图，并且取决于您想要在何处使用数据，可以确定哪种解决方案最适合您的需求。