在mysql的同一张表中找到许多匹配到一行 [英] Finding many matches to one row in the same table in mysql

问题描述

我的桌子是这个.

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

通过将property_id与id匹配，我有两个records分别是admin和一些其他records属于这两个记录之一. 最后，我要的是admin行数据及其properties的count. 问题在于数据全部在同一表中. 这就是所需查询的输出.

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

推荐答案

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

这篇关于在mysql的同一张表中找到许多匹配到一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！