周末写的点代码,希望各位拍砖

--(1)数据统计与汇总declare @Tb table(  ID int identity(1,1) primary key,  EmpoyeeName varchar(50),  Salary int)insert into @Tb select 'aa',1200union all select 'bb',1300union all select 'cc',2400union all select 'bb',900union all select 'bb',1800union all select 'cc',700union all select 'aa',600select EmpoyeeName,sum(Salary) Salaryfrom @Tb where Salary>1000 group by EmpoyeeName--派生数据--部门表declare @Dept table(  ID int identity(1,1) primary key,  DepartMent nvarchar(20))insert into @Dept select 'A部门'insert into @Dept select 'B部门'insert into @Dept select 'C部门'--员工表declare @Employee table(  ID int identity(1,1) primary key,  [Name] varchar(20),  DeptID int--与部门表中的ID相关联)insert into @Employee select '胡果',1insert into @Employee select '小梁',1insert into @Employee select '近身剪',2insert into @Employee select '树哥',3--订单表declare @Order table(  ID int identity(1,1) primary key,  EmployeeID int,--与员工表中的ID相关联  SalePrice decimal(10,2),  Date datetime)insert into @Order select 1,1000.00,'2009-1-1'union all select 1,900.00,'2009-3-1'union all select 2,800.00,'2009-3-8'union all select 2,700.00,'2009-3-18'union all select 3,1200.00,'2009-3-10'union all select 3,1200.00,'2009-4-10'union all select 3,600.00,'2009-5-1'union all select 4,900.00,'2009-1-18'union all select 4,900.00,'2009-2-18'union all select 4,900.00,'2009-4-18'union all select 4,600.00,'2009-5-11';with hgo as(  select D.DepartMent,Convert(varchar(6),dateadd(month,D.[month],'20081201'),112) 'Month',isnull(sum(O.SalePrice),0) SalePricefrom(    select D.ID,D.DepartMent,M.[month]    from @Dept D    cross join    (      select [month]=1 union all      select [month]=2 union all      select [month]=3 union all      select [month]=4 union all      select [month]=5 union all      select [month]=6    )M) Dleft join(  select E.DeptID,O.SalePrice,O.Date from @Employee E  join @Order O on E.ID=O.EmployeeID) O on O.DeptID=D.ID andO.Date>=dateadd(month,D.[month],'20081201')and O.Date<dateadd(month,D.[month],'20090101')group by D.DepartMent,D.[month])select DepartMent,sum(case when Month='200901' then SalePrice else 0 end) '一月份销售',sum(case when Month='200902' then SalePrice else 0 end) '二月份销售',sum(case when Month='200903' then SalePrice else 0 end) '三月份销售',sum(case when Month='200904' then SalePrice else 0 end) '四月份销售',sum(case when Month='200905' then SalePrice else 0 end) '五月份销售',sum(case when Month='200906' then SalePrice else 0 end) '六月份销售',sum(SalePrice) '上半年总业绩'from hgo group by DepartMent/*DepartMent 一月份销售 二月份销售 三月份销售 四月份销售 五月份销售 六月份销售 上半年总业绩--------- ---------- --------   ---------- ----------- ---------- ---------- ------------A部门      1000.00   0.00       2400.00     0.00       0.00       0.00        3400.00B部门      0.00      0.00       1200.00    1200.00     600.00     0.00        3000.00C部门      900.00    900.00     0.00       900.00      600.00     0.00        3300.00*/--(2)使用union alldeclare @TB table(  Item varchar(20),  Color varchar(20),  Quantity int)insert into @TB select 'Table','Blue',124union all select 'Table','Red',-23union all select 'Chair','Blue',101union all select 'Chair','Red',91select Item,Color,Quantity from @TB where Item='Table'union all select '',Item as 'TableTotal',sum(Quantity) as TableQua from @TB where Item='Table'group by Itemunion allselect Item,Color,Quantity from @TB where