计算多个值的不同行数 [英] 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
- 我不在乎性能或骇人听闻的方式.
- 有两个以上的频率.
-
一个人可以购买固定时间的固定时间(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:
是否可以仅使用SQL查询来做到这一点?
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屋!