用SQL Server Having计算列和平均值(2)

USE [AdventureWorks]
GO
/****** Object:  UserDefinedFunction 
[dbo].[SalesTotals_fnt]    
Script Date: 12/09/2006 11:32:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SalesTotals_fnt]()
RETURNS TABLE
AS RETURN      
(SELECT SalesOrderID, 
SUM(LineTotal)AS TotalSale      
FROM Sales.SalesOrderDetail      
GROUP BY Sales.SalesOrderDetail.SalesOrderID)

CREATE FUNCTION [dbo].[AverageSale_fns] (      
-- Add the parameters for the function here      
--  )RETURNS money
ASBEGIN      
-- Declare the return variable here      
DECLARE @Result money
-- Add the T-SQL statements to compute the return value here      
SET @Result =(SELECTAvg(TotalSale)AS AverageSale      
FROM dbo.SalesTotals_fnt())
-- Return the result of the function      
RETURN @Result
END

DECLARE @Avg moneySELECT @Avg = dbo.AverageSale_fns()
SELECT       *, @Avg as Average,      
TotalSale / @Avg as Ratio,      CASE             
WHEN TotalSale / @Avg > 1 THEN 'Above Average'           
 WHEN TotalSale / @Avg < 1 THEN 'Below Average'            
ELSE 'Average'      END
FROM dbo.SalesTotals_fnt()