Centar za edukaciju-BiH

iCentar → Racunari i oprema → Programirannje i baze podataka → MySQL


Prosiri i skupi Opcije teme
Stranice (6):1,2,3,4,5,6
 Prikazi prvu neprocitanu poruku

#41 10.06.2020 19:34
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
Citiraj Gjoreski:
jel datum uslov ili nije

zapocne se od danasnjeg dana, a danasnji dan je recimo 1.6.
sutra ce biti 2.6 itd.

recimo da ce WHERE biti date>='$danasnjiDatum'
zivot je moja domovina.
↑  ↓

#42 10.06.2020 22:05
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,854


Predmet:Re: SQL upiti
Opet se nerazumemo
Pitajne glasi.
1. Ako je datum uslov od cega se sostoi on ? 1. Jedan datum
2. Moze biti vise datuma
2. id_Office je isto uslov
- E sad ako je jedan datum onda imamo uslov r1.date >= DATE('2020-06-01') sta je ispravno ali ces dobiti
podatke samo za tog datuma
-- Ako je ovako dobicemo Sve radnike koi su radili u kancalarijama gde je radio Vasko bez razlika dali
Vasko je bio na posao
- Ako su vise datuma po koj kriterium ih biramo
Dali su oni u nekakav raspon OD DO ili je uslov Vasko kad je radio
Ako je u rasponu OD DO bobijamo isti rezultat kako i predhodno samo za odredzeni period
Ako je uslov Vasko za dobijajne datuma onda je moje resejne od posta #31 ispravno
Znaci u resejne #31 imamo uslov ON r1.date=tUs.date AND s1.id_office=tUs.id_office

Evo sta jas dobijam

Slicice prilozenih slika:
Vasko.png
Tip datoteke:Informacije o tipu datoteke za:png png
Preuzimanja:113
Velicina datoteke:41.13 KB
Velicina slike: 766 x 464 Pikseli

↑  ↓

#43 10.06.2020 22:29
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
da to je tocno, ali kada pokrenem taj upit ja dobijem ovo:



i nemogu sortirati po datumu
zivot je moja domovina.
↑  ↓

#44 10.06.2020 23:10
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,854


Predmet:Re: SQL upiti
PreuzmiIzvorni kôd (SQL):
  1. SELECT r1.DATE, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.DATE) AS Uslov
  2. FROM tbl_schedule AS r1
  3. INNER JOIN tbl_calendar AS k1 ON r1.DATE = k1.DATE
  4. LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
  5. INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
  6. INNER JOIN (
  7.     SELECT ra1.DATE, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
  8.     FROM tbl_schedule AS ra1
  9.     INNER JOIN tbl_calendar AS ka1 ON ra1.DATE = ka1.DATE
  10.     LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
  11.     INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
  12.     WHERE ea1.id_employees=1001) tUs
  13.     ON CONCAT(s1.id_office,'_',r1.DATE) = CONCAT(tUs.id_office,'_',tUs.DATE)
  14. ORDER BY r1.DATE ASC, r1.id_department ASC;
↑  ↓

#45 10.06.2020 23:39
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
@Gjoreski
date Ascending 1id_employeesid_departmentid_officestartTimeUslov
2020-06-01100110105:00:0010_1_2020-06-01
2020-06-011002310111:00:00310_1_2020-06-01
2020-06-011003610117:00:00610_1_2020-06-01
2020-06-02100830306:00:0030_3_2020-06-02
2020-06-021009330312:00:00330_3_2020-06-02
2020-06-021001630318:00:00630_3_2020-06-02
2020-06-03100930306:00:0030_3_2020-06-03
2020-06-031001330312:00:00330_3_2020-06-03
2020-06-031002630318:00:00630_3_2020-06-03
2020-06-04100130306:00:0030_3_2020-06-04
2020-06-041002330312:00:00330_3_2020-06-04
2020-06-041003630318:00:00630_3_2020-06-04
2020-06-05100820205:30:0020_2_2020-06-05
2020-06-051009320211:30:00320_2_2020-06-05
2020-06-051001620217:30:00620_2_2020-06-05
2020-06-06100920209:00:0020_2_2020-06-06
2020-06-061001320215:00:00320_2_2020-06-06

to je to ali fali još da napiše i
2020-06-07 1001FD
zivot je moja domovina.
↑  ↓

#46 11.06.2020 15:34
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,854


Predmet:Re: SQL upiti
PreuzmiIzvorni kôd (SQL):
  1. SELECT r1.DATE, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.DATE) AS Uslov
  2. FROM tbl_schedule AS r1
  3. INNER JOIN tbl_calendar AS k1 ON r1.DATE = k1.DATE
  4. LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
  5. INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
  6. INNER JOIN (
  7.     SELECT ra1.DATE, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
  8.     FROM tbl_schedule AS ra1
  9.     INNER JOIN tbl_calendar AS ka1 ON ra1.DATE = ka1.DATE
  10.     LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
  11.     INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
  12.     WHERE ea1.id_employees=1001) tUs
  13.     ON CONCAT(s1.id_office,'_',r1.DATE) = CONCAT(tUs.id_office,'_',tUs.DATE)
  14. UNION ALL
  15. SELECT r1.DATE, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.DATE) AS Uslov
  16. FROM tbl_schedule AS r1
  17. INNER JOIN tbl_calendar AS k1 ON r1.DATE = k1.DATE
  18. LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
  19. INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
  20. INNER JOIN (
  21.     SELECT ra1.DATE, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
  22.     FROM tbl_schedule AS ra1
  23.     INNER JOIN tbl_calendar AS ka1 ON ra1.DATE = ka1.DATE
  24.     LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
  25.     INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
  26.     WHERE ea1.id_employees=1001) tUs
  27. ON r1.DATE=tUs.DATE AND e1.id_employees=tUs.id_employees
  28. WHERE ISNULL(s1.id_office)
  29. ORDER BY DATE ASC, id_department ASC
