plsql—收集sql中每个筛选条件的行计数

nnsrf1az 于 2021-08-01 发布在 Java

关注(0)|答案(1)|浏览(256)

我已经编写了我想使用pl/sql（oracle 11g）执行的操作：

DECLARE
    cursor date_list
    IS
        WITH year_dates (active_date) AS
        (
            SELECT to_date('1997-01-01','YYYY-MM-DD')
            FROM DUAL
            UNION ALL
            SELECT active_date + 1
            FROM year_dates
            WHERE active_date < to_date('1998-01-01','YYYY-MM-DD')
        ) SELECT active_date
                FROM year_dates;

        l_date date_list%ROWTYPE;

        l_count NUMBER;
BEGIN
    OPEN date_list;

    LOOP
        FETCH date_list INTO l_date;
        EXIT WHEN date_list%NOTFOUND;

        SELECT COUNT(emplid)
        INTO l_count
        FROM ps_job a
        WHERE effdt = (SELECT max(a1.effdt) FROM ps_job a1 WHERE a1.emplid = a.emplid AND a1.effdt < l_date.active_date)
        AND effseq = (SELECT max(a1.effseq) FROM ps_job a1 WHERE a1.emplid = a.emplid AND a1.effdt = a.effdt)
        AND deptid NOT LIKE 'XX%'
        AND hr_status = 'A'
        AND company = 'ABC';

        dbms_output.put_line(l_date.active_date || ',' || l_count);

    END LOOP;

END;
/

基本上，我想得到一整年中每个日期的行数，然后将其作为结果集返回。
有没有更好的方法用纯sql编写呢？

sql plsql

来源：https://stackoverflow.com/questions/62642652/collect-a-row-count-per-filter-condition-in-sql

1条答案

按热度按时间

bksxznpy1#

你的光标看起来没问题，不过你也可以使用 connect by level 而不是递归查询，如下所示：

select to_date('1997-01-01','YYYY-MM-DD') + (level-1) as active_date
from dual
connect by
    (to_date('1997-01-01','YYYY-MM-DD') + (level-1))
    < to_date('1998-01-01','YYYY-MM-DD')

根据数据的复杂程度，由于每个循环迭代的上下文切换，循环将导致糟糕的性能。oracle将统计表中的数据 ps_job 365次。使用 group by 一次获得所有结果集，然后在循环中输出它们可能会更好。
因为您没有提供表的表结构 ps_job ，我将提供一个使用oracle示例表的示例 EMP :

with dates as (
    select to_date('1981-01-01','YYYY-MM-DD') + (level-1) as active_date
    from dual
    connect by (to_date('1981-01-01','YYYY-MM-DD') + (level-1)) < to_date('1982-01-01','YYYY-MM-DD')
)    
select dates.active_date, count(emp.empno)
from dates
left outer join emp on (dates.active_date = hiredate)
group by dates.active_date
order by dates.active_date

现在，您可以将此查询放入隐式游标循环以将数据输出到控制台，如下所示：

begin
    for c in (
        with dates as (
            select to_date('1981-01-01','YYYY-MM-DD') + (level-1) as active_date
            from dual
            connect by (to_date('1981-01-01','YYYY-MM-DD') + (level-1)) < to_date('1982-01-01','YYYY-MM-DD')
        )    
        select dates.active_date, count(emp.empno) lcount
        from dates
        left outer join emp on (dates.active_date = hiredate)
        group by dates.active_date
        order by dates.active_date
    ) loop
        dbms_output.put_line(c.active_date || ',' || c.lcount);
    end loop;
end;

请注意，如果您不需要“空”日期，则可以将其缩短，因为您不需要首先生成日期。
如果您能提供表结构和一些示例数据 ps_job ，我将调整查询。

赞(0）回复(0）举报 2021-08-01

我来回答

plsql—收集sql中每个筛选条件的行计数

1条答案

相关问题

热门标签

最新问答