|
SQL baze podataka - Sponzor: Baze-Podataka.net MySQL, MSSQL, Oracle, Access, ODBC. Ako imate problem brže i preciznije ćete dobiti odgovor ako priložite strukturu tabela ili skript koji kreira tabele i puni ih test podacima umesto što to problem opisujete samo rečima. Sponzor: Baze-Podataka.net - Blog o bazama podataka |
|
Alati teme | Način prikaza |
02. 12. 2007. | #1 |
Ivan Dilber
Sir Write-a-Lot
|
update pomocu subqueries u mysql-u
imam 2 tabele koje se koriste za pravljenje statistike(jedna pamti svako pojedinacno glasanje, druga cuva izracunatu statistiku):
Kôd:
mysql> desc votes; +---------+---------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+-------------------+----------------+ | id | int(20) unsigned | NO | PRI | NULL | auto_increment | | post_id | bigint(20) unsigned | NO | MUL | 0 | | [... neka polja...] | rank | tinyint(3) unsigned | NO | | 0 | | +---------+---------------------+------+-----+-------------------+----------------+ mysql> desc post_details; +----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------+------+-----+---------+-------+ | post_id | bigint(20) unsigned | NO | PRI | 0 | | [... neka polja...] | rating | tinyint(3) unsigned | NO | MUL | 0 | | | num_votes | int(10) | NO | | 0 | | +----------------+---------------------+------+-----+---------+-------+ SELECT AVG(rank), COUNT(*) FROM votes GROUP BY post_id; i onda za svaki post upisati izracunate vrednosti u post_details. Naravno ovo nije frka da se uradi programski u petlji, ali me zanima da li moze da se resi pomocu jednog upita( i koriscenjem subqueries pretpostavljam), posto cenim da moze, ali nisam uspeo da se snadjem kako... ajd da vas cujem.. EDIT: Uspeo sam u medjuvremenu da odradim ovo sa 2 upita: Kôd:
UPDATE brush_details AS bd SET num_votes= ( SELECT count(*) FROM brush_votes AS bv WHERE bv.post_id=bd.post_id ); UPDATE brush_details AS bd SET rating= ( SELECT avg(rank) FROM brush_votes AS bv WHERE bv.post_id=bd.post_id );
__________________
Leadership is the art of getting people to want to do what you know must be done. Poslednja izmena od ivanhoe : 02. 12. 2007. u 00:37. |
|
|
Slične teme | ||||
Tema | Početna poruka teme | Forum | Odgovori | Poslednja poruka |
cross-domain komunikacije pomocu iframe-a | ivanhoe | (X)HTML, JavaScript, DHTML, XML, CSS | 3 | 27. 05. 2009. 18:14 |
ranges pomocu regexpa | ivanhoe | Regular expression i htaccess | 1 | 12. 11. 2007. 09:31 |
mysql update problem | vendi | SQL baze podataka - Sponzor: Baze-Podataka.net | 3 | 27. 06. 2007. 23:32 |
Citanje binarnih fajlova pomocu PHP? | krcko | PHP | 4 | 19. 07. 2006. 01:33 |
prenosenje pomocu get metode | oliver78 | PHP | 16 | 30. 11. 2005. 20:09 |