SQL：如何计算MS ACCESS中的不同记录

Could anybody please help me on SQL command? I have a table (tbl_sActivity) that have below data

有人可以帮我解决SQL命令吗?我有一个表(tbl_sActivity)具有以下数据

user_id | client_id | act_status |
1           |     7        |      cold     |
1           |     7        |    dealed   |
22         |     5        |      cold     |
1           |     6        |      cold     |
1           |     6        |     warm    |
1           |     6        |      hot       |
1           |     6        |    dealed   |
1           |     8        |     warm    |
1           |     8        |    dealed   |
21         |     4        |     warm    |
21         |     4        |    dealed   |

user_id | client_id | act_status | 1 | 7 |冷| 1 | 7 | dealed | 22 | 5 |冷| 1 | 6 |冷| 1 | 6 |暖| 1 | 6 |热点| 1 | 6 | dealed | 1 | 8 |暖| 1 | 8 | dealed | 21 | 4 |暖| 21 | 4 | dealed |

The out put should be

输出应该是

user_id | Count_C_id |
 1          |     3             |
 21        |     1             |
 22        |     1             |

user_id | Count_C_id | 1 | 3 | 21 | 1 | 22 | 1 |

I've searched from net and learnt that MS ACCESS cannot use COUNT(DISTINCT) function.. so i stuck at this stage for days.. Thank you very much.

我从网上搜索并了解到MS ACCESS无法使用COUNT(DISTINCT)功能..所以我在这个阶段停留了好几天..非常感谢你。

1 个解决方案

#1

Try this one. The "trick" is to have a subquery first to get all the distinct combinations of user and client IDs and then do the grouping per user:

试试这个。 “技巧”是首先获得子查询以获取用户和客户端ID的所有不同组合,然后对每个用户进行分组:

SELECT
    user_id
  , COUNT(*) AS count_distinct_clients
FROM
    ( SELECT DISTINCT
          user_id, 
          client_id
      FROM tbl_sActivity
    ) AS tmp
GROUP BY
    user_id ;