SQL进阶提升（平时小积累）-学习sql server2005 step by step(十)
积累一：
动态交叉表行转列
代码
1 ----------------行列的转换
2 --测试环境
3 Create table #T(Date datetime,Type varchar(20),qty int,Amount int)
4 insert into #T select '2006-01-02','早上',2,50
5 union all select '2006-01-02','中午',1,20
6 union all select '2006-01-02','晚上',3,30
7 union all select '2006-01-02','零晨',5,40
8 union all select '2006-01-03','早上',1,40
9 union all select '2006-01-03','中午',5,60
10 union all select '2006-01-03','晚上',2,50
11 union all select '2006-01-03','零晨',1,50
12 union all select '2006-01-04','早上',2,80
13 union all select '2006-01-04','中午',4,60
14 union all select '2006-01-04','晚上',3,20
15 union all select '2006-01-04','零晨',1,40
16 select * from #T
17
18
19 --动态sql交叉表
20 DECLARE @S VARCHAR(MAX),@s1 varchar(max)
21 SET @S=''
22 SELECT @S=@S+',['+Type+']' FROM #T
23 GROUP BY [Type]
24 print @s
25 SELECT @S1=isnull(@S1+',','') +'b.['+Type+'] as qty'+[type] FROM #T
26 GROUP BY [Type]
27 print @s1
28
29 SET @S=STUFF(@S,1,1,'')
30 declare @m varchar(8000)
31 set @m='
32 select a.*,'+@S1+'
33 from (
34 select Date,'+@S+'--,金额小计=(select sum(Amount) from #T where Date=PT.Date )
35 from (select date,type,amount from #T) as p
36 PIVOT
37 ( max(Amount)
38 for [type] in ('+@S+')
39 ) as PT) a
40 ,
41 (
42 select Date,'+@S+'--,qty小计=(select sum(Amount) from #T where Date=PT.Date )
43 from (select date,type,qty from #T) as p
44 PIVOT
45 ( max(qty)
46 for [type] in ('+@S+')
47 ) as PT) b
48 where a.date = b.date
49 '
50
51
52
53 exec(@m)
54
55
56
57 Drop Table #T
结果：
-------------master..spt_values自然數從零開始增長 type='p' 變異數 number---------------------------
代码
1 declare @t table(id int, name varchar(10),idCardNum varchar(20),num int)
2 insert @t select
3 1, '张三' , '230103198210295729' , 3 union all select
4 2 , '李四', '230103198210295729' , 2
5
6 select name=a.name+case when ltrim(number)='0' then '' else ltrim(number) end ,
7 idCardNum=case when number=0 then idCardNum else null end,
8 sex=case when substring(idCardNum,17,1)%2=0 then '女' else '男' end,
9 birthday=substring(idCardNum,7,8)
10 from master..spt_values b
11 join @t a
12 on number<a.num and number>=0 and type='p'
13
14 select substring('230103198210295729',17,1)
15 --name idCardNum sex birthday
16 ------------------------ -------------------- ---- ----------------
17 --张三 230103198210295729 女 19821029
18 --张三1 NULL 女 19821029
19 --张三2 NULL 女 19821029
20 --李四 230103198210295729 女 19821029
21 --李四1 NULL 女 19821029
22 --
23 --(5 行受影响)
24
积累二：
sql 获取ip地址
代码
1 select
2 hostname as computername,
3 program_name as programname,
4 *
5 from master..sysprocesses
6 where dbid=db_id( 'GDS_Service ')
7
8 --SQL Server 2005: SQL Server blocked access to procedure 'sys.xp_cmdshell' 错误解决方法
9 sp_configure 'show advanced options', 1
10 GO
11 RECONFIGURE
12 GO
13 sp_configure 'xp_cmdshell', 1
14 GO
15 RECONFIGURE
16 GO
17
18 declare @ip varchar(15),@sql varchar(1000)
19
20 --得到ip地址
21 create table #ip(a varchar(200))
22 set @sql='ping '+host_name()+' -a -n 1 -l 1'
23 insert into #ip exec master..xp_cmdshell @sql
24
25 select @ip=left(a,patindex('%:%',a)-1) from(
26 select a=substring(a,patindex('Ping statistics for %:%',a)+20,20)
27 from #ip where a like 'Ping statistics for %:%') a
28 select * from #ip
29 --显示结果
30 select computername=host_name(),ipaddress=@ip
31
32 drop table #ip
签核流程：
代码
1 select * into #ta from (
2 select '001' 工号,'李1' 姓名
3 union all
4 select '002' 工号,'李2' 姓名
5 union all
6 select '003' 工号,'李3' 姓名
7 union all
8 select '004' 工号,'李4' 姓名
9 union all
10 select '005' 工号,'李5' 姓名
11 union all
12 select '006' 工号,'李6' 姓名
13 union all
14 select '007' 工号,'李7' 姓名
15 union all
16 select '008' 工号,'李8' 姓名
17 ) a
18
19 select * into #tb from
20 (select 'aaa' 流程编号 , '加班申请' 流程名 , '003' 发起人
21 union all
22 select 'bbb' 流程编号 , '请假申请' 流程名 , '004' 发起人
23 union all
24 select 'ccc' 流程编号 , '加班申请' 流程名 , '002' 发起人
25 union all
26 select 'ddd' 流程编号 , '出差申请' 流程名 , '001' 发起人
27 union all
28 select 'eee' 流程编号 , '培训申请' 流程名 , '005' 发起人)
29 b
30
31 select * into #tc from (
32 select 'aaa' 流程编号 , '申请' 流程步骤 , '003' 办理人
33 union all
34 select 'aaa' 流程编号 , '同意' 流程步骤 , '002' 办理人
35 union all
36 select 'bbb' 流程编号 , '申请' 流程步骤 , '004' 办理人
37 union all
38 select 'bbb' 流程编号 , '退回' 流程步骤 , '005' 办理人
39 union all
40 select 'bbb' 流程编号 , '申请' 流程步骤 , '004' 办理人
41 union all
42 select 'bbb' 流程编号 , '同意' 流程步骤 , '005' 办理人
43 union all
44 select 'ccc' 流程编号 , '申请' 流程步骤 , '002' 办理人
45 union all
46 select 'ccc' 流程编号 , '退回' 流程步骤 , '006' 办理人
47 union all
48 select 'ccc' 流程编号 , '申请' 流程步骤 , '002' 办理人
49 union all
50 select 'ccc' 流程编号 , '再次退回' 流程步骤 , '006' 办理人
51 union all
52 select 'ccc' 流程编号 , '申请' 流程步骤 , '002' 办理人
53 union all
54 select 'ccc' 流程编号 , '同意' 流程步骤 , '008' 办理人
55 ) c
56
57
58 ;with f as
59 (
60 select
61 b.流程编号 , b.流程名 , a.姓名 AS 发起人 , c.流程步骤 , c.姓名 AS 办理人
62 from
63 #ta a ,
64 #tb b ,
65 (select a.*,b.姓名 AS 姓名 from #tc a left join #ta b on a.办理人 = b.工号) c
66 where
67 a.工号 = b.发起人 and b.流程编号 = c.流程编号
68 )
69 --select rn=row_number() over(partition by 流程编号,流程名,发起人 order by 发起人),* from f
70 --(这种临时表只能使用一次)
71 --select * from f
72 select
73 case when rn=1 then 流程编号 else '' end as 流程编号,
74 case when rn=1 then 流程名 else '' end as 流程名,
75 case when rn=1 then 发起人 else '' end as 发起人,流程步骤 ,办理人
76 from
77 (select rn=row_number() over(partition by 流程编号,流程名,发起人 order by 发起人),* from f)t
78
79
80
81 drop table #ta
82 drop table #tb
83 drop table #tc
84
85 /*流程编号 流程名 发起人 流程步骤 办理人
86 ---- -------- ---- -------- ----
87 aaa 加班申请 李3 申请 李3
88 同意 李2
89 bbb 请假申请 李4 申请 李4
90 退回 李5
91 申请 李4
92 同意 李5
93 ccc 加班申请 李2 申请 李2
94 退回 李6
95 申请 李2
96 再次退回 李6
97 申请 李2
98 同意 李8
99
100 (12 行受影响)
101 */
替代料的分配：
代码
1 --数量分配
2
3 create table #t(
4 id int identity(1,1),
5 group_id int,
6 name nvarchar(02),
7 value int
8 )
9 Go
10 Insert into #t(group_id,name,value) select 1,'A',100
11 Insert into #t(group_id,name,value) select 1,'B',50
12 Insert into #t(group_id,name,value) select 1,'C',-70
13 Insert into #t(group_id,name,value) select 1,'D',-130
14 Insert into #t(group_id,name,value) select 2,'E',30
15 Insert into #t(group_id,name,value) select 2,'F',150
16 Insert into #t(group_id,name,value) select 2,'G',-50
17 Insert into #t(group_id,name,value) select 2,'H',-100
18 Insert into #t(group_id,name,value) select 2,'I',-200
19 GO
20 select * from #t
21 select a.name as name1
22 ,a.value as value1
23 ,b.name as name2
24 ,b.value as value2
25 ,case when a.v2+b.V2<=0 then case when a.v2+b.V1<=a.value then a.v2+b.V1 else a.value end
26 else case when -(a.v1+b.V2)<= -b.value then -(a.v1+b.V2) else -b.value end
27 end as adjust_value
28 from (
29 select *,isnull((select sum(value) from #t where group_id = t.group_id and name< t.name and value>0),0) as V1
30 ,(select sum(value) from #t where group_id = t.group_id and name<= t.name and value>0) as V2
31 from #t t
32 where value>0
33 ) as a,(
34 select *,isnull((select sum(value) from #t where group_id = t.group_id and name< t.name and value<0),0) as V1
35 ,(select sum(value) from #t where group_id = t.group_id and name<= t.name and value<0) as V2
36 from #t t
37 where value<0
38 ) as b
39 where a.group_id=b.group_id
40 --a.V1+b.V2<=0上一级够，本级提供。
41 --a.V2+b.V1>=0 为上一级提供有剩为本机提供
42 and a.V2+b.V1>=0 and a.V1+b.V2<=0
43 order by a.name,b.name
44
45
46 select *,isnull((select sum(value) from #t where group_id = t.group_id and name< t.name and value>0),0) as V1
47 ,(select sum(value) from #t where group_id = t.group_id and name<= t.name and value>0) as V2
48 from #t t
49 where value>0
50
51
52 select *,isnull((select sum(value) from #t where group_id = t.group_id and name< t.name and value<0),0) as V1
53 ,(select sum(value) from #t where group_id = t.group_id and name<= t.name and value<0) as V2
54 from #t t
55 where value<0
56
57 ----结果
58 --group_id name1 value1 name2 value2 adjust_value
59 ------------- ----- ----------- ----- ----------- ------------
60 --1 A 100 C -70 70
61 --1 A 100 D -130 30
62 --1 B 50 D -130 50
63 --2 E 30 G -50 30
64 --2 F 150 G -50 20
65 --2 F 150 H -100 100
66 --2 F 150 I -200 30
如果把前面的实例一个个的一运行，或者用手敲一遍，相信中等难度的储存过程我们都能写出来了，我一开始也是一步步运行，复杂的储存过程就拆成一个个小的步骤运行，
遇到看不懂的函数或者陌生的全局变量就百度或者Google。希望大家能学到一点点东西，工作之余我会继续共享我的所得。。。。
sql个人收集资料下载地址
有错误的地方欢迎大家拍砖,希望交流和共享。
