In my previous article, we looked at how to use the CREATE VIEW SQL statement to create views. In this one, we are moving on and focusing on how to modify views. We will continue using examples on a sample database and data created in the first write-up so in order to follow along, head over and read the Creating views in SQL Server part before starting with this one.
在上一篇文章中,我们研究了如何使用CREATE VIEW SQL语句创建视图。 在这一篇中,我们将继续关注于如何修改视图。 我们将继续在示例数据库中使用示例,并在第一次撰写中创建数据,因此,在继续学习之前,请继续阅读并在SQL Server中创建视图 。
介绍 (Introduction)
The primary goal will be to get familiar with the ALTER VIEW command used to modify views and change the output. A view is based on the result set from a query, and this command allows us to change the structure and definition of a query.
主要目标是熟悉用来修改视图和更改输出的ALTER VIEW命令。 视图基于查询的结果集,该命令允许我们更改查询的结构和定义。
Ironically, before modifying a view, we will create another view with a bit more complex T-SQL using aggregates in it rather than having a simple SELECT statement that is pulling everything from a table. We will not go over the syntax again since T-SQL is exactly the same as in CREATE VIEW SQL statement except the fact that instead of the CREATE reserved keyword ALTER is used.
具有讽刺意味的是,在修改视图之前,我们将使用其中的聚合来创建另一个视图,该视图使用更复杂的T-SQL,而不是使用简单的SELECT语句从表中提取所有内容。 由于T-SQL与CREATE VIEW SQL语句完全相同,因此我们不再赘述语法,只是使用了代替CREATE保留关键字ALTER的事实。
创建视图 (Creating view)
As I mentioned earlier, let’s use the code from below to create a bit more complex view:
正如我之前提到的,让我们使用下面的代码创建一个更复杂的视图:
CREATE VIEW vTop3SalesByQuantity
AS
SELECT TOP 3 --will only return first 3 records from query
Sales.ProductID,
Name AS ProductName,
SUM(Sales.Quantity) AS TotalQuantity
FROM Sales
JOIN Products ON Sales.ProductID = Products.ProductID
GROUP BY Sales.ProductID,
Name
ORDER BY SUM(Sales.Quantity) DESC;
But before we run the script, we can again just highlight the SELECT statement and see what it returns as shown below:
但是在运行脚本之前,我们可以再次突出显示SELECT语句并查看其返回结果,如下所示:
Basically, what we are doing here is for each product in the Product table, we are fetching all the quantities and add them together per product. As you can see, we have our Long-Sleeve Logo Jersey product in different sizes and sold quantities. We only have four products in our table, so that’s why we’re selecting only the top three records.
基本上,我们在这里所做的是针对“产品”表中的每个产品ÿ