Imam sledeći upit :
Code:
SELECT
t1.*,
t2.name,
IF(ROUND(AVG(t3.grade),2)IS NULL,0,ROUND(AVG(t3.grade),2)) AS grade,
COUNT(t3.jid) AS votes
FROM t1
INNER JOIN t2 ON t1.cat_id=t2.id
INNER JOIN t3 ON jokes.id=t3.joke_id
WHERE (t1.approved_by IS NOT NULL)
GROUP BY t1.id
HAVING grade >8
LIMIT 0,5
SELECT
t1.*,
t2.name,
IF(ROUND(AVG(t3.grade),2)IS NULL,0,ROUND(AVG(t3.grade),2)) AS grade,
COUNT(t3.jid) AS votes
FROM t1
INNER JOIN t2 ON t1.cat_id=t2.id
INNER JOIN t3 ON jokes.id=t3.joke_id
WHERE (t1.approved_by IS NOT NULL)
GROUP BY t1.id
HAVING grade >8
LIMIT 0,5
Hoću da pre ovoga izvršim jedan upit koji će izbrojati rezultate po datim kriterijumima i vratiti kao rezultat broj zapisa.
Ovo mi treba zbog paginacije, tj. neću da mi vrati sve zapise kao u ovom upitu gore,
nego mi treba da prvo izbrojim a onda prikažem samo prvih n.
Ja sam ovako rešio to, ali mi i dalje deluje da može bolje, pa ako može neki hint...
Code:
SELECT COUNT(*) AS br_zapisa FROM (SELECT
IF(ROUND(AVG(t3.grade),2)IS NULL,0,ROUND(AVG(t3.grade),2)) AS grade
FROM t1
INNER JOIN t2 ON t1.cat_id=t2.id
INNER JOIN t3 ON t1.id=t3.joke_id
WHERE (t1.approved_by IS NOT NULL)
GROUP BY t1.id
HAVING grade >8
)AS rezultat
SELECT COUNT(*) AS br_zapisa FROM (SELECT
IF(ROUND(AVG(t3.grade),2)IS NULL,0,ROUND(AVG(t3.grade),2)) AS grade
FROM t1
INNER JOIN t2 ON t1.cat_id=t2.id
INNER JOIN t3 ON t1.id=t3.joke_id
WHERE (t1.approved_by IS NOT NULL)
GROUP BY t1.id
HAVING grade >8
)AS rezultat
[Ovu poruku je menjao Tudfa dana 18.05.2009. u 21:38 GMT+1]