单条SQL语句实现复杂逻辑几例~~

aqcjsy

MODEL挺有意思的

hellodx

不错，收藏下来，留待后用！

fuxiaoyang13

不错，学习学习！

addm

select city,product,year,sales
from tmp1
union
select city,product, 2002 year, sum(sales) sales
from tmp1
where product='彩电' and year in (2000,2001)
group by city,product
union
select city,product,2002 year, sum(sales) sales
from tmp1
where product='微波炉' and year=2000
group by city,product
union
select city,'彩电+微波炉' product,2002 year, sum(sales) sales
from tmp1
where (product='彩电' and year in (2000,2001))or(product='微波炉' and year=2000)
group by city
order by 1,2,3
/

addm

1. 
   
2. CHENCH@lilac> l
   
3.   1  SELECT id,station,
   
4.   2                 p_start_date,
   
5.   3                 NVL(p_end_date, p_start_date) p_end_date,
   
6.   4                 start_date,
   
7.   5                 END_date,
   
8.   6                 CASE
   
9.   7                   when p_end_date is null or p_end_date <= start_date THEN
   
10.   8                    MONTHS_BETWEEN(end_date, start_date)
    
11.   9                   WHEN end_date <= p_end_date THEN
    
12. 10                    0
    
13. 11                   WHEN start_date <= p_end_date THEN
    
14. 12                    MONTHS_BETWEEN(end_date, p_end_date)
    
15. 13                 END months
    
16. 14            FROM (select id,station,
    
17. 15                         lag(start_date, 1, start_date) over(order by start_date) p_start_date,
    
18. 16                         max(end_date) over(order by start_date rows between unbounded preceding
    
19. and 1 preceding) p_end_date,
    
20. 17                         start_date,
    
21. 18                         end_date
    
22. 19                    from tmp3)
    
23. 20* order by start_date
    
24. CHENCH@lilac> /
    
25. 
    
26.         ID STATION              P_START_DATE P_END_DATE   START_DATE   END_DATE         MONTHS
    
27. ---------- -------------------- ------------ ------------ ------------ ------------ ----------
    
28.       1000 开发                 01-JAN-00    01-JAN-00    01-JAN-00    01-APR-00             3
    
29.       1000 DBA                  01-JAN-00    01-APR-00    01-FEB-00    01-MAR-00             0
    
30.       1000 兼职经理             01-FEB-00    01-APR-00    01-MAR-00    01-AUG-00             4
    
31.       1000 测试                 01-MAR-00    01-AUG-00    01-JUL-00    01-OCT-00             2
    
32.       1000 副经理               01-JUL-00    01-OCT-00    01-JAN-01    01-APR-01             3
    
33.       1000 经理                 01-JAN-01    01-APR-01    01-MAY-01    01-AUG-01             3
    
34. 
    
35. 6 rows selected.
    
36. 
    
37. Elapsed: 00:00:00.84
    
38. 
    

复制代码

最后一列为不重复工作月数，累加即为总工作月数：15个月。

[ 本帖最后由 addm 于 2008-12-5 11:26 编辑 ]

addm

select adddate,max(addvalue) over (partition by addvalue_c) addvalue
from (
select adddate,addvalue,
sum(addvalue) over (order by adddate rows between current row and unbounded following) addvalue_c
from tmp4
)
order by adddate
/

CHENCH@lilac> /

ADDDATE                ADDVALUE
-------------------- ----------
2007-03-01                 3.64
2007-03-02                 3.64
2007-03-05                 3.64
2007-03-06                 3.82
2007-03-07                 3.47
2007-03-08                 3.47
2007-03-09                 4.01
2007-03-12                 4.01
2007-03-13                 4.01
2007-03-14                 4.21
2007-03-15                 4.12

ADDDATE                ADDVALUE
-------------------- ----------
2007-03-16                    0
2007-03-17                    0

13 rows selected.

Elapsed: 00:00:01.21

newkid

原帖由 addm 于 2008-11-3 22:03 发表
CHENCH@lilac> l
  1  SELECT id,station,
  2                 p_start_date,
  3                 NVL(p_end_date, p_start_date) p_end_date,
  4                 start_date,
  5                 END_date,
  6                 CASE
  7                   when p_end_date is null or p_end_date  

