SQL解惑(第2版) 的一些样题

mdkii

呵呵， newkids 兄厉害。

mdkii

谜题 59 解答得很巧妙。
joe celko在 《thinking in set》（sql沉思录）里提到另一个解法也很巧妙：

select task_id, min_start_date,max(end_date)
from (
select task_id
      , start_date
      , max( case when start_date > max_end_date then start_date end)  ---这一步找到离自己最近的broken
                 over ( partition by task_id order by start_date,end_date
                            rows between unbounded preceding and current row ) as min_start_date
       ,end_date from (
select task_id
      ,start_date
      ,end_date
      ,coalesce(max(end_date)
           over ( partition by task_id order by start_date,end_date
                   rows between unbounded preceding and 1 preceding )
                   , start_date -2 days) as max_end_date
      from timesheets ) )
group by task_id,min_start_date


谜题 27 我想到一个SQL可以减少表的关联，
貌似结果也是对的：
WITH T(SNO,PNO,CNT)
     AS (SELECT SUPPARTS.*,
                COUNT(PNO)
                  OVER(PARTITION BY SNO ) AS CNT
         FROM   SUPPARTS)
SELECT   A.SNO,
         B.SNO
FROM     T A,
         T B
WHERE    A.PNO = B.PNO
         AND A.SNO < B.SNO
         AND A.CNT = B.CNT
GROUP BY A.SNO,B.SNO
HAVING   COUNT(* ) = MAX(A.CNT);

newkid

mdkii 发表于 2012-10-18 19:03
谜题 59 解答得很巧妙。
joe celko在 《thinking in set》（sql沉思录）里提到另一个解法也很巧妙：

很不错的写法！59题的找断点方法和我一样; 27题用分析函数进一步减少了连接次数。

xqmei

---For #59
select start_date, max(end_date) end_date from  (select * from (select a.*, rownum rn from Timesheets a order by start_date)
model
dimension by (rn)
measures(start_date, end_date)
rules ( start_date[rn>1]= case when end_date[cv()-1] >= start_date[cv()] then start_date[cv()-1] else start_date[cv()] end))
group by start_date;

---For #27
with t0 as (select sno, rtrim(xmlagg(xmlelement (e, pno || ',') order by pno).extract ('//text()'),',') pnos
from    supparts group by    sno )
select rtrim (xmlagg (xmlelement (e, sno || '=') order by sno).extract ('//text()'), '=')  equal_sets
from t0 group by pnos having count(*) >=2;

mayaowu27

好东西，收藏了

newkid

xqmei 发表于 2012-10-19 07:21
---For #59
select start_date, max(end_date) end_date from  (select * from (select a.*, rownum rn fr ...

#59:
断点判断的原理不变，但你的MODEL只和上一行相比，所以有点漏洞。比如我构造下面的数据：

DELETE TIMESHEETS;

--- 第一行有一整年时间，包含了所有其他区间，合并结果应该是这一行
INSERT INTO TIMESHEETS VALUES (1,  DATE '1997-01-01', DATE '1997-12-31');
INSERT INTO TIMESHEETS VALUES (2,  DATE '1997-02-01', DATE '1997-2-1');
INSERT INTO TIMESHEETS VALUES (3,  DATE '1997-03-01', DATE '1997-3-1');

用你的MODEL返回错误结果：
select start_date, max(end_date) end_date from  (select * from (select a.*, rownum rn from Timesheets a order by start_date)
model
dimension by (rn)
measures(start_date, end_date)
rules ( start_date[rn>1]= case when end_date[cv()-1] >= start_date[cv()] then start_date[cv()-1] else start_date[cv()] end))
group by start_date;

START_DATE          END_DATE
------------------- -------------------
1997-01-01 00:00:00 1997-12-31 00:00:00
1997-03-01 00:00:00 1997-03-01 00:00:00

#27
XML很强大，但如天书一般，幸亏咱们现在有了LISTAGG, 我把它翻译过来：
SELECT LISTAGG(sno,'=') WITHIN GROUP (ORDER BY sno)
  FROM (SELECT sno,LISTAGG(pno,',') WITHIN GROUP(ORDER BY pno) pnos FROM supparts GROUP BY sno)
GROUP BY pnos
HAVING COUNT(*)>=2;

xqmei

For #59, how about this?

select start_date, max(end_date) from  ( select distinct start_date, end_date from
(select a.*, rownum rn from Timesheets a order by start_date)
model dimension by (rn) measures(start_date, end_date) rules (
end_date[rn>1]= case when end_date[cv()-1] >= start_date[cv()]  then greatest(end_date[cv()-1],end_date[cv()]) else end_date[cv()] end,
start_date[rn>1]= case when end_date[cv()-1] >= start_date[cv()]  then least(start_date[cv()-1],start_date[cv()]) else start_date[cv()] end
) ) group by start_date;

newkid

改了就对了，但是least(start_date[cv()-1],start_date[cv()])没有必要因为你已经按start_date排序，还是用原来的start_date[cv()-1] .

research_2009

学习了，多谢~

rdsxj0830

神贴收藏。