--三种方法查询人所在部门平均工资

--建表

IF OBJECT_ID(N'Emp','U') IS NOT NULL

    BEGIN

        PRINT 'This table have been existed';

       

    END

ELSE

    BEGIN

        CREATE TABLE [dbo].[Emp]

        (

            [Name]       NVARCHAR(50) ,

            [Slary]      INT,

            [DepID]      INT

          

        )

 

         INSERT INTO [Emp]

           SELECT N'张三', '100', 1

           UNION ALL

           SELECT N'李四', '200', 1

           UNION ALL

           SELECT N'王五', '200', 2

           UNION ALL

           SELECT N'张麻子', '300', 2

           UNION ALL

           SELECT N'一柱擎天', '300', 2

           UNION ALL

           SELECT N'陈勃', '400', 3

             

    END

-------------------------------------------------------------------------------------------------------------------------

--方法:Nested Loop

SELECT a.*,(SELECT AVG(Slary) FROM [Emp] b WHERE b.DepID=a.DepID ) 

  FROM [Emp] a

--方法:开窗

SELECT *, AVG(Slary) OVER(PARTITION BY DepID)

  FROM [Emp] 

--方法:

SELECT a.*, b.Avg

  FROM [Emp] a INNER JOIN (SELECT DepID, AVG(Slary) Avg FROM [Emp] GROUP BY DepID) b ON a.DepID=b.DepID