好久没用group by了，今日看了个sql

好久没用group by了，今天看了个sql
这个SQL取出所有没有address，且有name的site。注意里面SQL的执行顺序。

SELECT
    count(*)
FROM
    (
    SELECT
        pcp.roleplayer,
        max(log_del_timestamp)
    FROM
        xxxx.pcp
    GROUP BY
        roleplayer
    HAVING
        max(log_del_timestamp) != '9999-12-31 00:00:00'
    )
    s1,
    xxxx.pcp p,
    xxxx.site site,
    xxxx.name pname
WHERE
    site.site_id                = pname.roleplayer
    and site.log_del_timestamp = '9999-12-31 00:00:00'
    and pname.log_del_timestamp = '9999-12-31 00:00:00'
    and prty_name_type_cd       = 'BU'
    and s1.roleplayer           = site.site_id
    and s1.roleplayer           = p.roleplayer
    and p.log_del_system_id     = 30007
    and p.roleplayer_type       = '2011'
    and p.contactpoint_type     = '2306'
    and p.PRI_FLG               = 1
WITH
    ur;

下面这个SQL是不等价与上面的,先用where条件筛选，结果集再用having来过滤。这样如果有一个site存在两条记录，9999的一条被where过滤掉了，另一条不是9999的就会留下来（having过滤不掉了），这并不是上面SQL想要的数据。

SELECT
    *
FROM
    (
    SELECT
        roleplayer,
        max(log_del_timestamp)
    FROM
        xxxx.pcp
    WHERE
        log_del_system_id     = 30007
        and role_type   = '2011'
        and contactpoint_type = '2306'
    GROUP BY
        roleplayer
    HAVING
        max(log_del_timestamp) != '9999-12-31 00:00:00'
    )
    s1,
    xxxx.site site,
    xxxx.name pname
WHERE
    site.site_id                = pname.role
    and site.log_timestamp = '9999-12-31 00:00:00'
    and pname.log_timestamp = '9999-12-31 00:00:00'
    and prty_name_type     = 'BU'
    and s1.role           = site._id
FETCH
    first 10 rows only
WITH
    ur;

下面的SQL可以取出所有仅次于9999的最大删除时间。先用where过滤掉9999的再做group。

select roleplayer, max(log_del_timestamp)
from xxxx.prtyrol_cntct_pnt where log_timestamp !='9999-12-31 00:00:00' group by roleplayer

好久没用group by了，今日看了个sql

相关推荐