笔记238 将别名列作为select字段 2013-4-15

  1 --将别名列作为select字段 2013-4-15
  2 --http://social.technet.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/447ff60f-be88-431d-8cd2-2daded4d4e72
  3 use tempdb
  4 go
  5 
  6 if object_id('ReceivableTable') is not null
  7      drop table ReceivableTable
  8 go
  9 
 10 create table ReceivableTable
 11 (
 12   sn int identity primary key,
 13   addr nvarchar(50),
 14   MonthRentR01 int,
 15   MonthRentR02 int,
 16   MonthRentR03 int
 17 )
 18 
 19 
 20 
 21 
 22 --ALTER TABLE [dbo].[ReceivableTable] ADD  ReceivableState INT
 23 
 24 insert into ReceivableTable
 25 select 'add1',0,0,0
 26 union all
 27 select 'add2',100,100,0
 28 union all
 29 select 'add3',200,0,200
 30 union all
 31 select 'add4',300,300,300
 32 union all
 33 select 'add5',0,0,400
 34 union all
 35 select 'add6',500,0,0
 36 
 37 DECLARE @i INT
 38 SET @i = 1
 39 WHILE @i < 7
 40     BEGIN
 41         IF @i % 2 = 0
 42             BEGIN
 43                 UPDATE  [dbo].[ReceivableTable]
 44                 SET     [ReceivableState] = 0
 45                 WHERE   [sn] = @i
 46             END
 47         ELSE
 48             BEGIN
 49                 UPDATE  [dbo].[ReceivableTable]
 50                 SET     [ReceivableState] = 1
 51                 WHERE   [sn] = @i
 52             END
 53         SET @i = @i + 1
 54     END
 55 
 56 if object_id('OppositeSide') is not null
 57      drop table OppositeSide
 58 go
 59 
 60 create table OppositeSide
 61 (
 62 name nvarchar(10),
 63 addr nvarchar(50)
 64 )
 65 
 66 insert into OppositeSide
 67 select N'張三',N'add1'
 68 union all
 69 select N'李四',N'add2'
 70 union all
 71 select N'王五',N'add5'
 72 
 73 
 74 SELECT * FROM [dbo].[OppositeSide]
 75 SELECT * FROM ReceivableTable
 76 
 77 
 78 
 79 --没有加order by的
 80 SELECT  Addr ,
 81         MonthRentR01 ,
 82         MonthRentR02 ,
 83         MonthRentR03
 84 FROM    ReceivableTable
 85 UNION ALL
 86 SELECT  '合计' AS addr ,
 87         SUM(MonthRentR01) ,
 88         SUM(MonthRentR02) ,
 89         SUM(MonthRentR03)
 90 FROM    ReceivableTable
 91 
 92 
 93 
 94 ------------------------------------------------------------------------------
 95 --terry chuang使用with rollup
 96 --select isnull(addr,N'合計') addr
 97 --,sum(MonthRentR01) MonthRentR01
 98 --,isnull((select name
 99 --  from OppositeSide b
100 --  where b.addr = a.addr),'') OppositeSide
101 --,sum(MonthRentR02) MonthRentR02
102 --,sum(MonthRentR03) MonthRentR03
103 --from ReceivableTable a
104 --group by addr
105 --with rollup
106 --
107 --drop table ReceivableTable
108 ----------------------------------------------------------------------------------------
109 --我自己的
110 if object_id('#tb') is not null
111      drop table #tb
112 
113 
114 SELECT     addr, MonthRentR01, MonthRentR02, MonthRentR03,[ReceivableState] INTO #tb
115 from ReceivableTable
116 ORDER BY ReceivableState desc, [addr] asc
117 GO
118 ------------------------------------------------------------------------------------
119 INSERT INTO [#tb]([addr],[MonthRentR01],[MonthRentR02],[MonthRentR03]) SELECT  '合计'   ,  Sum(MonthRentR01),  Sum(MonthRentR02),  Sum(MonthRentR03) FROM [dbo].[ReceivableTable]
120 
121 ------------------------------------------------------------------------------------
122 SELECT * FROM [#tb] ORDER BY [addr] ASC
123 
124 ---------------------------------------------------------------
125 --乱马客的答案
126 select *
127 from (
128 SELECT      [addr], MonthRentR01 , MonthRentR02 , MonthRentR03, 1 as sort_order
129 from ReceivableTable
130 union all
131 SELECT    ' 总数合计 ' ,   Sum ( MonthRentR01),   Sum( MonthRentR02 ),  Sum (MonthRentR03 ), 2 as sort_order
132 from ReceivableTable) T1
133 order by T1 .[addr]
134 
135 DROP TABLE [#tb]