SELECT 0 AS id, '%{User-Name}' AS UserName, 'Mikrotik-Rate-Limit' AS Attribute, CONCAT(ROUND(COALESCE(x.upceil, y.upceil, z.upceil)),'k','/', ROUND(COALESCE(x.downceil, y.downceil, z.downceil)),'k') AS Value, '==' AS op FROM (SELECT n.id, MIN(n.name) AS name, SUM(t.downceil/o.cnt) AS downceil, SUM(t.upceil/o.cnt) AS upceil FROM nodeassignments na JOIN assignments a ON (na.assignmentid = a.id) JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (na.nodeid = 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 n.name = '%{User-Name}' GROUP BY n.id ) x LEFT JOIN (SELECT SUM(t.downceil)/o.cnt AS downceil, SUM(t.upceil)/o.cnt AS upceil FROM assignments a JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (a.customerid = n.ownerid) JOIN (SELECT COUNT(*) AS cnt, ownerid FROM nodes WHERE NOT EXISTS(SELECT 1 FROM nodeassignments, assignments a WHERE assignmentid = a.id AND nodeid = nodes.id AND (a.dateto > unix_timestamp() OR a.dateto = 0)) GROUP BY ownerid) o ON (o.ownerid = n.ownerid) WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) AND (a.dateto > unix_timestamp() OR a.dateto = 0) AND NOT EXISTS (SELECT 1 FROM nodeassignments WHERE assignmentid = a.id) AND n.name = '%{User-Name}' GROUP BY n.id ) y ON (1=1) RIGHT JOIN (SELECT n.id, n.name, 20000 AS downceil, 2000 AS upceil FROM nodes n WHERE n.name = '%{User-Name}' ) z ON (1=1) UNION SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = '%{User-Name}' ORDER BY id;
Zwraca mi ładnie:
UserName | Attribute | Value | op
user |Mikrotik-Rate-Limit |11111k/2222k | ==
Chciałbym dodać do tego jeszcze jedną linijkę
user |Cisco-Service-Info | QU;11111000;D;2222000 | ==
I tutaj mam pytanie: czy muszę duplikować i podać jeszcze raz powyższe zapytanie ze zmienionymi wartościami przy "AS" i w CONCAT czy da się to jakoś inaczej zrobić aby tak nie rozbudowywać finalnego zapytania?