W mam radiusie takie zapytanie:
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?