用SQL Server Having计算列和平均值

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()