只有当第一个表中的记录数量大于或等于第二个表中的记录数时，我才能使左联接工作？
只有当第一个表中的记录数量大于或等于第二个表中的记录数时，我才能使左联接工作？
提问于 2019-10-06 13:50:20
我有两个表，我想在它们之间做左联接，，但，我希望只有在第一个表中包含相同值的连接列的记录计数大于或等于第二个表中包含相同值的联接列的记录数时，才会发生左联接。
我所尝试的：
首先，我对每一组进行记录计数。
然后将条件放入过滤器(a.cnt >= b.cnt)。
这是代码：
insert into work.discount_tmp
select SubsID, MSISDN, EppAcc, User_Name, Bill_Cycle, Tariff, Pack, Discount_Qual_ID,
Discount_ID, Qualification_Type, Discount_Desc, Sequence, a.GroupID, RuleID,
dib_band_id, dib_band_end, dib_charge_ref, DIB_DIS0, dib_disc_type, dib_limit_disc,
DIB_MAX_, cd_class_type, ClassID, Class, dgh_group_id, dgh_inclusion_from,
dgh_inclusion_to, 20191003
from (
(
select *,
row_number() over (partition by GroupID order by Discount_ID) as seqnum,
COUNT(*) over (partition by GroupID order by GroupID) as cnt
from work.disc_band
where tbl_dt = 20191003
order by Discount_ID
) a
left join (
select *,
row_number() over (
partition by GroupID
order by cd_class_type, try(cast(coalesce(classid,'0') as bigint))
) as seqnum,
count(*) over (partition by GroupID order by GroupID) as cnt
from work.alltable1
where tbl_dt = 20191003
) b on a.GroupID = b.GroupID and a.cnt >= b.cnt and a.seqnum = b.seqnum
); 但是我的尝试不工作，因为连接先完成，然后是条件(因此第二个表中的cnt值在join完成后不会保持不变)
知道该怎么做吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-10-06 14:28:02
您希望在FROM子句中编写引用中性“种子表”的查询。然后，您可以从这两个表中计算行数，并将它们进行比较。就像这样：
insert into work.discount_tmp
select SubsID, MSISDN, EppAcc, User_Name, Bill_Cycle, Tariff, Pack, Discount_Qual_ID,
Discount_ID, Qualification_Type, Discount_Desc, Sequence, a.GroupID, RuleID,
dib_band_id, dib_band_end, dib_charge_ref, DIB_DIS0, dib_disc_type, dib_limit_disc,
DIB_MAX_, cd_class_type, ClassID, Class, dgh_group_id, dgh_inclusion_from,
dgh_inclusion_to, 20191003
from (
select a.*, b.*,
FROM (SELECT 1 AS DummyCol) AS dt -- "seed" table; not sure the Presto equivalent
CROSS JOIN ( -- Get all qualifying rows from "disc_band"
SELECT *, count(*) OVER(PARTITION BY groupid) AS RowCount
FROM work.disc_band
WHERE tbl_dt = 20191003
) a
LEFT JOIN ( -- Get all qualifying rows from "alltable1"
SELECT *, count(*) OVER(PARTITION BY groupid) AS RowCount
FROM work.alltable1
WHERE tbl_dt = 20191003
) b ON a.groupid = b.groupid AND a.RowCount >= b.RowCount
) src我不确定seqnum逻辑如何，所以如果需要，可以将它添加回。
我还没有对它进行测试，您可能需要修改语法才能让它与Presto协同工作。试试看让我知道。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58257888
复制相关文章
相似问题
腾讯云开发者
