在同一个表中查找与一行的多个匹配项，并根据结果从第二个表中获取结果 [英] Finding many matches to one row in the same table and getting results from a second table based on the results

问题描述

我知道问题标题可能不太清楚，但我试着解释一下:

I know that the question title may not be quit clear to understand but I try to explain:

users_table:

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

users_table 有两个或多个记录，它们是 admin 和一些其他记录属于这些 admin 记录之一，通过匹配 >property_id 和 id.最后我想要的是 admin 行数据和它的 properties 的 count.这应该是查询第一部分的输出:

users_table has two or more records which are admin and some other records which belong to one of these admin 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. This is what should be the output from the first part of the query:

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

直到现在我知道如何获得想要的结果，但问题开始了.我有另一个表

Until now I know how to get the desired results but here begins the problem.I have another table

sells_table:

id | seller_id | sell_amount
----------------------------
 1 | 3         | 250
 2 | 5         | 120
 3 | 7         | 100
 4 | 5         | 200

所以这就是逻辑:每个admin 都有很多property，每个property 都有很多sells.我想要 users_table 中每个 admin 的所有记录加上其 property_id 的计数.然后以一种方式查询sells_table，其中对于每个admin 的每个property，sells 的数量和<代码>sum 的总销售额被计算出来.例如，这应该是 admin 与 id 2 和 name y 的结果:

So this is the logic: every admin has many properties and each property has many sells. I want all records for each admin from the users_table plus the count of its property_id. And then query the sells_table in a way where for each property of each admin the number of sells and the sum of the total sells gets calculated. for example this should be the result for the admin with the id 2 and the name y:

  name | properties | property_sells | property_amount
  --------------------------------------------------------
   y   |    3       |     3          |   420

y 有 3 个属性.id 5 属于 y(admin) 的属性有 two sells 和 id 7y(admin)的code>有one卖，这3卖的sum是420.

y has 3 properties. Property with id 5 which belongs to y(admin) has two sells and id 7 which also belongs to y(admin) has one sell and the sum of these 3 sells is 420.

推荐答案

我想这就是你想要的:

select ua.id, ua.name, ua.admin, count(distinct u.id) as property_count,
       sum(s.sell_amount) as amount
from users_table ua left join
     users_table u
     on ua.id = u.property_id left join
     sales s
     on s.seller_id = u.id
where ua.admin = 1
group by ua.id, ua.name, ua.admin;

这篇关于在同一个表中查找与一行的多个匹配项，并根据结果从第二个表中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！