Wyszukanie wielozapytaniowe

pge · 14 Grudzień 2010 13:27

Jest tabela:

ID Imię

1 null

1 Paweł

2 Paweł

3 Izabela

4 Jarek

4 Magda

4 null

5 Rafał

5 Null

Wynika ma być taki

ID Imię

1 null

1 Paweł

2 Paweł

– Wyświetla rekordy z ID 1 i 2, bo takie samo imię posiada dwa ID

4 Jarek

4 Magda

4 null

– Wyświetla rekordy z ID 4, bo wykluczając imię null, jedno ID zawiera więcej niż jedno imię.

Czy to jest możliwe wyświetlenie takich wyników?

matzu · 14 Grudzień 2010 14:22

SELECT t1.ID, t1.Imie

FROM Tabela As t1,

(

SELECT DISTINCT t1.ID As ID

FROM Tabela As t1, Tabela As t2

Where t1.ID <> t2.ID And t1.Imie = t2.Imie And t1.Imie is not null And t2.Imie is not null

UNION

SELECT DISTINCT t1.ID As ID

FROM Tabela As t1, Tabela As t2

Where t1.ID = t2.ID And t1.Imie <> t2.Imie And t1.Imie is not null And t2.Imie is not null

) As temp

WHERE t1.ID = temp.ID

pge · 15 Grudzień 2010 06:32

Czapki z głów. Tak się zdobywa uznanie.

Szybko i skutecznie.

Wielkie dzięki.

Pozdrawiam

matzu · 15 Grudzień 2010 09:11

Nie ma za co.

pge · 17 Listopad 2011 10:22

Następny rebus

Są trzy tabele

ID Imię Nazwisko

1 Jan Kowalski

2 Jan Nowak

3 Edward Wiśniewski

4 Agnieszka Kowalska

ID Okres Kwota

1 01.2008 30 zł

1 04.2008 30 zł

2 05.2008 30 zł

3 08.2008 0 zł

4 09.2008 30 zł

ID Od Do

1 2008-01-31 2008-03-01

4 2008-08-01 2009-09-03

Wynik ma być taki:

Imię Nazwisko Okres Kwota

Jan Kowalski 04.2008 30 zł – (ponieważ nie ma żadnego okresu w tabeli 3, który choćby jeden dzień zawierał się w miesiącu kwietniu 2008, a za ten miesiąc dostał pieniądze)

Jan Nowak 05.2008 30 zł – (ponieważ w tabeli 3 nie ma żadnego okresu dla tej osoby a dostał w miesiącu maj 2008 pieniądze)

matzu · 17 Listopad 2011 15:22

Jaki typ danych przechowują kolumny Okres, Od i Do? Czy to są zwykłe łańcuchy znaków (char/varchar/itp.)?

pge · 17 Listopad 2011 16:18

Poprawiona tabela 2

ID | Okres_pocz | Kwota

1 | 2008-01-01 | 30.00

1 | 2008-04-01 | 30.00

2 | 2008-05-01 | 30.00

3 | 2008-08-01 | 30.00

4 | 2008-09-01 | 30.00

ID | Od | Do

1 | 2008-01-31 | 2008-03-01

4 | 2008-08-01 | 2009-09-03

Pole okres - jaki to typ danych? Data
Pole Od i Do - jaki to typ danych? Data
Pole Kwota - jaki to typ danych? Walutowy

matzu · 17 Listopad 2011 19:05

Próbowałem wykombinować rozwiązanie, które będzie na 100% niezależne od rodzaju używanego SZBD, ale niestety mi się to nie udało. Poniżej zamieszczam kod, który działa (a przynajmniej tak mi się wydaje, trzeba potestować) na MS SQL Server 2008 R2 (jeśli używasz innego SZBD to musisz sam sprawdzić, czy wszystko jest OK - daj proszę znać). Kod składa się z dwóch elementów … funkcji SQL (najpierw musisz utworzyć funkcję) oraz zapytania SQL:

/******Object: UserDefinedFunction [dbo].[GetDatesFromRangeAsTable] Script Date: 11/17/2011 20:04:28******/

SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


CREATE FUNCTION [dbo].[GetDatesFromRangeAsTable] (

	@DateFrom Date,

	@DateTo Date

)

RETURNS @Result TABLE(

	Value Date

)

AS 

BEGIN

	-- http://msdn.microsoft.com/en-us/library/ms190766.aspx

	-- http://msdn.microsoft.com/en-us/library/ms186819.aspx


	WITH temp(Value)

	AS

	( 

		SELECT @DateFrom

		UNION ALL

		SELECT DATEADD(DAY, 1, temp.Value) 

		FROM temp 

		WHERE temp.Value < @DateTo

	)


	INSERT @Result

	SELECT Value From temp


	RETURN 

