cognos 数据源需要自关联，每个保单取最新一天数据，求助！
select *
FROM F_LIFE_LONG_POLICY_DETAIL
WHERE APPLY_CODE, POLICY_CODE, DEADLINE_ID IN
(SELECT APPLY_CODE, POLICY_CODE, MAX(DEADLINE_ID)
FROM F_LIFE_LONG_POLICY_DETAIL F_LIFE_LONG_POLICY_DETAIL
WHERE DEADLINE_ID IS NOT NULL
GROUP BY APPLY_CODE, POLICY_CODE)
FROM F_LIFE_LONG_POLICY_DETAIL
WHERE APPLY_CODE, POLICY_CODE, DEADLINE_ID IN
(SELECT APPLY_CODE, POLICY_CODE, MAX(DEADLINE_ID)
FROM F_LIFE_LONG_POLICY_DETAIL F_LIFE_LONG_POLICY_DETAIL
WHERE DEADLINE_ID IS NOT NULL
GROUP BY APPLY_CODE, POLICY_CODE)
2 同行回答
首先你得写法 如果天数很多，性能会很差很差！
比较好的是修改你的模型，比较次的，建议至少修改成如下
select *
FROM F_LIFE_LONG_POLICY_DETAIL a
WHERE exists
(
select 1 from
(SELECT APPLY_CODE, POLICY_CODE, MAX(DEADLINE_ID) DEADLINE_ID
FROM F_LIFE_LONG_POLICY_DETAIL F_LIFE_LONG_POLICY_DETAIL
WHERE DEADLINE_ID IS NOT NULL
GROUP BY APPLY_CODE, POLICY_CODE)b
where a.APPLY_CODE = b.APPLY_CODE
and a.POLICY_CODE=b.POLICY_CODE
and a.DEADLINE_ID=b.DEADLINE_ID
) 收起
比较好的是修改你的模型，比较次的，建议至少修改成如下
select *
FROM F_LIFE_LONG_POLICY_DETAIL a
WHERE exists
(
select 1 from
(SELECT APPLY_CODE, POLICY_CODE, MAX(DEADLINE_ID) DEADLINE_ID
FROM F_LIFE_LONG_POLICY_DETAIL F_LIFE_LONG_POLICY_DETAIL
WHERE DEADLINE_ID IS NOT NULL
GROUP BY APPLY_CODE, POLICY_CODE)b
where a.APPLY_CODE = b.APPLY_CODE
and a.POLICY_CODE=b.POLICY_CODE
and a.DEADLINE_ID=b.DEADLINE_ID
) 收起