distinct count() Opcje

[jastu]

Witam,
jeśli mamy w tabeli trzy wiersze i pole parent_id ma wartości 1, 2, 2 to możemy policzyć ilość unikalnych parent_id tak

[SQL] pobierz, plaintext 
SELECT DISTINCT parent_id FROM TABLE
[SQL] pobierz, plaintext 

a jak to zrobić z count() ?


Potrzebne mi to do widoku

[SQL] pobierz, plaintext 
CREATE VIEW `db_core_`.`f_v_grupy` AS SELECT
              g.*,
              count(f.id_f) AS ile_for,
              count(c.id_k) AS ile_kat,
              count(t.id_t) AS ile_tem,
              count(r.id_o) AS ile_odp
            FROM f_t_groups AS g
            LEFT JOIN f_t_forums AS f ON f.id_g = g.id_g
            LEFT JOIN f_t_categories AS c ON f.id_f = c.id_f
            LEFT JOIN f_t_topics AS t ON c.id_k = t.id_k
            LEFT JOIN f_t_replies AS r ON t.id_t = r.id_t
            GROUP BY
              id_g
[SQL] pobierz, plaintext 

obecna wersja zlicza błędnie...

Ten post edytował jastu 24.08.2007, 12:42:47

[nospor]

[SQL] pobierz, plaintext 
SELECT count(DISTINCT parent_id) FROM TABLE
[SQL] pobierz, plaintext 

[jastu]

Ręce mi opadły....mogłem sprawdzić w ten sposób.Dzięki !

Szkoda bo :

For a view to be updatable, there must be a one to one relatoinship between a view row and a (base) table row. Although I can think of a situation where a join does not preclude this requirement, it seem MySQL chooses to ban joins altogether. At least, the doc:

[dev.mysql.com]

say that a view is not updateble if it contains:

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
DISTINCT
GROUP BY
HAVING
UNION or UNION ALL
Subquery in the select list
Join
Non-updatable view in the FROM clause
A subquery in the WHERE clause that refers to a table in the FROM clause
Refers only to literal values (in this case, there is no underlying table to update)
ALGORITHM = TEMPTABLE (use of a temporary table always makes a view non-updatable)

Ten post edytował jastu 24.08.2007, 13:20:57