END


GO

SELECT t1.ID, t1.Imię, t1.Nazwisko, MONTH(temp.Okres) AS Miesiac, YEAR(temp.Okres) AS Rok, temp.Kwota

FROM Table1 AS t1, (

	SELECT t2.ID, t2.Okres, t2.Kwota

	FROM Table2 as t2 LEFT JOIN Table3 as t3 ON t2.ID = t3.ID

	WHERE t3.ID IS NULL

	UNION ALL

	SELECT t2.ID, t2.Okres, t2.Kwota

	FROM Table2 as t2, Table3 as t3

	WHERE t2.ID = t3.ID AND NOT EXISTS (

		SELECT temp.Value

		FROM GetDatesFromRangeAsTable(t2.Okres, DATEADD(MONTH, 1, t2.Okres)) As temp

		WHERE temp.Value BETWEEN t3.Od AND t3.Do

	)

) as temp

WHERE t1.ID = temp.ID

and temp.Kwota > 0;

Jeśli akurat korzystasz z MS SQL Server i masz dużo rekordów w tabeli, to daj znać jak z wydajnością tego zapytania, tzn. ile będzie trwało przetwarzanie.

pge · 17 Listopad 2011 19:54

Dzięki za szybką odpowiedz.

Zależało mi aby był sam select. Ale na podstawie twojego pytania chyba mam wizję jak wyznaczyć czy miesiąc nachodzi na wyznaczony okres.

Jutro po testuję i podam wypociny.

Ogólnie zapytanie będzie na PostgreSQL wykonywane.

matzu · 17 Listopad 2011 19:57

Ta funkcja jest po to, aby wyciągnąć z zakresu dat, wszystkie dni (czyli np. wszystkie dni danego miesiąca). Być może w PostgreSQL jest już wbudowana funkcja, która takie coś wykonuje? A być może wykombinujesz inne rozwiązanie. Daj znać, bo nie ukrywam, że sam jestem ciekaw innych rozwiązań (problem wcale nie jest banalny).

