我的数据库中有一个这样的表：
id --- owner --- product ---- type
0 --- john --- mustang ---- car
1 --- tim --- a360 ---- plane
2 --- john --- camry ---- car
3 --- dan --- a380 ---- plane
4 --- tim --- ninja ---- bike
5 --- dan --- accord ---- car
我正在试着得到一个所有者拥有的每种类型的编号。类似这样的东西：
John
Car = 2
Plane = 0
Bike = 0
-------------
Tim
Car = 0
Plane = 1
Bike = 1
-------------
Dan
Car = 1
Plane = 1
Bike = 0
-------------
我一直无法解决这个问题。
另一个问题是我的数据库能够接受新的类型。例如，有人可以添加一辆自行车作为一种类型。
有办法做到这一点吗？
我处理这个问题的方式有点棘手。
我首先创建了一个结果集，该结果集使用笛卡尔乘积为每个人和类型组合获得一行。
SELECT m.owner, t.type
FROM myTypes t, myTable m
GROUP BY m.owner, t.type
然后，我制作了另一个结果集，获取所有者、类型以及该所有者的每个类型的编号。但是，这只返回了现有所有者类型组合的行。它不会返回任何值，比如"John"answers"Plane"，因为他没有飞机产品。
SELECT m.owner, t.type, COUNT(*) as numOfType
FROM myTypes t
JOIN myTable m ON t.type = m.type
GROUP BY t.type, m.owner;
最后，我使用外部联接将这两个表联接在一起，这样我就收到了owner-type组合表中的每一行。当然，有些行的计数返回null，所以我不得不使用IFNULL将它们替换为0。这与您问题中的结果相匹配。
SELECT w1.owner, w1.type, IFNULL(w2.numOfType, 0) AS numOfType
FROM (SELECT m.owner, t.type
FROM myTypes t, myTable m
GROUP BY m.owner, t.type) w1
LEFT JOIN (SELECT m.owner, t.type, COUNT(*) as numOfType
FROM myTypes t
JOIN myTable m ON t.type = m.type
GROUP BY t.type, m.owner) w2
ON w1.owner = w2.owner AND w1.type = w2.type;
这是SQL Fiddle。
要解决这个问题，首先必须生成输出行，然后获取计数：
select o.owner, p.product, count(t.owner)
from (select distinct owner from table) o cross join
(select distinct product from table) p left join
table t
on t.owner = o.owner and t.product = p.product
group by o.owner, p.product;
如果您有所有者和产品的引用表，那么您可以使用这些表而不是select distinct子查询。
编辑：
按类型分组基本上是相同的想法：
select o.owner, ty.type, count(t.owner)
from (select distinct owner from table) o cross join
(select distinct type from table) ty left join
table t
on t.owner = o.owner and t.type = ty.type
group by o.owner, ty.type;