MySQL Count all和Count distinct超过2列相同的查询

Question

目前，我使用下面的查询来计算购买某项商品的不同(客户用户名和商店id)的行数。如何计算同一查询中item_id =3的总(所有)行？

SELECT count(*)
FROM (
SELECT DISTINCT customer_username, store_id
FROM transactions
WHERE item_id = 3)

Answer 1

您可以尝试如下所示：

SELECT COUNT(*), SUM(customer_cnt) FROM (
    SELECT customer_username, store_id, COUNT(*) AS customer_cnt
      FROM transactions
     WHERE item_id = 3
     GROUP BY customer_username, store_id
) t;

Answer 2

尝试删除查询中的DISTINCT：

SELECT count(*)
FROM (
SELECT customer_username, store_id
FROM transactions
WHERE item_id = 3)

Answer 3

另一种方式是两个内联视图的cross join：

select x.distinct_rows, y.total_rows
  from (select count(distinct concat(user_name, store_id)) as distinct_rows
          from transactions
         where item_id = 3) x
 cross join (select count(*) as total_rows
               from transactions
              where item_id = 3) y