我有表 T1:
TICKETID
1
2
3
我有T2桌
ID TICKETID DESCRIPTION
1 1 First
2 2 Second
3 2 Automatic
我想加入这些表(SELECT 必须从 T1 表开始)并在 Automatic 值之前提取 Last description 值(例如,因为 Automatic 值用于 TICKETID 2,它意味着 MAXIMUM ID(即 3)之前的最后一个值,这意味着应提取 TICKETID 2 的值 Second)。对于 TICKETID 1 和 TICKETID,由于自动值不存在,它将具有空值。
所以最后的结果应该是
1 null
2 Second
3 null
我试过这样:
select t11.ticketid, t21.description
from t1 as t11
left outer join t2 as t21 on t21.ticketid=t11.ticketid
left outer join
(
select t12.ticketid, max(t22.id) as maxwl from t2 as t22
inner join t1 as t12 on t12.ticketid=t22.ticketid and t22.ticketid in
(select t23.ticketid from t2 as t23 inner join
t1 as t13 on t13.ticketid=t23.ticketid and t23.description='Automatic')
and t22.description!='Automatic'
group by t12.ticketid
) as t24 on t22.maxwl=t21.id
然后我得到了结果:
1 First
2 Second
3 null
这是错误的,因为 TICKETID 1 的值必须为 Null,因为它没有值为 Automatic 的描述。
应该是这样的。您只需要在加入前检查两个条件。但这并不是最佳选择,因为您阅读了同一个表 3
select t11.ticketid, t21.description
from t1 as t11
left outer join
( select *
from t2 as t22
where exists
( select max(t23.id) as maxid, t23.ticketid
from t2 as t23
where t23.description <> 'Automatic'
and t22.ticketid = t23.ticketid
and exists
( select max(t24.id) as max_aut_id, t24.ticketid
from t2 as t24
where t24.description = 'Automatic'
and t23.ticketid = t24.ticketid
group by t24.ticketid
having t23.id < max_aut_id)
group by t23.ticketid
having maxid = t22.id)
) as t21
on t21.ticketid = t11.ticketid
更新:已修复,因此它将是自动之前的最新版本,而不仅仅是最新的非自动版本。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句