SQL语言高级技法演练
--再谈列转行二维交叉表的实现
SQL语言高级技法演练
--再谈列转行二维交叉表的实现
|
某人在数据库中变态地设计了如下的一个表格,用于记录工厂预算额和实际发生额:
tkey
|
code
|
description
|
year
|
get_1
|
get_2
|
get_3
|
get_4
|
get_5
|
get_6
|
get_7
|
get_8
|
get_9
|
get_10
|
get_11
|
get_12
|
used_1
|
used_2
|
used_3
|
used_4
|
used_5
|
used_6
|
used_7
|
used_8
|
used_9
|
used_10
|
used_11
|
used_12
|
15
|
XX1
|
財務部預算
|
2004
|
10000000
|
10000000
|
10000000
|
10000000
|
10000000
|
10000000
|
10000000
|
10000000
|
10000000
|
10000000
|
10000000
|
10000000
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
16
|
XX2
|
工程部預算
|
2004
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000
|
1200
|
1300
|
1400
|
1000
|
2000
|
3000
|
1000
|
2000
|
3000
|
1002
|
2002
|
17
|
XX3
|
MIS部預算
|
2004
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
5000
|
200
|
2110
|
1566
|
200
|
330
|
55
|
222
|
222
|
111
|
33
|
322
|
18
|
XX4
|
市場部預算
|
2004
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
0
|
0
|
0
|
0
|
0
|
544
|
0
|
4522
|
5412
|
0
|
0
|
45553
|
19
|
XX5
|
動力部預算
|
2004
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
5200
|
5110
|
5412
|
5422
|
7521
|
0
|
4221
|
0
|
4221
|
4422
|
4695
|
5487
|
20
|
XX6
|
發展部預算
|
2004
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
5200
|
5110
|
5412
|
5422
|
7521
|
9442
|
4221
|
4522
|
0
|
4422
|
0
|
5487
|
21
|
XX7
|
計劃部預算
|
2004
|
3000000
|
9000000
|
9000000
|
9000000
|
9000000
|
9000000
|
9000000
|
9000000
|
9000000
|
8000000
|
4000000
|
3000000
|
5200
|
5110
|
5412
|
0
|
7521
|
0
|
4221
|
0
|
4221
|
0
|
4695
|
5487
|
22
|
XX8
|
生產部預算
|
2004
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
5200
|
5110
|
5412
|
5422
|
7521
|
9442
|
4221
|
4522
|
4221
|
4422
|
4695
|
5487
|
23
|
XX9
|
采購部預算
|
2004
|
1000000
|
1000000
|
20000000
|
20000000
|
9000000
|
9000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
5200
|
5110
|
5412
|
5422
|
7521
|
9442
|
4221
|
4522
|
4221
|
0
|
0
|
0
|
24
|
XX10
|
品質部預算
|
2004
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
5200
|
5110
|
5412
|
5422
|
0
|
0
|
4221
|
0
|
286
|
8744
|
522
|
44
|
25
|
XX11
|
行政部預算
|
2004
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
1000000
|
5200
|
5110
|
5412
|
0
|
7521
|
9442
|
4221
|
0
|
544
|
1224
|
4695
|
552
|
33
|
XX3
|
MIS預算
|
2005
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
0
|
5110
|
5412
|
5422
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
34
|
XX1
|
財務部預算
|
2005
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
35
|
XX4
|
市場部預算
|
2005
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
10000
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
36
|
XX11
|
行政部預算
|
2005
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
100000
|
0
|
0
|
0
|
120.84
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
但在程序中却又要以如下界面(方式)进行录入和显示:
tkey
|
code
|
description
|
年
|
月
|
限額
|
發生額
|
17
|
XX3
|
MIS部預算
|
2004
|
1
|
1000000
|
2060
|
17
|
XX3
|
MIS部預算
|
2004
|
2
|
1000000
|
312
|
17
|
XX3
|
MIS部預算
|
2004
|
3
|
1000000
|
453
|
17
|
XX3
|
MIS部預算
|
2004
|
4
|
1000000
|
65
|
17
|
XX3
|
MIS部預算
|
2004
|
5
|
1000000
|
611
|
17
|
XX3
|
MIS部預算
|
2004
|
6
|
1000000
|
767
|
17
|
XX3
|
MIS部預算
|
2004
|
7
|
1000000
|
12
|
17
|
XX3
|
MIS部預算
|
2004
|
8
|
1000000
|
2
|
17
|
XX3
|
MIS部預算
|
2004
|
9
|
1000000
|
295
|
17
|
XX3
|
MIS部預算
|
2004
|
10
|
1000000
|
411
|
17
|
XX3
|
MIS部預算
|
2004
|
11
|
1000000
|
162
|
17
|
XX3
|
MIS部預算
|
2004
|
12
|
1000000
|
188
|
怎幺办?你有办法吗?你能用最简洁的SQL语句来实现吗?
如果有兴趣,大家一起来探讨,我这里给出一个示例,可能不是最好的方法,
但也算是比较完美地解决了这道还算比较难办的题。
Create procedure sp_BudGet(
@keyid int
)
As
declare @sql varchar(8000)
set @sql=' select tkey,code,description,year,'
select @sql=@sql+rtrim(name)+' as 限額 ,used'+right(name,len(name)-3)+' as 發生額 from data2 where tkey='+cast(@keyid as varchar(3))+' union all select tkey,code,description,year, ' from syscolumns where id=object_id('data2') and name like 'get_%' order by colorder
set @sql=left(@sql,len(@sql)-len(' union all select tkey,code,description,year,'))
print @sql
exec(@sql)
GO
以上存储过程接受一个tkey的传入参数,用于显示某一部门的年度预算,
其中的要点在于在从"syscolumns"表中查询字段名时动态构建一条真实的查询语句,
并且在查询语句中用union合成12个月的预算额和实际发生额。当然,这个表中的字段有
一定的规律,要是全部都没规律,那就只能老老实实的一个一个的写了。
上面的表格貼得不大好,還請見諒,如有更好的語句,請告訴我。:)
|
上山砍柴去 2005-04-25 于 博客园 |
|