好久没用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
