mysql - 按三个以上元素分组以显示所有可能的组合

Question

我有如下三个表格，我尝试按三个以上元素进行分组以显示所有可能的组合

Play
--------------------------------
id    typeId    periodId
--------------------------------
1     a       1         
2     b       1         
3     b       1         
4     b       1         
5     a       2         
6     b       1         
7     a       1         
8     b       2         


Period
-------------
periodId
-------------
1
2
3


Type
-------------
typeId
-------------
a
b
c

我尝试了这个，但它不起作用，我看到一些 NULL 值，但分组依据不起作用。

SELECT type, p, count(*) as superNiceCount
FROM Play 
RIGHT JOIN Period pp ON Play.periodId = Period.periodId
RIGHT JOIN Type tt ON Play.typeId = Type.typeId
GROUP BY tt.typeId, pp.periodId

预期结果是

-------------------------
type p  superNiceCount
-------------------------
a   1    2 
a   2    1
a   3    0
b   1    4
b   2    1
b   3    0
c   1    0
c   2    0
c   3    0

我怎样才能实现这一目标？

Answer 1

看看这是否有效

SELECT ty.typeId as type, pe.periodId as p, count(pl.id) as superNiceCount
FROM Period pe 
CROSS JOIN Type ty 
LEFT JOIN Play pl ON (pl.periodId = pe.periodId AND pl.typeId = ty.typeId)
GROUP BY ty.typeId, pe.periodId

如果不尝试

SELECT ty.typeId as type, pe.periodId as p, count(pl.id) as superNiceCount
FROM (
    SELECT * FROM
    Period pe 
    CROSS JOIN Type ty 
) as t1
LEFT JOIN Play pl ON (pl.periodId = t1.periodId AND pl.typeId = t1.typeId)
GROUP BY ty.typeId, pe.periodId