如何对视图中的列进行求和/分组/排序

Question

我有两个表，Author_Dim和Author_Fact如下:

CREATE TABLE Author_Dim
(
TitleAuthor_ID INT IDENTITY(1,1),
Title_ID CHAR(20),
Title VARCHAR(80),
Type_Title CHAR(12),
Author_ID CHAR(20),
Last_Name   VARCHAR(40),
First_Name VARCHAR(20),
Contract_Author BIT,
Author_Order INT,
PRIMARY KEY (TitleAuthor_ID),
);
GO
CREATE TABLE Author_Fact
(
Fact_ID INT IDENTITY(1,1),
TitleAuthor_ID INT,
Author_ID CHAR (20),
Price DEC(10,2),
YTD_Sales INT,
Advance DEC(10,2),
Royalty INT,
Royalty_Perc INT,
Total_Sales DEC(10,2),
Total_Advance DEC(10,2),
Total_Royalty DEC(10,2)
PRIMARY KEY(Fact_ID),
FOREIGN KEY (TitleAuthor_ID) REFERENCES Author_Dim(TitleAuthor_ID),
);
go

我希望创建一个视图，该视图给出每个作者支付的版税总额，然后按照首先显示的收入最高的作者对其进行排序，即对Total_Royalty列求和，按Author_ID对其分组，然后按降序对Total_Royalty进行排序。

我有以下，但我不确定如何将sum/group/sort函数添加到视图:

Create view [Total_Royalty_View] As (
Select Author_Dim.Author_ID, Author_Dim.Last_Name, Author_Dim.First_Name, Author_Fact.Total_Royalty
From Author_Dim
Join Author_Fact
On Author_Fact.TitleAuthor_ID = Author_Dim.TitleAuthor_ID
);
Go

Answer 1

在SQL中，它是所有表。从表中进行选择，结果还是一个表(由列和行组成)。您可以存储一个选择语句以供重用，这被称为视图。您也可以编写一个特别视图(from子句中的子查询)。他们的结果还是表格。

和表被认为是无序的数据集。

因此，您不能编写生成有序行集的视图。

这是视图(无序):

create view total_royalty_view as 
select
a.author_id,
a.last_name,
a.first_name,
coalesce(r.sum_total_royalty, 0) as total_royalty
from author_dim a
left join
(
select titleauthor_id, sum(total_royalty) as sum_total_royalty
from author_fact
group by titleauthor_id
) r on r.titleauthor_id = a.titleauthor_id;

下面是如何从中选择:

select *
from total_royalty_view
order by total_royalty desc;