SELECT id WHERE COUNT(*) > X ? - How to get records from any user that has more than X records in table?

Obviously the query in the title does not work, but it might illustrate in a naive way, what I would like to do. I have a table that contains some users identified by an id column. This id is NOT unique within the database. It marks a user that may have multiple 开发者_C百科records in my table.

How can I show the whole record of all users (identified by id) that have more than 10 records in my table?

---

Use having instead of where:

SELECT id
  FROM (
        SELECT id, COUNT(*) as cnt
        FROM somewhere 
        GROUP BY id
        HAVING cnt > 1
   ) temp_table

---

SELECT * FROM user 
WHERE id IN (SELECT id FROM user GROUP BY id HAVING COUNT(*) > 10)

---

SELECT id, COUNT(*) FROM Table GROUP BY id HAVING COUNT(*) > 10

---

here is how:

 CREATE TABLE mytable_clean AS
 SELECT * FROM mytable WHERE id IN(
 SELECT id FROM
 (SELECT id,COUNT(*) AS appearance 
 FROM mytable
 GROUP BY id) AS id_count
 WHERE id_count.appearance > 9 )

It does work. It's not to slow, but looks a little clumsy to me. Better solutions welcome :)