如何计算不同的记录 [英] How to count distinct records

问题描述

有人可以在SQL命令上为我提供帮助吗?

Could anybody please help me on SQL command?

我有一个表(tbl_sActivity)，其中包含以下数据:

I have a table (tbl_sActivity) that have below data:

user_id | client_id | act_status |
1        |            ||           |
1        |            ||   交易    |
22       |            ||           |
1        |            ||           |
1        |            ||         |
1        |            ||             |
1        |            ||   交易    |
1        |            ||         |
1        |            ||   交易    |
21       |            ||         |
21       |            ||   交易   |

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 | Count_C_id |
            |                          ||                 ||
  22       |                 |

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

我从网上搜索后得知，MS ACCESS无法使用COUNT(DISTINCT)函数.所以我在这个阶段停留了几天.

I've searched from net and learnt that MS ACCESS cannot use COUNT(DISTINCT) function. So I'm stuck at this stage for days.

推荐答案

尝试一下. 技巧"是首先有一个子查询，以获取用户ID和客户端ID的所有不同组合，然后对每个用户进行分组:

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:

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

这篇关于如何计算不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！