W dniu 2012-05-24 12:44, Marcin pisze:
Witam. mam zapytanie sql: SELECT x.id x.id as id, 'marcin' AS UserName, CONCAT(ROUND(COALESCE(x.upceil, y.upceil)),'k','/', ROUND(COALESCE(x.downceil, y.downceil)),'k') AS Value FROM ( SELECT n.id n.id, MIN(n.name n.name) AS name, SUM(t.downceil) AS downceil, SUM(t.upceil) AS upceil FROM nodeassignments na JOIN assignments a ON (na.assignmentid = a.id a.id) JOIN tariffs t ON (a.tariffid = t.id t.id) JOIN nodes n ON (na.nodeid = n.id n.id) JOIN ( SELECT assignmentid, COUNT(*) AS cnt FROM nodeassignments GROUP BY assignmentid ) o ON (o.assignmentid = na.assignmentid) WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) AND (a.dateto unix_timestamp() OR a.dateto = 0) AND a.suspended = 0 AND n.name n.name = 'marcin' GROUP BY n.id n.id ) x RIGHT JOIN ( SELECT n.id n.id, n.name n.name, 64 AS downceil, 64 AS upceil FROM nodes n WHERE n.name n.name = 'marcin' ) y ON (1=1)
to zapytanie ładnie mi zwraca prędkość danego usera. jak mogę wyświetlić dla wszystkich komputerów z publicznymi adresami?
Tak na szybcika warunek w ostatnim joinie dajesz "WHERE ipaddr_pub 0" zamiast "WHERE n.name = 'marcin'". Tylko na poczatku "x.id x.id as id, 'marcin' AS UserName," na "y.id as id, y.name AS UserName," W ogole skad masz to zapytanie?