↑  ↓

#47 11.06.2020 23:44
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
1. nema 1001 za 1.6.
2. zasto mi u rezultatu daje EDIT, COPY, DELETE kolone? (mozda ovo LINK, greška u phpAdminu, koliko sam razumio)

zivot je moja domovina.
Ovaj post je ureden 1 puta. Posljednja izmjena 11.06.2020 23:48 od strane Avko. ↑  ↓

#48 12.06.2020 09:01
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,854


Predmet:Re: SQL upiti
Ja sam na sql a koi si potvrdio dodao jos jedan sql sa union za ono FD sta nije bilo . Smatram da je problem taj PHP myAdmin koj koristis,Potrazi uzmi neki alat za misajne koda u my sql i vidi sta ces dobiti.
Za pocetak pokreni oba dva sql a bez Union All I pogledaj rezultat.
Evo sta ja dobijam:

Slicice prilozenih slika:
1111.png
Tip datoteke:Informacije o tipu datoteke za:png png
Preuzimanja:145
Velicina datoteke:51.23 KB
Velicina slike: 916 x 479 Pikseli

↑  ↓

#49 12.06.2020 22:08
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
1. sql

PreuzmiIzvorni kôd (Text):
  1. SELECT r1.date, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.date) AS Uslov
  2. FROM tbl_schedule AS r1
  3. INNER JOIN tbl_calendar AS k1 ON r1.date = k1.date
  4. LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
  5. INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
  6. INNER JOIN (
  7.     SELECT ra1.date, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
  8.     FROM tbl_schedule AS ra1
  9.     INNER JOIN tbl_calendar AS ka1 ON ra1.date = ka1.date
  10.     LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
  11.     INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
  12.     WHERE ea1.id_employees=1001) tUs
  13.     ON CONCAT(s1.id_office,'_',r1.date) = CONCAT(tUs.id_office,'_',tUs.date)

rezultat:
dateid_employeesid_departmentid_officestartTimeUslov
2020-06-01100110105:00:0010_1_2020-06-01
2020-06-011002310111:00:00310_1_2020-06-01
2020-06-011003610117:00:00610_1_2020-06-01
2020-06-04100130306:00:0030_3_2020-06-04
2020-06-041002330312:00:00330_3_2020-06-04
2020-06-041003630318:00:00630_3_2020-06-04
2020-06-031001330312:00:00330_3_2020-06-03
2020-06-031002630318:00:00630_3_2020-06-03
2020-06-03100930306:00:0030_3_2020-06-03
2020-06-051001620217:30:00620_2_2020-06-05
2020-06-05100820205:30:0020_2_2020-06-05
2020-06-051009320211:30:00320_2_2020-06-05
2020-06-021001630318:00:00630_3_2020-06-02
2020-06-02100830306:00:0030_3_2020-06-02
2020-06-021009330312:00:00330_3_2020-06-02
2020-06-061001320215:00:00320_2_2020-06-06
2020-06-06100920209:00:0020_2_2020-06-06

2.sql
PreuzmiIzvorni kôd (Text):
  1. SELECT r1.date, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.date) AS Uslov
  2. FROM tbl_schedule AS r1
  3. INNER JOIN tbl_calendar AS k1 ON r1.date = k1.date
  4. LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
  5. INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
  6. INNER JOIN (
  7.     SELECT ra1.date, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
  8.     FROM tbl_schedule AS ra1
  9.     INNER JOIN tbl_calendar AS ka1 ON ra1.date = ka1.date
  10.     LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
  11.     INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
  12.     WHERE ea1.id_employees=1001) tUs
  13. ON r1.date=tUs.date AND e1.id_employees=tUs.id_employees
  14. WHERE ISNULL(s1.id_office)
  15. ORDER BY date ASC, id_department ASC

dateid_employeesid_departmentid_officestartTimeUslov
2020-06-071001FDNULL    NULL    NULL  


to je to, hvala Gjoreski. Rezultat je ono sto sam trazio.
Potrazit cu rijesenje za union all i prouciti.

hvala jos jednom
zivot je moja domovina.
↑  ↓

#50 14.06.2020 11:12
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
evo pokrenuo sam u starom wampserver, tamo mi ne radi php >5.5 i tamo UNION ALL radi.
Evo rezultat:



eto, nemoze sve biti idealno. wampserver nema novi php, a UwWamp ne podrzava UNION ALL.
Tako je to u zivotu
zivot je moja domovina.
↑  ↓

Stranice (6):1,2,3,4,5,6

Srodne teme

Ko je online?

Aktivni clanovi:
nema clanova


Kontakt
Sva vremena su GMT +01:00. Trenutno vrijeme: 1: 37 am.