mysql - 选择接收的照片多于发送的照片的用户

我正在努力与此SQL查询。说我有这两张桌子

**USERS**
+----+-------+
| id | name  |
+----+-------+
|  1 | james |
|  2 | tom   |
|  3 | kate  |
+----+-------+

**PHOTOS**
+-----------+-----------+---------+
|   name    | sent_from | sent_to |
+-----------+-----------+---------+
| beach.jpg |         1 |       2 |
| trees.jpg |         3 |       1 |
| earth.jpg |         2 |       1 |
+-----------+-----------+---------+

我如何使用一个SQL查询获得与ID关联的send_to大于send_from的所有用户？

最佳答案

我认为这是两次汇总数据，然后进行比较：

select sf.sent_from
from (select sent_from, count(*) as numsent
      from photos
      group by sent_from
     ) sf left outer join
     (select sent_to, count(*) as numrecv
      from photos
      group by sent_to
     ) st
     on sf.sent_from, st.sent_to
where numsent > numrecv;

如果您需要用户信息，请加入。

另一种方法是先重组数据，然后再进行聚合：

select who
from (select sent_from as who, 1 as sent_from, 0 as sent_to
      from photos
      union all
      select sent_to as who, 0, 1
      from photos
     ) p
group by who
having sum(sent_from) > sum(sent_to);

关于mysql - 选择接收的照片多于发送的照片的用户，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23309516/

AS

mysql - 选择接收的照片多于发送的照片的用户