t****n
发帖数: 10724 | | t****n
发帖数: 10724 | 2 *******************************************************************
HR SQL难题的三种解法
*******************************************************************
摘要
本文综合描术了针对养老院人事难题的多种SQL解法。
鸣谢
Beijing
===========================================================
解法1 -- Partition 法
===========================================================
可以参考wyr解法，本解法用了lead/lag，以及ROWS UNBOUNDED PRECEDING AND
CURRENT ROW
原理一样，就是要达到排序后分组的目的
借用原题数据，第一步的目标加上ind1，指示此行是否是超过30天的行
User StartDate EndDate ind1
1 12/2/2011 1/16/2012 0
1 3/4/2012 3/24/2012 1
1 4/5/2012 4/26/2012 0
1 5/14/2012 6/7/2012 0
2 3/5/2012 7/30/2012 0
2 8/4/2012 9/15/2012 0
3 6/5/2012 8/20/2012 0
假设结果是t1，t1是这样得到的，请自行优化
select t11.*,
(t11.startdate - (LEAD/LAG)? (end_date,1) over (PARTITION BY ?? ORDER BY ??
)) t11.ind11
from t t11
ind11是相差的天数，自行转化成ind1 0（<=30），1（>30）
借用原题数据，第二步的目标加上ind2，指示分组
User StartDate EndDate ind1 ind2
1 12/2/2011 1/16/2012 0 0
1 3/4/2012 3/24/2012 1 1
1 4/5/2012 4/26/2012 0 1
1 5/14/2012 6/7/2012 0 1
2 3/5/2012 7/30/2012 0 0
2 8/4/2012 9/15/2012 0 0
3 6/5/2012 8/20/2012 0 1
假设结果是t2，t2是这样得到的，请自行优化
select t21.*,
(select sum(ind1) OVER (PARTITION BY id ORDER BY start_date ROWS UNBOUNDED .
... ) ind2
from t1 t21
分组成功，min，max
===========================================================
解法2 -- CTE 法
===========================================================
首先，这类复杂sql问题，建议大家用CTE(和Recursive无关)，每一步都分开，思路会
清晰得多
with t1 as(
select ....
from ....),
t2 as (
select...
from t1, .....),
....
select
from tn
用过oracle connect by的同学，Recursive CTE实现了类似功能，而且是ansi的，推荐
大家使用，当然，本题用connect by也可以
对于解决本题，思路和用Analytic Functions类似，就是要达到排序后分组的目的，以
原题数据为例，如果有以下标记，用group by user，标记值可得到min(startdate),
max(enddate)即可
User StartDate EndDate 标记值
1 12/2/2011 1/16/2012 0
1 3/4/2012 3/24/2012 1
1 4/5/2012 4/26/2012 1
1 5/14/2012 6/7/2012 1
2 3/5/2012 7/30/2012 0
2 8/4/2012 9/15/2012 0
3 6/5/2012 8/20/2012 0
先看一下原理
http://www.nocoug.org/download/2010-05/2010_303_Fernandez_ppt.p
http://www.morganslibrary.org/reference/with.html
http://technet.microsoft.com/en-us/library/ms186243%28v=sql.105
问题就来了，我们的起始的SQL应该是什么呢？我们的recursive应该怎么连呢？很显然
我们的起始SQL应该是每个user最早的startdate记录，下面记录就应该是一个一个按
startdate的升序排列。我们原始table没有这个序列数据，所以我们要给它加上，结果
假设是t1（use CTE！！）
select *, row_number() over (partition by user order by startdate) rn
from table
有了t1，我们上面的2个问题都解决了。起始sql就是select *, rn from t1 where rn=
1，连接就是靠user=user and rn=rn+1。排序连接解决了，但是我们的分组问题还没有
解决，也就是我们的标记ind的值还没有解决。在我们的Recursive CTE加入ind，起始
sql里面ind的值假定是0（select *, rn, 0 ind from t1 where rn=1, 任何值都行）
。我们是否能使ind值达到上面例子里分组的目的呢。答案是肯定的，每一个record，
如果和前面比没超过30天，这个record的ind就沿用上一个record的ind；如果超过30天
，就在上一个的基础上+1。这样问题就解决了。
===========================================================
解法3 -- 古典 法
===========================================================
原理一样，就是要达到排序后分组的目的
借用原题数据，第一步的目标加上ind1，指示此行是否是超过30天的行
User StartDate EndDate ind1
1 12/2/2011 1/16/2012 0
1 3/4/2012 3/24/2012 1
1 4/5/2012 4/26/2012 0
1 5/14/2012 6/7/2012 0
2 3/5/2012 7/30/2012 0
2 8/4/2012 9/15/2012 0
3 6/5/2012 8/20/2012 0
假设结果是t1，t1是这样得到的，请自行优化
select t11.*,
t11.startdate - (select MAX(t12.enddate) from t t21 where .. < ..) ind11
from t t11
ind11是相差的天数，自行转化成ind1 0（<=30），1（>30）
借用原题数据，第二步的目标加上ind2，指示分组
User StartDate EndDate ind1 ind2
1 12/2/2011 1/16/2012 0 0
1 3/4/2012 3/24/2012 1 1
1 4/5/2012 4/26/2012 0 1
1 5/14/2012 6/7/2012 0 1
2 3/5/2012 7/30/2012 0 0
2 8/4/2012 9/15/2012 0 0
3 6/5/2012 8/20/2012 0 1
假设结果是t2，t2是这样得到的，请自行优化
select t21.*,
(select sum(ind1) from t1 t22 where ...<=....) ind2
from t1 t21
分组成功，min，max | B*****g
发帖数: 34098 | | t****n
发帖数: 10724 | 4 荣耀归北京
包子归北京
【在 B*****g 的大作中提到】
: 你这是赤果果的剽窃，哈哈
| w*r
发帖数: 2421 | | B*****g
发帖数: 34098 | 6 一起打，玩sql的都是打酱油的
【在 w*r 的大作中提到】
: MD，我就是打酱油的
| t****n
发帖数: 10724 | 7 credit to wyr
【在 w*r 的大作中提到】
: MD，我就是打酱油的
|
|
