Mam zapytanie sql, aby sprawdzić nakładanie się rekordów produktów w tabeli PRODUKTY. W większości przypadków zapytanie działa poprawnie, z wyjątkiem następujących.

select * from products where 
product_reg_no = 'AL-NAPT' 
and (to_date('14-Aug-2001') BETWEEN to_date('27-Aug-2001') AND to_date('30-Aug-2001')
or to_date('31-Aug-2001') BETWEEN to_date('27-Aug-2001') AND to_date('30-Aug-2001'))

Jak sprawić, by to zapytanie wyłapało wszystkie rekordy, które częściowo lub całkowicie zachodzą na siebie?

W razie potrzeby mogę dostarczyć strukturę tabeli z przykładowymi rekordami.

Dzięki

Zaktualizuj 1

Dodałem strukturę tabeli i rekordy tutaj lub jak poniżej:

create table products
(product_reg_no varchar2(32),
 start_date date,
 end_date date);


Insert into products
   (product_reg_no, START_DATE, END_DATE)
 Values
   ('AL-NAPT', TO_DATE('08/14/2012', 'MM/DD/YYYY'), TO_DATE('08/31/2012', 'MM/DD/YYYY'));
Insert into products
   (product_reg_no, START_DATE, END_DATE)
 Values
   ('AL-NAPT', TO_DATE('08/27/2012', 'MM/DD/YYYY'), TO_DATE('08/30/2012', 'MM/DD/YYYY'));
COMMIT;

Pierwszy rekord z okresu od 14 sierpnia 2012 do 31 sierpnia 2012 pokrywa się z drugi rekord, który trwa od 27 sierpnia 2012 do 30 sierpnia 2012. Jak więc zmodyfikować moje zapytanie, aby uzyskać nakładanie się?

1
Jacob 1 październik 2012, 08:59

2 odpowiedzi

Najlepsza odpowiedź

Zobacz określanie, czy dwa zakresy dat się pokrywają.

Musisz ocenić następujące elementy lub pomniejszy wariant, używając <= zamiast <, być może:

Start1 < End2 AND Start2 < End1

Ponieważ pracujesz z jedną tabelą, musisz samodzielnie dołączyć:

SELECT p1.*, p2.*
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no != p2.product_reg_no
   AND p1.start < p2.end
   AND p2.start < p1.end;

Warunek nierówności zapewnia, że ​​nie otrzymasz rekordu sparowanego z samym sobą (chociaż warunki < również to zapewniają, ale jeśli użyjesz <=, warunek nierówności byłby dobrym pomysłem.

Spowoduje to wygenerowanie dwóch wierszy dla każdej pary produktów (jeden wiersz z ProductA jako p1 i ProductB jako p2, drugi z ProductB jako p1 i ProductA jako p2). Aby temu zapobiec, zmień != na < lub >.


A przyglądając się dokładniej przykładowym danym, może się okazać, że naprawdę interesujesz się wierszami, w których numery rejestracyjne są zgodne, a daty nakładają się. W takim przypadku możesz zignorować moje pismo o != i < lub > i mimo wszystko zastąpić warunek =.

SELECT p1.*, p2.*
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start < p2.end
   AND p2.start < p1.end;

SQL Fiddle (niezapisany) pokazuje, że to działa:

SELECT p1.product_reg_no p1_reg, p1.start_date p1_start, p1.end_date p1_end,
       p2.product_reg_no p2_reg, p2.start_date p2_start, p2.end_date p2_end
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start_date < p2.end_date
   AND p2.start_date < p1.end_date
 WHERE (p1.start_date != p2.start_date OR p1.end_date != p2.end_date);

Klauzula WHERE eliminuje wiersze, które są ze sobą połączone. Po wyeliminowaniu zduplikowanych nazw kolumn z listy SELECT, zobaczysz wszystkie dane. Dodałem wiersz:

INSERT INTO products (product_reg_no, start_date, end_date)
VALUES ('AL-NAPT', TO_DATE('08/27/2011', 'MM/DD/YYYY'), TO_DATE('08/30/2011', 'MM/DD/YYYY'));

To nie zostało wybrane — co pokazuje, że odrzuca nienakładające się wpisy.

Jeśli chcesz wyeliminować podwójne rzędy, musisz dodać kolejne fantazyjne kryterium:

SELECT p1.product_reg_no p1_reg, p1.start_date p1_start, p1.end_date p1_end,
       p2.product_reg_no p2_reg, p2.start_date p2_start, p2.end_date p2_end
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start_date < p2.end_date
   AND p2.start_date < p1.end_date
 WHERE (p1.start_date != p2.start_date OR p1.end_date != p2.end_date)
   AND (p1.start_date < p2.start_date OR
       (p1.start_date = p2.start_date AND p1.end_date < p2.end_date));
2
Community 23 maj 2017, 15:12

To dziwne pytanie. Sprawdzasz, czy 14-Aug-2001 jest między 27-Aug-2001 a 30-Aug-2001, co zawsze jest fałszywe LUB 31-Aug-2001 jest między 27-Aug-2001 a 30-Aug-2001, co również zawsze jest fałszywe. Więc twoja klauzula where zawsze będzie fałszywa.

Edytuj: dziękuję za wyjaśnienie

SQL Fiddle Demo.

select   p1.product_reg_no
       , p1.start_date p1s
       , p1.end_date   p1e
       , p2.start_date p2s
       , p2.end_date   p2e
from products p1, products p2
where p1.product_reg_no = p2.product_reg_no
  and not (    p1.end_date   < p2.start_date
           and p1.start_date > p2.end_date   );

To, czego potrzebujesz, to następujące scenariusze (1 oznacza pierwszy wiersz, 2 oznacza drugi)

1    1
 2  2

 1  1
2    2

1    1
2    2

1   1
 2   2

 1   1
2   2

Że możesz również zostać odwrócony i powiedzieć, że tego nie chcesz:

1 1
   2 2

   1 1
2 2

Założyłem, że ty też tego chcesz

1 1
  2 2

  1 1
2 2

Klauzula WHERE może być również napisana inaczej

not (    p1.end_date   < p2.start_date and p1.start_date > p2.end_date   )

Jest taki sam jak

        p1.end_date   >= p2.start_date or p1.start_date <= p2.end_date   

Myślę, że nazywało się to prawem De Morgana, kiedy miałem to w szkole wieki temu.

Prawdopodobnie musisz pomyśleć o tym, co by się stało, gdybyś miał więcej niż 2 rzędy.

2
hol 1 październik 2012, 10:54