join，group by 的一些非常规用法

realgz 2003-12-27 04:01:05

续：
http://expert.csdn.net/Expert/TopicView1.asp?id=2607573
本来想写行合并的但一时没想好，还是说说join 吧。
先再次声明一下，本人不是很喜欢使用join 运算符，喜欢直接用=、*=、=*表示各种join 。

3、join，group by 的非常规用法
本人认为,下列要求都可以考虑使用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 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

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可以说的还实在太多，自己体会妙处吧。

...全文