--(1)数据统计与汇总
declare @Tb table
(
  ID int identity(1,1) primary key,
  EmpoyeeName varchar(50),
  Salary int
)
insert into @Tb select 'aa',1200
union all select 'bb',1300
union all select 'cc',2400
union all select 'bb',900
union all select 'bb',1800
union all select 'cc',700
union all select 'aa',600
select EmpoyeeName,
sum(Salary) Salary
from @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 '胡果',1
insert into @Employee select '小梁',1
insert into @Employee select '近身剪',2
insert 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) SalePrice
from
(
    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
) D
left 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 and
O.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.00
B部门      0.00      0.00       1200.00    1200.00     600.00     0.00        3000.00
C部门      900.00    900.00     0.00       900.00      600.00     0.00        3300.00
*/

--(2)使用union all
declare @TB table
(
  Item varchar(20),
  Color varchar(20),
  Quantity int
)
insert into @TB select 'Table','Blue',124
union all select 'Table','Red',-23
union all select 'Chair','Blue',101
union all select 'Chair','Red',91

select 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 Item
union all
select Item,Color,Quantity from @TB where