Diskuze: Složitý výběr

V předchozím kvízu, Online test znalostí SQL a databází, jsme si ověřili nabyté zkušenosti z kurzu.

Aktivity

tomas

Člen

tomas:28.8.2018 16:26

Ahoj všem. Mám následující tabulku:

Id	device_id	empployee_id	protocol_number	transfer	date
265	351	48	106	1
266	134	48	106	1
281	189	48	119	1
282	372	48	191	1
283	132	48	191	1
284	421	48	273	1
285	132	48	273	1
286	132	48	273	1
287	142	48	273	1
288	189	48	350	0
289	134	48	351	0
290	372	48	356	0
291	132	48	357	0

Datum neuvádím, nebude potřeba. Jedná se o tabulku předaných zařízení zaměstnanci. Tohle je konkrétně výpis jednoho z nich. Sloupec transfér a hodnota 1 znamená, že zařízení bylo předáno, 0 že bylo vráceno. V tomto případě tedy převzal 9 zařízení a 4 z nich vrátil. Ve výsledné tabulce jsou ještě další sloupce, ale pro výběr jsou nepodstatné. Nic méně jsou v té proceduře vidět.

Zkusil jsem: moje procedura

create table #table (
                id      int,
                device_id       int,
                employee_id     int,
                date    datetime,
                state   varchar(40),
                protocol_number int,
                transfer        bit,
                name    varchar(30),
                serial_number   varchar(20),
                inventory_number        varchar(6));

        insert into #table (id, device_id, employee_id, date, state, protocol_number, transfer, name, serial_number, inventory_number)

        SELECT
       [Assigned_device].[id]
      ,[Assigned_device].[device_id]
      ,[Assigned_device].[employee_id]
          ,[Assigned_device].[date]
          ,[Assigned_device].[state]
          ,[Assigned_device].[protocol_number]
          ,[Assigned_device].[transfer]
          ,[Device].[name]
          ,Device.serial_number
          ,Device.inventory_number

        FROM [Device]
        LEFT Join Assigned_device on device.id = Assigned_device.device_id
        Where Assigned_device.employee_id = 48

        select *
        from #table as list1
        where not exists (select id from #table as list2 where list2.device_id = list1.device_id AND list2.protocol_number > list1.protocol_number) AND transfer = 1

        drop table #table

Chci docílit: Já bych potřeboval vytvořit dotaz, který by mě vrátil všechny aktuáně zapůjčené zařízení. V tomto případě má procedura mě přestává fungovat. Protože má jedno stejné zažízení předanné víckrát. Konkrétně jde o deviceID 132. původně měl tuto věci třikrát, ale jednu vrátil(ID řádku 294). Má procedura ale z výsledku vyhodí tohle ID všechno. Výsledek mého dotazu by měla v tomto případě být tabulka s pěti řádky zařízení, které stále má.

V prvním kroku vytvářím dočasnou tabulku s propojením a kompletní informací, v tom dalším dělám dva výběry z dočasné tabulky a ty porovnávám a vracím výsledek. Pokud je tam jedno ID jen jednou předané a vrácenné tak to funguje dobře, ale pokud je tam jedno zařízení dvakrát a vícekrát, tak to nefunguje.

Odpovědět

28.8.2018 16:26

Jakub Švasta

Lektor

Jakub Švasta:28.8.2018 20:39

Možná existuje lepší řešení, ale z první bych na to šel třeba takhle: zeptal bych se, kolik zařízení musí zbýt zaměstnanci půjčených (počet půjčení mínus počet vrácení pro dané zařízení), a do dočasné tabulky uložil právě tolik nejnovějších záznamů o výpůjčce. A takhle bych projel všechny zařízení cyklem (cursorem). Budu to psát pro MS-SQL, jinde se syntaxe může mírně lišit:

-- predpokladam, ze @EmployeeId bere procedura jako parametr

CREATE TABLE #Results
(
        id INT,
        device_id INT,
        employee_id INT,
        [date] DATETIME,
        [state] VARCHAR(40),
        protocol_number INT,
        [transfer] BIT,
        [name] VARCHAR(30),
        serial_number VARCHAR(20),
        inventory_number VARCHAR(6)
)

DECLARE @DeviceId INT

DECLARE C CURSOR READ_ONLY LOCAL FOR
SELECT DISTINCT device_id FROM Assigned_device WHERE employee_id = @EmployeeId
OPEN C
FETCH C INTO @DeviceId
WHILE (@@FETCH_STATUS = 0)
BEGIN

        DECLARE @DeviceCount INT
        SELECT @DeviceCount =
                (SELECT COUNT(1) FROM Assigned_device WHERE device_id = @DeviceId AND employee_id = @EmployeeId AND [transfer] = 1)
                - (SELECT COUNT(1) FROM Assigned_device WHERE device_id = @DeviceId AND employee_id = @EmployeeId AND [transfer] = 0)

        INSERT INTO #Results (id, device_id, employee_id, [date], [state], protocol_number, [transfer], [name], serial_number, inventory_number)
        SELECT TOP @DeviceCount
                AD.id, AD.device_id, AD.employee_id, AD.[date], AD.[state], AD.protocol_number, AD.[transfer], D.[name], D.serial_number, D.inventory_number
        FROM Assigned_device AD
        LEFT OUTER JOIN Device D ON D.id = AD.device_id
        WHERE AD.device_id = @DeviceId AND AD.employee_id = @EmployeeId AND AD.[transfer] = 1
        ORDER BY AD.[date] DESC         -- radit by slo i podle ad.id DESC

FETCH C INTO @DeviceId
END CLOSE C DEALLOCATE C

SELECT * FROM #Results

DROP TABLE #Results

Nahoru Odpovědět

28.8.2018 20:39

don.jarducius

Člen

don.jarducius:28.8.2018 20:50

Ahoj,
využij v dotazu case when then else end
Pujčil si (1) + vrátil (-1) tzn. už nemá (součet 0)

Dotaz bych postavil nějak takhle:

select * from (select device_id, empployee_id, sum(case when transfer = 1 then 1 else -1 end) Pocet
from [Assigned_device] with(nolock)
where empployee_id = 48
group by device_id, empployee_id) as tmpData inner join Device with(nolock) on tmpData.Device_ID = Device.ID
where tmpData.pocet > 0

Ppředpokládám, podle zápisu, že se jedná o MSSQL. To with(nolock) znamená že čte bez zámků a i nedokončené transakce, je o dost rychlejší, tady by dirty read vadit neměl

Akceptované řešení
+20 Zkušeností
+2,50 Kč

Nahoru Odpovědět

28.8.2018 20:50

Ten kdo nechce hledá důvod, ten kdo chce hledá způsob

Peter Mlich

Člen

Peter Mlich:29.8.2018 9:04

Podle mne to datum podstatne je. Ale ne teda pro forko.

muze existovat 5 pujceni a 2 vraceni nebo 2 pujceni a 5 vraceni, protoze doslo k chybe pri evidenci, at uz umyslne nebo omylem

Slo by resit tak, ze zarizeni i zamest. ma chip. Chipem si otevre dvere s naradim. Zarizeni projde dvernim ramem a zaeviduje zam-chip + naradi-chip, automaticky. Stale muze dojit k chybe. Ale aspon to nijak zvlast lidi neobtezuje.

Dalo by se to resit pres group by. Vyberes si 2 radky podle datumu, zarizeni + zapujceni, zarizeni + vraceni. A podle datumu urcit, zda schazi nebo je ve stavu vraceno, asi dalsim grupem. Ten dotaz jenom naznacim, zatim jsem nic takoveho neresil...

(
(
SELECT id_radek, id_zarizeni, datum
FROM tab
WHERE datum=MAX(datum)
GROUP BY id_zarizeni, stav
) -- to by melo vratit 2 radky, posledni vypujceni a vraceni pro kazde zarizeni
WHERE datum=MAX(datum)
GROUP BY id_zarizeni
) -- z toho vytridim posledni stav podle datumu
WHERE stav=0 (pujceno a nevraceno)
-- a nakonec vyberu podle stavu

Mozna to jde zjednodusit, vynechat nejaky krok.

Editováno 29.8.2018 9:07

Nahoru Odpovědět

29.8.2018 9:04

tomas

Člen

tomas:29.8.2018 10:07

Může existovat 5 půjčení a 2 vrácení, opačně ne. Nemůže vrátit něco co si nepůjčil. Jinak se jedná o MS-SQL. Zrovna zkouším vaše návrhy, tak dám vědět. Děkuji za rady. Jinak datum mám v tabulce vložený bez času a konkrétně deviceid132 byl zapůjčen dvakrát v jeden den v rámci jednoho protokolu. Čili zde nejde rozenat který byl dříve podle datumu ani času, ale jen podle id řádku v tabulce.

Nahoru Odpovědět

29.8.2018 10:07

tomas

Člen

tomas:29.8.2018 14:16

Děkuji všem, řešení od don.jarducius mě vrátí přesně to co potřebuji. Díky.

Nahoru Odpovědět

29.8.2018 14:16

Naučíme tě pracovat na home-office.

Zjistit více...

Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 6 zpráv z 6.