一个sql语句

len1267

浏览: 14079 次
性别:
来自: 青岛

最近访客更多访客>>

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

SQL

         , sum(nvl(case when a.d0180 is not null and a.d0170 is not null then 
                        case when to_number(a.d0180)
                                   >  to_number(a.d0170) then
                                   (to_number(substr(a.d0180, 1, 2)) * 60 + to_number(substr(a.d0180, 3, 2)))
                                      - (to_number(substr(a.d0170, 1, 2)) * 60 + to_number(substr(a.d0170, 3, 2)))
                            else 
                                   (to_number(substr(a.d0180, 1, 2) + 24) * 60 + to_number(substr(a.d0180, 3, 2)))
                                      - (to_number(substr(a.d0170, 1, 2)) * 60 + to_number(substr(a.d0170, 3, 2)))
                            end
                       else 0 end, 0)) total

    where 1 = (case when (a.typ = '1' and 0 = (
select count(rowid)                                                
from   t0060
                                               where  d0010 = sD0010
                                               and    d0020 = sD0020
                                               and    d0030 = a.d0030
                                               and    d0050 = a.d0110
                                               and    d0060 < last_day(add_months(to_date(a.d0040, 'yyyymm'), -1))))
                      or (a.typ = '2' and 0 = (select count(rowid) --
                                               from   t0040
                                               where  d0010 = sD0010
                                               and    d0020 = sD0020
                                               and    d0030 = a.d0030
                                               and    d0110 = a.d0110
                                               and    nvl(d0210, 0) > 0
                                               and    d0040 < to_char(last_day(to_date(a.d0040, 'yyyymm')),'yyyymmdd')))
                    then 1 else 0 end)

分享到：