how can I determine the names of users who have two or more tasks?

If I have the following tables

users
ID  NAME
1   john
2   jack
3   kate 
4   sawyer


tasks
ID   USER_ID   TITLE
1      1       mow lawn
2      2       take out trash
3      1       file taxes
4      2       wash dishes
5      1       learn ruby
6      3       groom dog

How can I determine the names of users who have two or more tasks?

I came up with the following but I want to know if there's a more efficient query.

SELECT name, cnt 
FROM (SELECT count(t.user_id) cnt, u.name 
      FROM tasks t INNER JOIN users u ON u.id=t.user_id
      GROUP BY t.user_id) as copy_table
WHERE cnt > 1
开发者_JAVA百科

  SELECT count(t.user_id) cnt, u.name 
  FROM tasks t INNER JOIN users u ON u.id=t.user_id
  GROUP BY t.user_id, u.name
  HAVING count(t.user_id) > 1

or

  with interesting as
  ( 
     SELECT count(t.user_id) cnt, t.user_id
     FROM tasks t 
     GROUP BY t.user_id
     HAVING count(t.user_id) > 1   
  )
  select cnt, u.name 
  from users u
  join interesting i on u.id = i.user_id

    SELECT users.*
      FROM users
INNER JOIN (SELECT user_id
              FROM tasks
          GROUP BY user_id
            HAVING COUNT(*) > 1) x ON x.user_id = users.id

  SELECT count(t.user_id) cnt, u.name 
  FROM tasks t INNER JOIN users u ON u.id=t.user_id
  GROUP BY t.user_id, u.name 
  HAVING count(t.user_id) > 1

SELECT DISTINCT user.name
FROM USERS, TASKS AS task1, TASKS AS task2
WHERE USERS.ID = task1.user_id AND USERS.ID = task2.user_id AND task1.id <> task2.id;

This represents a join between the tasks table and itself over userid,
The task1.id <> task2.id restriction leaves only rows with pairs of distinct tasks had by the same user,
The join with USERS joins in the user name,
And the DISTINCT filters out the duplicates.

If your DBMS gives you performance trouble over DISTINCT, then :

• get a better DBMS
• rewrite the query as a correlated subquery

(genre SELECT name FROM USERS WHERE USERS.ID in (SELECT userid FROM TASKS task1 JOIN TASKS task2 ON ... WHERE);