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]
