当有多个匹配行时，控制SQL连接条件

发布于 2025-01-29 22:30:37 字数 589 浏览 2 评论 0 原文

我有一个以上的匹配行。我如何控制加入？特别是，如示例所示，当有多个匹配行时，我不想获得任何结果并维护我的输入。

在图片中，我显示了所需的结果。由于指导匹配，还正确获得了带有绿色的行，并且还没有正确获得带有红色的行，因为有多个匹配。

Select t2.id, t1.Insurance_num, t1Name, t1.Surname
From Table_1 t1
left join Table_2 t2
on t1.Insurance_num=t2.Insurance_num

我正在使用 hive ，但我猜是这个答案的答案问题应该是通用的问题。

原文

I have a join with more than one matching row. How can I have more control over the join? In particular, as shown in the example, when there is more than one matching row, I don't want to obtain any result and maintain my input.

In the picture, I am showing the desired result. The row with the green color is correctly obtained because of directing matching and the row with the red color is also correctly not obtained, because there are more than one matching.

Select t2.id, t1.Insurance_num, t1Name, t1.Surname
From Table_1 t1
left join Table_2 t2
on t1.Insurance_num=t2.Insurance_num

I am using Hive, but I guess the answer to this question should be a generic one.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

内心激荡 2025-02-05 22:30:37

您可以尝试一下...我不确定这是否正是您所需的。

如果没有左限制的结合如果没有匹配的Insurance_num，则将返回0，或，则是是匹配的Insurance_num，但计数是 /em>大于1，它获得了案例查询的 else 条件。

只有是在预先疑问中和中的匹配，该保险_num的记录才从该预处理查询中获取ID。

select
      case when coalesce( PreSum.NumEntries, 0 ) = 1
           then cast( PreSum.LowID as varchar )
           else 'N.D' end as IDorNot,
      t1.Insurance_num,
      t1.Name,
      t1.Surname
   from
      table_1 t1
         LEFT JOIN
         ( select
                 t2.Insurance_Num,
                 min( t2.id ) as LowID,
                 count(*) as NumEntries
              from
                 Table_2 t2
              group by
                 t2.Insurance_Num ) PreSum
            on t1.insurance_Num = PreSum.Insurance_Num

You might try this... I am not sure if it is exactly what you need.

If the coalesce with left-join would be null if no matching Insurance_Num, thus returning 0, or, there is a matching insurance_num, but the count is other than 1, it gets the ELSE condition of the CASE query.

Only if there is a match in the pre-query, and there is one record for that insurance_num does it get the ID from that pre-aggregation query.

select
      case when coalesce( PreSum.NumEntries, 0 ) = 1
           then cast( PreSum.LowID as varchar )
           else 'N.D' end as IDorNot,
      t1.Insurance_num,
      t1.Name,
      t1.Surname
   from
      table_1 t1
         LEFT JOIN
         ( select
                 t2.Insurance_Num,
                 min( t2.id ) as LowID,
                 count(*) as NumEntries
              from
                 Table_2 t2
              group by
                 t2.Insurance_Num ) PreSum
            on t1.insurance_Num = PreSum.Insurance_Num

回复收藏 0 原文

~没有更多了~