我有一个这样的表:
Device | Rating
-----------------
iPhone | 1
iPhone | 2
iPhone | 4
iPhone | 5
Android | 3
Android | 5
我想返回一张包含所有可能评分的表格。在这种情况下1 to 5
。所以我的结果表将是:
Device | Rating
-----------------
iPhone | 1
iPhone | 2
iPhone | 3
iPhone | 4
iPhone | 5
Android | 1
Android | 2
Android | 3
Android | 4
Android | 5
我尝试使用类似的东西:
SELECT a.device, b.rating
FROM device_rating_table a
RIGHT JOIN rating_lookup_table b
ON a.rating = b.rating
GROUP BY 1,2
;
但这不起作用。您能提出一个解决方案吗?
您可以cross join
列出数字在1到5之间的不同设备:
select d.device, r.rating
from (select distinct device from mytable) d
cross join generate_series(1, 5) as r(rating)
或者,如果您要根据表中的最小值和最大值生成评级范围:
select d.device, r.rating
from (select distinct device from mytable) d
cross join (select generate_series(min(rating), max(rating)) from mytable) as r(rating)
最后:如果表中的所有评分均已可用,则无需使用generate_series()
:
select d.device, r.rating
from (select distinct device from mytable) d
cross join (select distinct rating from mytable) r
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句