Polecenia, które użyłem (takie nietypowe) to With, DateAdd, Month, Year. Te dwa ostatnie użyłem tak trochę dodatkowo, nie są wymagane, żeby całość działała. Z tego co widziałem PostgreSQL obsługuje składnię With (będziesz musiał dodać do With słówko RECURSIVE - http://www.postgresql.org/docs/8.4/stat … -with.html), ale nie obsługuje DateAdd. Będziesz musiał znaleźć jakiś ekwiwalent (na bank jest).

pge · 21 Listopad 2011 14:00

((data_okr)::date + interval ‘1 month’ - interval ‘1 day’)::date as data_okr_end

Niby wyznacza datę końca miesiąca ale jak to powiązać.

– Dodane 21.11.2011 (Pn) 20:16 –

Ja mam tą wersję:

http://get.enterprisedb.com/postgresql/ … indows.exe

Ale jak na Win7 to chyba ta będzie lepsza:

http://www.dobreprogramy.pl/PostgreSQL, … 12929.html

Ta wersja jest od razu z pgAdmin III czyli można wydawać zapytania oraz analizować plan zapytania prezentowany w postaci klasycznej lub graficznej.

matzu · 21 Listopad 2011 23:25

2h walczyłem, żeby zainstalować wersję 64-bitową PostgreSQL 9.1.1 na Win7 64bit, a i tak mi się to nie udało. Wersja 32-bitowa PostgreSQL 9.1.1 na tym samym systemie zainstalowała się bez problemu -,-. Zobacz, czy ten kod SQL ruszy na Twojej wersji serwera:

CREATE OR REPLACE FUNCTION getdatesfromrange(date, date)

  RETURNS SETOF date AS

$BODY$

DECLARE i date;

BEGIN

-- http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL

-- http://www.postgresql.org/docs/8.4/static/queries-with.html


FOR i IN

	WITH RECURSIVE t(n) AS (

	    SELECT $1

	  UNION ALL

	    SELECT n + 1 FROM t WHERE n < $2

	)

	SELECT n FROM t

LOOP

RETURN NEXT i;

END LOOP;


END; 

$BODY$

LANGUAGE 'plpgsql';

SELECT t1.Imie, t1.Nazwisko, temp.OkresOd, temp.Kwota

FROM Table1 AS t1, (

   SELECT t2.ID, t2.OkresOd, t2.Kwota

   FROM table2 as t2 LEFT JOIN table3 as t3 ON t2.ID = t3.ID

   WHERE t3.ID IS NULL

   UNION ALL

   SELECT t2.ID, t2.OkresOd, t2.Kwota

   FROM Table2 as t2, Table3 as t3

   WHERE t2.ID = t3.ID AND NOT EXISTS (

      SELECT v

      FROM getDatesFromRange(t2.OkresOd, CAST((t2.OkresOd + INTERVAL '1 month' - INTERVAL '1 day') AS date)) As v

      WHERE v BETWEEN t3.OkresOd AND t3.OkresDo

   )

) as temp

WHERE t1.ID = temp.ID AND temp.Kwota > 0

ORDER BY temp.OkresOd;

Ten kod tabel, który podałeś musiałem trochę zmodyfikować (nazwy kolumn). Nie wiem z jakiego powodu u Ciebie np. nazwa kolumny ID jest w cudzysłowie?

pge · 22 Listopad 2011 07:46

Nie chce przyjąć funkcji

– Dodane 22.11.2011 (Wt) 9:49 –

…P…K

…|#####################|

…|xxxxxxxxxxxxxxxx| - P jest między data_od i data_do

…|xxxxxxxxxxxxx| - K jest między data_od i data_do

…|xxxxxxxxxxxx| - data_od jest między P i K

Z moich spostrzeżeń wynika, że wystarczy sprawdzić 3 warunki zamiast funkcji.

– Dodane 22.11.2011 (Wt) 13:43 –

SELECT t2.id_osoby, t2.data_okr, t2.kwota, t3.data_od, t3.data_do 

       FROM table2 as t2, table3 as t3

       WHERE t2.id_osoby = t3.id_osoby AND NOT EXISTS (

          SELECT temp.data_okr

          FROM table2 as temp

          WHERE (t2.data_okr BETWEEN t3.data_od AND t3.data_do 

          OR ((t2.data_okr)::date + interval '1 month' - interval '1 day') BETWEEN t3.data_od AND t3.data_do 

          OR t3.data_od BETWEEN t2.data_okr AND ((t2.data_okr)::date + interval '1 month' - interval '1 day'))

          )

/*


   SELECT t1.Imie, t1.Nazwisko, temp.data_okr, temp.kwota

    FROM table1 AS t1, (

       SELECT t2.id_osoby, t2.data_okr, t2.kwota

       FROM table2 as t2 LEFT JOIN table3 as t3 ON t2.id_osoby = t3.id_osoby

       WHERE t3.id_osoby IS NULL

       UNION ALL

       SELECT t2.id_osoby, t2.data_okr, t2.kwota

       FROM table2 as t2, table3 as t3

       WHERE t2.id_osoby = t3.id_osoby AND NOT EXISTS (

          SELECT temp.data_okr

          FROM table2 as temp

          WHERE (t2.data_okr BETWEEN t3.data_od AND t3.data_do 

          OR ((t2.data_okr)::date + interval '1 month' - interval '1 day') BETWEEN t3.data_od AND t3.data_do 

          OR t3.data_od BETWEEN t2.data_okr AND ((t2.data_okr)::date + interval '1 month' - interval '1 day'))

          )

    ) as temp

    WHERE t1.id_osoby = temp.id_osoby

and temp.kwota > 0




   SELECT t1.Imie, t1.Nazwisko, t2.data_okr, t2.kwota, t3.data_od, t3.data_do

    FROM table1 AS t1

     JOIN table2 as t2 ON t1.id_osoby = t2.id_osoby

     Left JOIN table3 as t3 ON t3.id_osoby = t2.id_osoby


"Jan ";"Kowalski ";"2008-01-01";30.00;"2008-01-31";"2008-03-01"

"Jan ";"Kowalski ";"2008-01-01";30.00;"2008-05-05";"2008-05-10"

"Jan ";"Kowalski ";"2008-02-01";30.00;"2008-01-31";"2008-03-01"

"Jan ";"Kowalski ";"2008-02-01";30.00;"2008-05-05";"2008-05-10"

"Jan ";"Kowalski ";"2008-03-01";30.00;"2008-01-31";"2008-03-01"

"Jan ";"Kowalski ";"2008-03-01";30.00;"2008-05-05";"2008-05-10"

"Jan ";"Kowalski ";"2008-04-01";30.00;"2008-01-31";"2008-03-01"

"Jan ";"Kowalski ";"2008-04-01";30.00;"2008-05-05";"2008-05-10"

"Jan ";"Kowalski ";"2008-05-01";30.00;"2008-01-31";"2008-03-01"

"Jan ";"Kowalski ";"2008-05-01";30.00;"2008-05-05";"2008-05-10"

"Jan ";"Nowak ";"2008-05-01";30.00;"";""

"Edward ";"Wiśniewski ";"2008-08-01";0.00;"";""

"Agnieszka ";"Kowalska ";"2008-09-01";30.00;"2008-08-01";"2009-09-03"




DROP TABLE std.Table1;

DROP TABLE std.Table2;

DROP TABLE std.Table3;


CREATE TABLE std.Table1

(

  id_osoby integer,

  imie character(15),

  nazwisko character(15)

)

WITH (OIDS=FALSE);

ALTER TABLE std.Table1 OWNER TO only_select;


CREATE TABLE std.Table2

(

  id_osoby integer,

  data_okr date,

  kwota numeric(9,2)

)

WITH (OIDS=FALSE);

ALTER TABLE std.Table2 OWNER TO only_select;


CREATE TABLE std.Table3

(

  id_osoby integer,

  data_od date,

  data_do date

)

WITH (OIDS=FALSE);

ALTER TABLE std.Table3 OWNER TO only_select;

--------

INSERT INTO Table1 VALUES ('1', 'Jan', 'Kowalski');

INSERT INTO Table1 VALUES ('2', 'Jan', 'Nowak');

INSERT INTO Table1 VALUES ('3', 'Edward', 'Wiśniewski');

INSERT INTO Table1 VALUES ('4', 'Agnieszka', 'Kowalska');

--------

INSERT INTO Table2 VALUES ('1', '2008-01-01', 30.00);

INSERT INTO Table2 VALUES ('1', '2008-02-01', 30.00);

INSERT INTO Table2 VALUES ('1', '2008-03-01', 30.00);

INSERT INTO Table2 VALUES ('1', '2008-04-01', 30.00);

INSERT INTO Table2 VALUES ('1', '2008-05-01', 30.00);


INSERT INTO Table2 VALUES ('2', '2008-05-01', 30.00);

INSERT INTO Table2 VALUES ('3', '2008-08-01', 0.00);

INSERT INTO Table2 VALUES ('4', '2008-09-01', 30.00);

--------

INSERT INTO Table3 VALUES ('1', '2008-01-31', '2008-03-01');

INSERT INTO Table3 VALUES ('1', '2008-05-05', '2008-05-10');

INSERT INTO Table3 VALUES ('4', '2008-08-01', '2009-09-03');


*/

Jest problem jak w tabeli 3 są dwa okresy dla jednej osoby.

matzu · 22 Listopad 2011 14:12

Na tym WITH RECURSIVE się wykłada, bo to polecenie pojawiło się dopiero w wersji 8.4 serwera (dowiedziałem się o tym dopiero, gdy wkleiłeś komunikat błędu).

Świetny sposób. To znacznie wszystko upraszcza Z uwzględnieniem tych Twoich spostrzeżeń to zapytanie może wyglądać następująco (użyłem tych nowych nazw kolumn):

SELECT t1.imie, t1.nazwisko, temp.data_okr, temp.kwota

FROM table1 AS t1, (

SELECT t2.id_osoby, t2.data_okr, t2.kwota

FROM table2 AS t2 LEFT JOIN table3 AS t3 ON t2.id_osoby = t3.id_osoby

WHERE t3.id_osoby IS NULL

UNION ALL

SELECT t2.id_osoby, t2.data_okr, t2.kwota

FROM table2 AS t2

WHERE NOT EXISTS (

   SELECT *

   FROM table3 AS t3

   WHERE t2.id_osoby = t3.id_osoby AND 

      (t2.data_okr BETWEEN t3.data_od AND t3.data_do)

      OR (CAST((t2.data_okr + INTERVAL '1 month' - INTERVAL '1 day') AS date) BETWEEN t3.data_od AND t3.data_do)

      OR (t3.data_od BETWEEN t2.data_okr AND CAST((t2.data_okr + INTERVAL '1 month' - INTERVAL '1 day') AS date))

   )

) AS temp

WHERE t1.id_osoby = temp.id_osoby AND temp.kwota > 0

ORDER BY temp.data_okr;

Żeby to działało z tym nowym warunkiem trzeba było trochę przebudować zapytanie. Daj znać, czy teraz jest OK, czy jednak wciąż jest coś nie tak.

pge · 22 Listopad 2011 15:44

=D> =D> i jeszcze raz brawo

Jestem pod wielkim wrażeniem. Człowiek tyle czasu traci i już po omacku zgaduje składnię a Ty parę minut i 100% skuteczność.

Jutro przetestuję na pełnej bazie, ale widzę, że na pewno zadziała.

Wielki dzięki.

PS

Muszę u Ciebie wykupić trochę lekcji.

matzu · 22 Listopad 2011 17:05

Parę minut to na pewno nie, trochę więcej. W każdym bądź razie zasługi rozkładają się 50/50. Gdyby nie Twój sposób, to to moje zapytanie, byłoby zapewne za wolne, a tak myślę, że nie będzie problemów z wydajnością (choć to się okaże ).

pge · 23 Listopad 2011 07:17

t1 = 17 466 rekordów

t2 = 42 677 rekordów

t3 = 315 438 rekordów

wynik 160 rekordów w 11 907 ms

Błyskawica