PostgreSQL加入以使用generate_series对表进行非规范化 [英] PostgreSQL join to denormalize a table with generate_series

问题描述

我有这张桌子:

CREATE TABLE "mytable"
(  name text,  count integer );
INSERT INTO mytable VALUES ('john', 4),('mark',2),('albert',3);

我想以这种方式删除"行:

and I would like "denormlize" the rows in this way:

SELECT name FROM mytable JOIN generate_series(1,4) tmp(a) ON (a<=count)

因此，每个名称的行数等于count列:我有约翰4行，马克2行，阿尔伯特行3行. 但是，如果我不知道最高计数(在这种情况下为4)，则无法使用generate_series()函数.有一种方法可以不知道MAX(count)吗?

so I've a number of rows for each name equals to the count column: I've 4 rows with john, 2 with mark and 3 with albert. But i can't use the generate_series() function if I don't know the highest count (in this case 4). There is a way to do this without knowing the MAX(count) ?

推荐答案

select name, 
       generate_series(1,count)
from mytable;

Set返回函数可以在select列表中使用，并将与从基表中检索到的行进行交叉联接.

Set returning functions can be used in the select list and will do a cross join with the row retrieved from the base table.

我认为这是一个未记录的行为，将来可能会消失，但是我不确定(我记得在邮件列表中对此有一些讨论)

I think this is an undocumented behaviour that might go away in the future, but I'm not sure about that (I recall some discussion regarding this on the mailing list)

SQLFiddle示例

这篇关于PostgreSQL加入以使用generate_series对表进行非规范化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！