十年

  :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
  92 随笔 :: 95 文章 :: 739 评论 :: 1 引用

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 于 博客园
posted on 2005-04-25 12:18  留不住的时光  阅读(3085)  评论(7编辑  收藏