Finding many matches to one row in the same table

问题:

My question is very simple but I can't find any solution to this. Please help me someone as I'm stuck very bad.

My table is this.

users_table:

 id | name | admin   | property_id
-----------------------------------
 1  | x    | 1       | 0
 2  | y    | 1       | 0
 3  | z    | 0       | 1
 4  | t    | 0       | 2
 4  | u    | 0       | 2
 4  | o    | 0       | 2

I have two records which are admin and some other records which belong to one of these two records by matching the property_id with the id. In the end what I want is the admin row data and the count of its properties. The problem is that the data is all in the same table. This is what should be the output from the desired query.

  id | name | admin   | property_count
-----------------------------------
  1  | x    | 1       | 1
  2  | y    | 1       | 3

I hope I could explain myself clear enough. Thanks in advance.

回答1:

http://sqlfiddle.com/#!9/5ad1fb/4

SELECT u.*, COUNT(ut.id) property_count
FROM users_table u
LEFT JOIN users_table ut
ON u.id = ut.property_id
WHERE u.admin = 1
GROUP BY u.id, u.name, u.admin

回答2:

You seem to want a self-join and aggregation:

select t1a.id, t1a.name, t1a.admin, count(t1.id) as property_count
from table1 t1a left join
     table1 t1
     on t1a.id = t1.property_id
where t1a.admin = 1
group by t1a.id, t1a.name, t1a.admin;

There is, incidentally, a trickier way to do this without a join:

select (case when admin = 1 then id else property_id end) as id,
       max(case when admin = 1 then name end) as name,
       max(admin) as admin,
       sum( admin <> 1 ) as property_count
from table1 t1
group by (case when admin = 1 then id else property_id end);