这位兄弟的SQL写得真不错，但如果我加入这么一条数据就会出问题：
insert into tmp3 (ID, STATION, START_DATE, END_DATE)
values ('1000', 'QA LEAD', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-03-2000', 'dd-mm-yyyy'));

修改一下：
SELECT id,station,
       p_end_date,
       start_date,
       END_date,
       CASE
         when p_end_date is null or p_end_date <= start_date THEN
              MONTHS_BETWEEN(end_date, start_date)
         WHEN end_date <= p_end_date THEN
              0
        WHEN start_date <= p_end_date THEN
              MONTHS_BETWEEN(end_date, p_end_date)
       END months
  FROM (select id,station,
               max(end_date) over(PARTITION BY id order by start_date,end_date DESC ROWS between unbounded preceding and 1 preceding) p_end_date,
               start_date,
               end_date
          from tmp3
        )
order by start_date
/


第四题的写法很是巧妙，我钻个牛角尖，如果addvalue有正数负数就不行了, 因为addvalue_c已经不往一个方向变化

[ 本帖最后由 newkid 于 2008-11-4 05:35 编辑 ]

无语的奇迹

好难理解对于初学的我来说 呵呵

addm

原帖由 newkid 于 2008-11-4 05:29 发表


这位兄弟的SQL写得真不错，但如果我加入这么一条数据就会出问题：
insert into tmp3 (ID, STATION, START_DATE, END_DATE)
values ('1000', 'QA LEAD', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-03-2000', 'dd-mm-yyyy'));

我试了，添加你的数据，没有错啊。

SELECT id,
       station,
       p_start_date,
       NVL(p_end_date, p_start_date) p_end_date,
       start_date,
       END_date,
       CASE
         when p_end_date is null or p_end_date <= start_date THEN
          MONTHS_BETWEEN(end_date, start_date)
         WHEN end_date <= p_end_date THEN
          0
         WHEN start_date <= p_end_date THEN
          MONTHS_BETWEEN(end_date, p_end_date)
       END months
  FROM (select id,
               station,
               lag(start_date, 1, start_date) over(order by start_date) p_start_date,
               max(end_date) over(order by start_date rows between unbounded preceding and 1 preceding) p_end_date,
               start_date,
               end_date
          from tmp3)
order by start_date;

        ID STATION              P_START_DATE P_END_DATE   START_DATE   END_DATE         MONTHS
---------- -------------------- ------------ ------------ ------------ ------------ ----------
      1000 QA LEAD              01-JAN-00    01-JAN-00    01-JAN-00    01-MAR-00             2
      1000 开发                 01-JAN-00    01-MAR-00    01-JAN-00    01-APR-00             1
      1000 DBA                  01-JAN-00    01-APR-00    01-FEB-00    01-MAR-00             0
      1000 兼职经理             01-FEB-00    01-APR-00    01-MAR-00    01-AUG-00             4
      1000 测试                 01-MAR-00    01-AUG-00    01-JUL-00    01-OCT-00             2
      1000 副经理               01-JUL-00    01-OCT-00    01-JAN-01    01-APR-01             3
      1000 经理                 01-JAN-01    01-APR-01    01-MAY-01    01-AUG-01             3

7 rows selected.

Elapsed: 00:00:00.20

还是15个月。不知你说的错误，是什么？

========
题外话：另外，看样子这位老兄也是oracle业内资深人士。小弟我以前做c++游戏开发，精通数据结构、常用算法、c++程序设计。（本来也不敢妄称精通，不过在上海做了几家业内小有名气的游戏公司，发现所谓的高手也不过如此，遂抖胆自诩精通。另外，对目前游戏开发混乱的现状，略有失望，决心转行oracle数据库。）今年才转oracle数据库，主攻oracle开发，对oracle基于成本的优化，有较多研究。目前在一家中型民营企业做小头目，带七、八个新人。
小弟想找家大型的专做oracle相关开发的公司，一直未能如愿。主要是oracle相关职位需求较少，一般招的也是初级职位。也不知哪家大公司招中、高级的oracle开发职位。
这位老兄，如果知道相关职位信息，帮忙引见一下。多谢了啊！

ysl_1981

总结的好