时间戳

首页 > 解决方案 > 使用 JOIN 从两个表中选择数据后，我得到 NULL

oracle - 使用 JOIN 从两个表中选择数据后，我得到 NULL

问题描述

我正在尝试将一个表中的数据和另一个表中的数据组合起来以获取员工列表。

情况如下。在一个表中（t1 -> KPOL）我有像

SIFRA_RAD, DAT_IZD, POLICA, MAT_BROJ, MJE_SIG, UL_BROJ, OPSTINA, DAT_IZD

还有另一个表（t2->SIFRAD）我有列

SIFRA_R, IME, OSTALI_POD, DATUM_UNOSA, DATUM_PROMJENE, RADNIk_UNIO, DATUM_ISTEKA

这是我的数据的表格模式。

KPOL

  -------------------------------------------------Table KPOL------------------------------------------------------------------------------------------------
    SIFRA_RAD    DAT_IZD    POLICA      MAT_BROJ              MJE_SIG       UL_BROJ             OPSTINA              DAT_IZD

1.  "7654"      "1.1.2009"  "Z1A2B"      "06051987215444"       "USA"        "Neka Tamo 21"     "Jerricho"           "1.1.2015"
2   "4581"      "1.2.2007"  "A58547"     "65412398766666"       "DEU"        "Love Sthrase"     "Munich"             "1.2.2012"
3.  "00547"     "1.3.2013"  "65A565"     "44654789621789"       "GR"         "Huston Street"        "London"         "1.3.2012"
4.  "00214"     "1.3.2013"  "789789"     "28736428742313"       "USA"        "Next Street"      "Munchester"         "1.4.2013" 
5.  "00547"     "1.4.2013"  "789856"     "74789798564656"       "USA"        "Pro Nano 21"      "Munchester"         "1.5.2013" 
6.  "00654"     "4.4.2013"  "657989"     "78965423413213"       "USA"        "iuadsgdkj"        "Hamburg"             "6.3.2013"

-----------------------------------------------------------------------------------------------------------------------------------------------------------

SIFRAD

    -----------------------------------------Table SIFRAD-------------------------------------------------------------------------------------------------------    
 SIFRA_R        IME     OSTALI_POD      DATUM_UNOSA   DATUM_PROMJENE    RADNIK_UNIO DATUM_ISTEKA
    00214       Test123     Head of IT      31.12.2012  1.1.2013    333333123   1.5.2019
    00547       Aladin      Economic        8.5.2012    2.3.2013    32111111    31.12.2012
    00654       TestTest    Head of Management  2.2.2013    4.5.2014    11111111    1.1.2016
    0214        AaBbCc      Business        3.2.2014    6.5.2014    66666666    2.9.2019
    --------------------------------------------------------------------------------------------------------------------------------------------------------------

组合后SIFRA_RAD(table KPOL), IME(table SIFRAD), DAT_IZD(table KPOL) 它应该是这样的

SIFRA_RAD     IME        DAT_IZD
------------------------------------
"00654"     "TestTest   "1.4.2013"
"00547"     "Aladin "   "1.5.2013"
"00214"     "AaBbCc"    "4.4.2013"

它应该显示所有员工如何发布(DAT_IZD)政策2013 year

到目前为止，我所做的是以下查询：

SELECT P.SIFRA_RAD, S.IME, P.DAT_IZD
FROM AUTO.SIFRAD S
LEFT JOIN AUTO.KPOL P ON S.IME = P.SIFRA_RAD 
                      AND EXTRACT(YEAR FROM P.DAT_IZD) = 2013;

但到目前为止，我无法在表中获得任何数据。

在我使用SELECT表的语句KPOL 来选择SIFRA_RAD并DAT_IZD使用以下查询后，我得到：

select sifra_rad, dat_izd 
from kpol 
where extract(year from dat_izd) = '2013'  
order by dat_izd asc

我相信问题来自我的查询。任何人都可以帮我找出这里的问题吗？

表KPOL：

SIFRA_RAD   DAT_IZD
----------------------    
00654       1.1.2007
00654       1.1.2007
00221       1.1.2007
00698       2.1.2007
00987       3.3.2013
00214       3.4.2013
00698       3.5.2013
etc     etc

标签： oracleplsql

解决方案

你不应该加入表格SIFRA，例如

select p.sifra_rad, s.ime, p.dat_izd
from kpol p join sifrad s on p.sifra_rad = s.sifra_r
where extract (year from p.izd_dat) = 2013

您的查询加入了IMEand SIFRA，这似乎是不正确的。

[编辑，带有样本数据]

SQL> with
  2  kpol (sifra_rad, dat_izd) as
  3    (select '7654' , date '2009-01-01' from dual union all
  4     select '00547', date '2013-03-01' from dual union all
  5     select '00214', date '2013-03-01' from dual union all
  6     select '00547', date '2013-04-01' from dual union all
  7     select '00654', date '2013-04-04' from dual
  8    ),
  9  sifrad (sifra_r, ime) as
 10    (select '00214', 'Test123' from dual union all
 11     select '00547', 'Aladin'  from dual union all
 12     select '00654', 'Test'    from dual union all
 13     select '0214' , 'AbBbCc'  from dual
 14    )
 15  select p.sifra_rad,
 16         s.ime,
 17         p.dat_izd
 18  from kpol p join sifrad s on p.sifra_rad = s.sifra_r
 19  where extract (year from p.dat_izd) = 2013
 20  order by p.sifra_rad;

SIFRA IME     DAT_IZD
----- ------- ----------
00214 Test123 01.03.2013
00547 Aladin  01.04.2013
00547 Aladin  01.03.2013
00654 Test    04.04.2013

SQL>

推荐阅读