附：一个通用从纵到横的简单转换存储过程
if object_id('make_fun') is not null
drop procedure make_fun
go
create procedure make_fun
(@table_to_turn varchar(255), --待旋转的表
@key_col varchar(255), --保留的关键字段
@col_know_how varchar(255), --生成列名的字段
@col_to_turn varchar(255), --作为值的字段
@how_to varchar(20)='sum') --生成值的方式 sum min max avg ,etc.
/*
过程作用，根据纵向数据生成新横向结构
by realgz@csdn 2003-12-26
*/
as
declare @exec varchar(8000)create table #tmp (col varchar(255))set @exec='select distinct '+@col_know_how+ ' from '+@table_to_turn
insert into #tmp exec (@exec)
set @exec=''select @exec=@exec+@how_to+'(case when ['+@col_know_how+']= '''+col+''' then ['+@col_to_turn +'] else null end ) as ['+col+'],'
from #tmp
set @exec=left(@exec,len(@exec)-1)
set @exec='select ['+@key_col+'],'+@exec+' from ['+@table_to_turn+'] group by ['+@key_col+']'exec(@exec)
go
3、join，group by 的非常规用法
本来想写行合并的但一时没想好，还是说说join 吧。
先再次声明一下，本人不是很喜欢使用join 运算符，喜欢直接用=、*=、=*表示各种join 。
本人认为,下列要求都可以考虑使用join的思路来解决问题
a、涉及列数变多的情况，就可以使用 join 。
例：
有表 emp
emp_no name
001 Tom
002 Green
003 Sam
004 Sun
005 Hale
想变成两列显示
emp_no1 name1 emp_no2 name2
001 Tom 004 Sun
002 Green 005 Hale
003 Sam大家还记得在2里我们怎么产生序列号的吗？
于是有了
select e1.name as name1,e1.emp_no as emp_no1,e2.name as name2,e2.emp_no as emp_no2
from
(select emp.* ,(select count(*) from emp x where x.emp_no<=emp.emp_no) as cnt from emp) e1,
(select emp.* ,(select count(*) from emp x where x.emp_no<=emp.emp_no) as cnt from emp) e2,
where (e1.cnt-((select count(*) from emp)/2))*=e2.cnt变形一下就有另一种表达方法，就不多写了。b、涉及对比可以使用join
对比，除了等于 还有 存在 不存在 大于等于……
最简单的应用
选择在表a、b 中都存在的 id是
select a.id
from a join b
on a.id=b.id
如果是不存在的呢？
select a.id
from a left oute join b
on a.id=b.id
where a.id+b.id is null
这样的对比自然没什么意义，但是通过对比思想来获得组合的思想有时候就很宝贵了，
有表 Num
cnt
1
2
3
4
5
6
8
问随意取出不同4个能有多少种组合？
解法是
select t1.cnt as cnt1, t2.cnt as cnt2, t3.cnt as cnt3, t4.cnt as cnt4
from Num t1,Num t2,Num t3,Num t4
where t1.cnt>t2.cnt and t2.cnt>t3.cnt and t3.cnt>t4.cnt
或存在表 in_out
id obj time
1 in 2003-12-27 13:30
1 out 2003-12-27 14:29
2 in 2003-12-27 12:30
2 out 2003-12-27 15:30
要求显示在1个小时内有出入的的记录
就可以写成
select t1.*
from in_out t1,in_out t2
where
t1.id=t2.id
and (case when t1.obj='in' then 'out' else null end )=t2.obj
and convert(float,t2.time-t1.time)<=(1.0000/24/60)*60
c、需要控制行显示的时候考虑使用外连接
数据表Test
OrderID Num Delivery Cqty
S0001 1 20031231 10000
S0001 2 20031201 5000
S0001 2 20031220 5000
S0001 3 20031201 2000
S0001 3 20031205 3000
S0001 3 20031210 2500
S0001 3 20031215 1000
S0001 3 20031201 1500
S0002 1 20031130 5000
S0002 2 20031130 1000
S0002 2 20031205 4000要求查询得到的结果如下：
单号 原始交期 原始交数 1st交期 1st数量 2nd交期 2nd数量 3rd交期 3rd数量
----- -------- ------- -------- ------ -------- ------- -------- -------
S0001 20031231 10000 20031201 5000 20031201 2000
20031220 5000 20031205 3000
20031210 2500
20031215 1000
20031220 1500
S0002 20031130 5000 20031130 1000
20031205 4000像这样的问题，大家会注意到这是一个不完全条件的行列转换，由于对于一个单号，哪个交期的数据最多也不预先知道，所以你想进行行列转换的时候没有办法确定行要压缩到几行，由于对于每个单号 只有第一笔有完整数据，所以几乎无法控制行的显示顺序。
分析如下：
纵横转换可以选择的方法有 join 和 group by
在这里由于行数找不到可以界定的方法，显然没有办法直接使用 group by ，join 因为有 outer join 所以可以控制行数，并界定一行的位置，如果能确定用哪个来outer join 就好了。
由于对多个 单号的查询比较难有思路，于是决定只取一个单号进行思考，思考的结果是进行 right outer join 。然后用一笔数据就删除一笔数据。然后因为在结果里缺损排序数据所以考虑使用identity列作为排序列。
最后的思路是建一个 Test 表的临时表副本 #t 然后使用下面语句的循环解决。
insert into #rlt (OrderID,Delivery,Cqty,Delivery1,Cqty1,Delivery2,Cqty2,Delivery3,Cqty3)
select top 1 r1.OrderID,r1.Delivery,r1.Cqty,r2.Delivery,r2.Cqty,r3.Delivery,r3.Cqty,r4.Delivery,r4.Cqty
from (select * from #t where OrderID=@OrderID and Num=4) r4,
(select * from #t where OrderID=@OrderID and Num=3) r3,
(select * from #t where OrderID=@OrderID and Num=2) r2,
(select * from #t where OrderID=@OrderID and Num=1) r1
where r1.OrderID*=r2.OrderID and r1.OrderID*=r3.OrderID and r1.OrderID*=r4.OrderID group by 一般是用来分组数据，比如说有表 emp
emp_no emp_name emp_age
001 Tom 19
002 Sam 55
003 Smith 33
要求按照10岁的跨度汇总各年龄段内的人数：
select min(convert(varchar(3),(emp_age/10)*10)+'~'+convert(varchar(3),(emp_age/10)*10+9)) as 年龄段,count(*) as 人数
from emp
group by emp_age/10同时 需要注意 group by 子句和聚合函数的作用 如上例里再加上
having count(*) >2
则只显示在段内超过两人的项目。但是它同样可以用来过滤重复，这在2中已经有了相关表达.更重要的是它在与join 联用的时候可以提取特征数据！
例表in_out
id obj time
1 in 2003-12-27 13:30
1 out 2003-12-27 14:29
2 in 2003-12-27 12:30
2 out 2003-12-27 15:30
1 in 2003-12-28 13:30
1 out 2003-12-28 14:29
2 in 2003-12-28 12:30
2 out 2003-12-28 15:30
要求取出各人的停留时间。
按照常规的做法，你可能被迫要做一个游标来遍历，以判断哪个in和哪个out配对，但有了 join 和 group by 你可以解放出来。
根据特征 离进来最近的一笔出去的记录就是对应的出去 记录于是有:
select t1.id,t1.time as in_time,min(t2.time) as out_time,convert(varchar(8),t1.time-min(t2.time),108) as length
from in_out t1,in_out t2
where
t1.id=t2.id
and (case when t1.obj='in' then 'out' else null end )=t2.obj
and t1.time<t2.time
group by t1.time
除了了join 连用，和union 连用 group by 的威力也非常强大
有组权限表 pep_grou
grou_no obj access
01 N01 128
01 N02 128
02 N02 256
用户权限表 pep_usr
usr_no obj access
U01 N01 64
U01 N03 25
和用户表
usr_no grou_no
U01 01
U02 02
要求生成每具体用户的最终权限，其中如果在组和用户权限表里都存在对相应对象的权限记录，则access的值取它们按位 AND的值（在SQL Server 里用&表示)，如果只在一个 地方有记录则只取这条记录
解决思路有2，1是用 join 然后用 case 取，实现过程就不写了，另一个思路就是使用group by：
select user_no,obj,(min(access)&max(access)) as access
from
(
select usr_no,obj,access from usr
union all
select u.usr_no,g.obj,g.access
from
pep_usr u,pep_grou g
where u.grou_no=g.grou_no
) rlt
group by user_no,obj思路简单，先根据组权限生成用户权限，然后巧妙地使用聚合函数取出可能的不同access做AND的操作。
join和group by可以说的还实在太多，自己体会妙处吧。
if object_id('make_fun') is not null
drop procedure make_fun
go
create procedure make_fun
(@table_to_turn varchar(255), --待旋转的表
@key_col varchar(255), --保留的关键字段
@col_know_how varchar(255), --生成列名的字段
@col_to_turn varchar(255), --作为值的字段
@how_to varchar(20)='sum') --生成值的方式 sum min max avg ,etc.
/*
过程作用，根据纵向数据生成新横向结构
by realgz@csdn 2003-12-26
*/
as
declare @exec varchar(8000)create table #tmp (col varchar(255))set @exec='select distinct '+@col_know_how+ ' from '+@table_to_turn
insert into #tmp exec (@exec)
set @exec=''select @exec=@exec+@how_to+'(case when ['+@col_know_how+']= '''+col+''' then ['+@col_to_turn +'] else null end ) as ['+col+'],'
from #tmp
set @exec=left(@exec,len(@exec)-1)
set @exec='select ['+@key_col+'],'+@exec+' from ['+@table_to_turn+'] group by ['+@key_col+']'exec(@exec)
go
3、join，group by 的非常规用法
本来想写行合并的但一时没想好，还是说说join 吧。
先再次声明一下，本人不是很喜欢使用join 运算符，喜欢直接用=、*=、=*表示各种join 。
本人认为,下列要求都可以考虑使用join的思路来解决问题
a、涉及列数变多的情况，就可以使用 join 。
例：
有表 emp
emp_no name
001 Tom
002 Green
003 Sam
004 Sun
005 Hale
想变成两列显示
emp_no1 name1 emp_no2 name2
001 Tom 004 Sun
002 Green 005 Hale
003 Sam大家还记得在2里我们怎么产生序列号的吗？
于是有了
select e1.name as name1,e1.emp_no as emp_no1,e2.name as name2,e2.emp_no as emp_no2
from
(select emp.* ,(select count(*) from emp x where x.emp_no<=emp.emp_no) as cnt from emp) e1,
(select emp.* ,(select count(*) from emp x where x.emp_no<=emp.emp_no) as cnt from emp) e2,
where (e1.cnt-((select count(*) from emp)/2))*=e2.cnt变形一下就有另一种表达方法，就不多写了。b、涉及对比可以使用join
对比，除了等于 还有 存在 不存在 大于等于……
最简单的应用
选择在表a、b 中都存在的 id是
select a.id
from a join b
on a.id=b.id
如果是不存在的呢？
select a.id
from a left oute join b
on a.id=b.id
where a.id+b.id is null
这样的对比自然没什么意义，但是通过对比思想来获得组合的思想有时候就很宝贵了，
有表 Num
cnt
1
2
3
4
5
6
8
问随意取出不同4个能有多少种组合？
解法是
select t1.cnt as cnt1, t2.cnt as cnt2, t3.cnt as cnt3, t4.cnt as cnt4
from Num t1,Num t2,Num t3,Num t4
where t1.cnt>t2.cnt and t2.cnt>t3.cnt and t3.cnt>t4.cnt
或存在表 in_out
id obj time
1 in 2003-12-27 13:30
1 out 2003-12-27 14:29
2 in 2003-12-27 12:30
2 out 2003-12-27 15:30
要求显示在1个小时内有出入的的记录
就可以写成
select t1.*
from in_out t1,in_out t2
where
t1.id=t2.id
and (case when t1.obj='in' then 'out' else null end )=t2.obj
and convert(float,t2.time-t1.time)<=(1.0000/24/60)*60
c、需要控制行显示的时候考虑使用外连接
数据表Test
OrderID Num Delivery Cqty
S0001 1 20031231 10000
S0001 2 20031201 5000
S0001 2 20031220 5000
S0001 3 20031201 2000
S0001 3 20031205 3000
S0001 3 20031210 2500
S0001 3 20031215 1000
S0001 3 20031201 1500
S0002 1 20031130 5000
S0002 2 20031130 1000
S0002 2 20031205 4000要求查询得到的结果如下：
单号 原始交期 原始交数 1st交期 1st数量 2nd交期 2nd数量 3rd交期 3rd数量
----- -------- ------- -------- ------ -------- ------- -------- -------
S0001 20031231 10000 20031201 5000 20031201 2000
20031220 5000 20031205 3000
20031210 2500
20031215 1000
20031220 1500
S0002 20031130 5000 20031130 1000
20031205 4000像这样的问题，大家会注意到这是一个不完全条件的行列转换，由于对于一个单号，哪个交期的数据最多也不预先知道，所以你想进行行列转换的时候没有办法确定行要压缩到几行，由于对于每个单号 只有第一笔有完整数据，所以几乎无法控制行的显示顺序。
分析如下：
纵横转换可以选择的方法有 join 和 group by
在这里由于行数找不到可以界定的方法，显然没有办法直接使用 group by ，join 因为有 outer join 所以可以控制行数，并界定一行的位置，如果能确定用哪个来outer join 就好了。
由于对多个 单号的查询比较难有思路，于是决定只取一个单号进行思考，思考的结果是进行 right outer join 。然后用一笔数据就删除一笔数据。然后因为在结果里缺损排序数据所以考虑使用identity列作为排序列。
最后的思路是建一个 Test 表的临时表副本 #t 然后使用下面语句的循环解决。
insert into #rlt (OrderID,Delivery,Cqty,Delivery1,Cqty1,Delivery2,Cqty2,Delivery3,Cqty3)
select top 1 r1.OrderID,r1.Delivery,r1.Cqty,r2.Delivery,r2.Cqty,r3.Delivery,r3.Cqty,r4.Delivery,r4.Cqty
from (select * from #t where OrderID=@OrderID and Num=4) r4,
(select * from #t where OrderID=@OrderID and Num=3) r3,
(select * from #t where OrderID=@OrderID and Num=2) r2,
(select * from #t where OrderID=@OrderID and Num=1) r1
where r1.OrderID*=r2.OrderID and r1.OrderID*=r3.OrderID and r1.OrderID*=r4.OrderID group by 一般是用来分组数据，比如说有表 emp
emp_no emp_name emp_age
001 Tom 19
002 Sam 55
003 Smith 33
要求按照10岁的跨度汇总各年龄段内的人数：
select min(convert(varchar(3),(emp_age/10)*10)+'~'+convert(varchar(3),(emp_age/10)*10+9)) as 年龄段,count(*) as 人数
from emp
group by emp_age/10同时 需要注意 group by 子句和聚合函数的作用 如上例里再加上
having count(*) >2
则只显示在段内超过两人的项目。但是它同样可以用来过滤重复，这在2中已经有了相关表达.更重要的是它在与join 联用的时候可以提取特征数据！
例表in_out
id obj time
1 in 2003-12-27 13:30
1 out 2003-12-27 14:29
2 in 2003-12-27 12:30
2 out 2003-12-27 15:30
1 in 2003-12-28 13:30
1 out 2003-12-28 14:29
2 in 2003-12-28 12:30
2 out 2003-12-28 15:30
要求取出各人的停留时间。
按照常规的做法，你可能被迫要做一个游标来遍历，以判断哪个in和哪个out配对，但有了 join 和 group by 你可以解放出来。
根据特征 离进来最近的一笔出去的记录就是对应的出去 记录于是有:
select t1.id,t1.time as in_time,min(t2.time) as out_time,convert(varchar(8),t1.time-min(t2.time),108) as length
from in_out t1,in_out t2
where
t1.id=t2.id
and (case when t1.obj='in' then 'out' else null end )=t2.obj
and t1.time<t2.time
group by t1.time
除了了join 连用，和union 连用 group by 的威力也非常强大
有组权限表 pep_grou
grou_no obj access
01 N01 128
01 N02 128
02 N02 256
用户权限表 pep_usr
usr_no obj access
U01 N01 64
U01 N03 25
和用户表
usr_no grou_no
U01 01
U02 02
要求生成每具体用户的最终权限，其中如果在组和用户权限表里都存在对相应对象的权限记录，则access的值取它们按位 AND的值（在SQL Server 里用&表示)，如果只在一个 地方有记录则只取这条记录
解决思路有2，1是用 join 然后用 case 取，实现过程就不写了，另一个思路就是使用group by：
select user_no,obj,(min(access)&max(access)) as access
from
(
select usr_no,obj,access from usr
union all
select u.usr_no,g.obj,g.access
from
pep_usr u,pep_grou g
where u.grou_no=g.grou_no
) rlt
group by user_no,obj思路简单，先根据组权限生成用户权限，然后巧妙地使用聚合函数取出可能的不同access做AND的操作。
join和group by可以说的还实在太多，自己体会妙处吧。
收藏了好久了，今天整理收藏夹看到好贴竟然没人顶...
期待中...
1.还没要完么，记得出关后就开始了，大郁闷ing???
2.春节期间不上网了，楼主如此期间出续集，请在此贴中提及或发信息，先谢了，你的贴对我很有帮助