根据 id 从表中的列中查找重复项，它们执行了多少事务

Question

我需要根据user_id他们根据borrow_id从表中的列中查找重复项

所以表格看起来像：

borrow_progress
+--------+-----------+------------+------------+
|user_id | borrow_id |  state     |  remark    | 
+--------+-----------+------------+------------+
|  170   |  236      |  10        |  waiting   |    
|  170   |  236      |  22        |  proceed   |      
|  170   |  236      |  26        |  success   |     
|  170   |  236      |  30        |  sent      |    
|  172   |  237      |  10        |  waiting   |   
|  172   |  237      |  22        |  proceed   | 
|  172   |  237      |  90        |  fail      |
|  170   |  238      |  10        |  waiting   | 
|  170   |  238      |  22        |  proceed   | 
|  170   |  238      |  90        |  fail      | 
|  173   |  239      |  10        |  waiting   | 
|  173   |  239      |  22        |  proceed   | 
|  173   |  239      |  26        |  success   |
|  170   |  240      |  10        |  waiting   |   
| ...    | ...       |  ...       |  ...       |  
+--------+-----------+------------+------------+

---

我的预期输出是：

1.

+--------+------------------+
|user_id | count(borrow_id) |
+--------+------------------+
|  170   |         3        |       
|  172   |         1        |      
|  173   |         1        |     
+--------+------------------+

阿拉伯数字。

+--------+------------------+
|user_id |        borrow_id |
+--------+------------------+
|  170   |         236      |
|  170   |         238      |
|  170   |         240      |       
|  172   |         237      |      
|  173   |         239      |     
+--------+------------------+

Answer 1

您可以在此处使用DISTINCT，用于两个查询：

SELECT
user_id,
COUNT(DISTINCT borrow_id) AS cnt
FROM borrow_progress
GROUP BY
user_id
ORDER BY
user_id;

和：

SELECT DISTINCT
user_id,
borrow_id
FROM borrow_progress
ORDER BY
user_id,
borrow_id;

Answer 2

我收集了这两个查询

select user_id, count(distinct borrow_id ) 
from borrow_progress
group by user_id

select user_id,  borrow_id
from borrow_progress
group by user_id,  borrow_id