计算多个值的不同行数 [英] Count number of distinct rows for multiple values

问题描述

让我们考虑一下这张表，它指定一个人购买房地产的次数.

Let's consider this table specifing how many times a person bought a property.

+--------+----------+
|  user  | property |
+--------+----------+
| john   | car      |
| john   | car      |
| john   | house    |
| peter  | car      |
| peter  | car      |
| amanda | house    |
| amanda | house    |
+--------+----------+

我需要知道一次购买汽车的次数，一次购买房屋的次数等等.

I need to know how many times a car was bought once, how many times a house was bought once, etc. Something like this:

+----------+---+---+
| property | 1 | 2 |
+----------+---+---+
| cars     | 4 | 2 |
| house    | 3 | 1 |
+----------+---+---+

• 购买了多少辆汽车? 四个，两个用于彼得，两个用于 john .
• 汽车两次被购买了几次? 两个，用于同一个人.
• 买了几次房子? 三个，两个用于 amanda ，一个用于 john .
• 多少次购房两次?仅一次，用于阿曼达
• How many times a car was bought? Four, two for peter and two for john.
• How many times a car was bought twice? Two, for the same guys.
• How many times a house was bought? Three, two for amanda and once for john.
• How many times a house was bought twice? Only once, for amanda

是否可以仅使用SQL查询来做到这一点?

• 我不在乎性能或骇人听闻的方式.
• 有两个以上的频率.

• 一个人可以购买固定时间的固定时间(5)，因此在查询中手动指定列是没有问题的.我的意思是做类似的事情没问题:

• I don't care about performance or hackish ways.
• There are more than two frequencies.

• There's a fixed set of time a person can buy a property (5) so it's not problem to specify the columns manually in the query. I mean there's not problem doing something like:

SELECT /* ... */ AS 1, /* ... */ AS 2, /* ... */, AS 3 /* ... */

推荐答案

SELECT DISTINCT @pr := prop,
    (SELECT COUNT(1) FROM tbl WHERE prop = @pr LIMIT 1),
    (SELECT COUNT(1) FROM 
        (SELECT *, COUNT(*) cnt
        FROM tbl
        GROUP BY usr, prop
        HAVING cnt = 2) as tmp
        WHERE `tmp`.prop = @pr LIMIT 1)
FROM tbl;

是的，这不是最好的方法.但是，嘿，您将获得所需的答案.

Yes, it is not the best method; but hey, you get the answers as desired.

此外，它将为表中的任何类型的属性生成结果.

Also, it'll generate the results for any kind of property in your table.

小提琴链接位于此处.

P.S .: 60次尝试O_O

P.S.: 60 tries O_O

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