SQL语句查询--Customer,Region,Sales;Order by and Top

/*
create table A_Customer(ID int,CName nvarchar(20),RegionID int)
create table A_Region(ID int,RName nvarchar(20))
create table A_Sales(ID int,CID int,SaleDate datetime,SaleValues money)
*/
--select * from A_Customer
/*
1 张三 100
2 李四 200
3 王五 300
4 赵六 400
5 沈散 100
*/
--select * from A_Region
/*
100 湖南
200 湖北
300 北京
400 上海
*/
--select * from A_Sales
/*
10 1 2005-07-08 00:00:00.000 100.0000
11 2 2005-09-08 00:00:00.000 200.0000
12 2 2006-03-09 00:00:00.000 150.0000
13 3 2006-08-09 00:00:00.000 140.0000
14 5 2007-01-01 00:00:00.000 250.0000
*/
select * from  A_Sales S left join A_Customer C on S.CID = C.ID
 left join A_Region R on C.RegionID = R.ID
/*
1.地区名称，销售总额,没有销售纪录的地区显示为0
*/
select R.RName as 地区名称,isnull(sum(S.SaleValues),0)
from  A_Sales S
 right join A_Customer C on S.CID = C.ID
--区域有客户但是没有销售纪录，如果销售纪录不能对应到客户，那么这条纪录就无法查询
 right join A_Region R on C.RegionID = R.ID
--区域可能没有客户
 group by R.RName
/*
2.地区名称，销售额，销售时间。每个区域最后一条销售纪录
*/
select R.RName as 地区名称,max(S.SaleDate) as 销售时间
 from  A_Sales S left join A_Customer C on S.CID = C.ID
 left join A_Region R on C.RegionID = R.ID
 group by R.RName

declare @tbl table (tblID int identity(1,1) , RID int)
declare @tblSale table (SID int)
insert into @tbl (RID)
select distinct R.ID from  A_Sales S left join A_Customer C on S.CID = C.ID
 left join A_Region R on C.RegionID = R.ID
declare @Total int
declare @Cnt int
select @Total = max(tblID) from @tbl
set @Cnt = 1
while(@Cnt <= @Total)
begin
 insert into @tblSale (SID)
 select top 1 S.ID from  A_Sales S left join A_Customer C on S.CID = C.ID
 left join A_Region R on C.RegionID = R.ID where R.ID in (select RID from @tbl where tblID =  @Cnt)
 order by S.SaleDate DESC
 set @Cnt = @Cnt + 1
end
select R.RName as 地区名称,S.SaleDate as 销售时间,S.SaleValues as 销售额
 from  A_Sales S left join A_Customer C on S.CID = C.ID
 left join A_Region R on C.RegionID = R.ID
 where S.ID in (select SID from @tblSale)
 
 

--order by 语句运行在top语句之前
select top 2 a.* from (select top 4 * from A_Sales order by ID asc) as a order by a.ID desc
/*
13 3 2006-08-09 00:00:00.000 140.0000
12 2 2006-03-09 00:00:00.000 150.0000
*/