查询忽略重复/空记录

2021-12-30 00:00:00 sql oracle11g oracle

以下是我们需要加入以获取数据的 3 个表

Below are the 3 tables which we need to join to get the data

 TableA         TableB               TableC
    AId          BId     Name      CId       DeclareDate  value
    1            1       abc       1         September      11
    2            2       def       1         October        12
    3            3       xyz       1         November       13
    4            4       pqr       1         December       14
    5            5       ghi       2         September      15
                                   3         October        16
                                   4         August         17
                                   5         October        18
                                   5         December       19

从 TableC 中，我们有相同的基金，CId 1 在所有 4 个月内都在申报，在这种情况下，我只需要 12 月的行，如果基金不在 12 月申报，那么我需要获得该 C id 的空值.所以输出表应该如下所示.

From TableC we have same fund with CId 1 is declaring in all the 4 months, in that case I need only the december row and if the fund is not declaring in december then I need to get the null valure for that C id. So the output table should look like below.

输出表

   AId    BId  name     DeclareDate   value
    1      1    abc     December      14
    2      2    def     null          null
    3      3    xyz     null          null
    4      4    pqr     null          null
    5      5    ghi     december      19

所以最后，如果 12 月没有声明日期，则返回 null，如果有多个声明日期，则只返回 12 月记录.

So finally if there are no declared dates in december then return null, if there are multiple declare dates then return only the december record.

请提出建议.

推荐答案

使用左连接到表C

  select a.*,b.*, c.DeclareDate, c.Value from TableA a
   inner join TableB b on  a.AI =b.BId
  left join 
  (select * from TableC where DeclareDate='December') c
   on b.BId = c.CId

输出

相关文章