Avko |
10.06.2020 20:34 |
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' |
Gjoreski |
10.06.2020 23:05 |
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 |
Prilozi: Vasko.png (Velicina datoteke:41.13 KB)
|
Avko |
10.06.2020 23:29 |
Predmet:Re: SQL upiti
da to je tocno, ali kada pokrenem taj upit ja dobijem ovo:
i nemogu sortirati po datumu |
Gjoreski |
11.06.2020 00:10 |
Predmet:Re: SQL upiti
PreuzmiIzvorni kôd (SQL):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
FROM tbl_schedule AS r1
INNER JOIN tbl_calendar AS k1 ON r1.DATE = k1.DATE
LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
INNER JOIN (
SELECT ra1.DATE, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
FROM tbl_schedule AS ra1
INNER JOIN tbl_calendar AS ka1 ON ra1.DATE = ka1.DATE
LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
WHERE ea1.id_employees=1001) tUs
ON CONCAT(s1.id_office,'_',r1.DATE) = CONCAT(tUs.id_office,'_',tUs.DATE)
ORDER BY r1.DATE ASC, r1.id_department ASC;
|
Avko |
11.06.2020 00:39 |
Predmet:Re: SQL upiti
@Gjoreski
date Ascending 1 | id_employees | id_department | id_office | startTime | Uslov | 2020-06-01 | 1001 | 10 | 1 | 05:00:00 | 10_1_2020-06-01 | 2020-06-01 | 1002 | 310 | 1 | 11:00:00 | 310_1_2020-06-01 | 2020-06-01 | 1003 | 610 | 1 | 17:00:00 | 610_1_2020-06-01 | 2020-06-02 | 1008 | 30 | 3 | 06:00:00 | 30_3_2020-06-02 | 2020-06-02 | 1009 | 330 | 3 | 12:00:00 | 330_3_2020-06-02 | 2020-06-02 | 1001 | 630 | 3 | 18:00:00 | 630_3_2020-06-02 | 2020-06-03 | 1009 | 30 | 3 | 06:00:00 | 30_3_2020-06-03 | 2020-06-03 | 1001 | 330 | 3 | 12:00:00 | 330_3_2020-06-03 | 2020-06-03 | 1002 | 630 | 3 | 18:00:00 | 630_3_2020-06-03 | 2020-06-04 | 1001 | 30 | 3 | 06:00:00 | 30_3_2020-06-04 | 2020-06-04 | 1002 | 330 | 3 | 12:00:00 | 330_3_2020-06-04 | 2020-06-04 | 1003 | 630 | 3 | 18:00:00 | 630_3_2020-06-04 | 2020-06-05 | 1008 | 20 | 2 | 05:30:00 | 20_2_2020-06-05 | 2020-06-05 | 1009 | 320 | 2 | 11:30:00 | 320_2_2020-06-05 | 2020-06-05 | 1001 | 620 | 2 | 17:30:00 | 620_2_2020-06-05 | 2020-06-06 | 1009 | 20 | 2 | 09:00:00 | 20_2_2020-06-06 | 2020-06-06 | 1001 | 320 | 2 | 15:00:00 | 320_2_2020-06-06 |
to je to ali fali još da napiše i
|
Gjoreski |
11.06.2020 16:34 |
Predmet:Re: SQL upiti
PreuzmiIzvorni kôd (SQL):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
FROM tbl_schedule AS r1
INNER JOIN tbl_calendar AS k1 ON r1.DATE = k1.DATE
LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
INNER JOIN (
SELECT ra1.DATE, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
FROM tbl_schedule AS ra1
INNER JOIN tbl_calendar AS ka1 ON ra1.DATE = ka1.DATE
LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
WHERE ea1.id_employees=1001) tUs
ON CONCAT(s1.id_office,'_',r1.DATE) = CONCAT(tUs.id_office,'_',tUs.DATE)
UNION ALL
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
FROM tbl_schedule AS r1
INNER JOIN tbl_calendar AS k1 ON r1.DATE = k1.DATE
LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
INNER JOIN (
SELECT ra1.DATE, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
FROM tbl_schedule AS ra1
INNER JOIN tbl_calendar AS ka1 ON ra1.DATE = ka1.DATE
LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
WHERE ea1.id_employees=1001) tUs
ON r1.DATE=tUs.DATE AND e1.id_employees=tUs.id_employees
WHERE ISNULL(s1.id_office)
ORDER BY DATE ASC, id_department ASC
|
Avko |
12.06.2020 00:44 |
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)
|
Gjoreski |
12.06.2020 10:01 |
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: |
Prilozi: 1111.png (Velicina datoteke:51.23 KB)
|
Avko |
12.06.2020 23:08 |
Predmet:Re: SQL upiti
1. sql
PreuzmiIzvorni kôd (Text):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
FROM tbl_schedule AS r1
INNER JOIN tbl_calendar AS k1 ON r1.date = k1.date
LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
INNER JOIN (
SELECT ra1.date, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
FROM tbl_schedule AS ra1
INNER JOIN tbl_calendar AS ka1 ON ra1.date = ka1.date
LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
WHERE ea1.id_employees=1001) tUs
ON CONCAT(s1.id_office,'_',r1.date) = CONCAT(tUs.id_office,'_',tUs.date)
rezultat:
date | id_employees | id_department | id_office | startTime | Uslov |
---|
2020-06-01 | 1001 | 10 | 1 | 05:00:00 | 10_1_2020-06-01 | 2020-06-01 | 1002 | 310 | 1 | 11:00:00 | 310_1_2020-06-01 | 2020-06-01 | 1003 | 610 | 1 | 17:00:00 | 610_1_2020-06-01 | 2020-06-04 | 1001 | 30 | 3 | 06:00:00 | 30_3_2020-06-04 | 2020-06-04 | 1002 | 330 | 3 | 12:00:00 | 330_3_2020-06-04 | 2020-06-04 | 1003 | 630 | 3 | 18:00:00 | 630_3_2020-06-04 | 2020-06-03 | 1001 | 330 | 3 | 12:00:00 | 330_3_2020-06-03 | 2020-06-03 | 1002 | 630 | 3 | 18:00:00 | 630_3_2020-06-03 | 2020-06-03 | 1009 | 30 | 3 | 06:00:00 | 30_3_2020-06-03 | 2020-06-05 | 1001 | 620 | 2 | 17:30:00 | 620_2_2020-06-05 | 2020-06-05 | 1008 | 20 | 2 | 05:30:00 | 20_2_2020-06-05 | 2020-06-05 | 1009 | 320 | 2 | 11:30:00 | 320_2_2020-06-05 | 2020-06-02 | 1001 | 630 | 3 | 18:00:00 | 630_3_2020-06-02 | 2020-06-02 | 1008 | 30 | 3 | 06:00:00 | 30_3_2020-06-02 | 2020-06-02 | 1009 | 330 | 3 | 12:00:00 | 330_3_2020-06-02 | 2020-06-06 | 1001 | 320 | 2 | 15:00:00 | 320_2_2020-06-06 | 2020-06-06 | 1009 | 20 | 2 | 09:00:00 | 20_2_2020-06-06 |
2.sql
PreuzmiIzvorni kôd (Text):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
FROM tbl_schedule AS r1
INNER JOIN tbl_calendar AS k1 ON r1.date = k1.date
LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
INNER JOIN (
SELECT ra1.date, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
FROM tbl_schedule AS ra1
INNER JOIN tbl_calendar AS ka1 ON ra1.date = ka1.date
LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
WHERE ea1.id_employees=1001) tUs
ON r1.date=tUs.date AND e1.id_employees=tUs.id_employees
WHERE ISNULL(s1.id_office)
ORDER BY date ASC, id_department ASC
date | id_employees | id_department | id_office | startTime | Uslov |
---|
2020-06-07 | 1001 | FD | NULL NULL NULL | | |
to je to, hvala Gjoreski. Rezultat je ono sto sam trazio.
Potrazit cu rijesenje za union all i prouciti.
hvala jos jednom |
Avko |
14.06.2020 12:12 |
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 |