需要有关Oracle SQL查询的帮助

I have a table x which is returning 20 rows and table x have 6 columns and now I have one more table y which have only one column with 5 rows now i would like to return all rows and all columns of x plus single column rows of table y and all these 5 values of y should be repeated 4 times because table x have 20 rows. And if table x have 18 rows then In last iteration of table y only 3 values should be repeated .

我有一个表x,它返回20行,表x有6列,现在我还有一个表y只有一列有5行现在我想返回所有行和x的所有列加上单列的行表y和y的所有这5个值应重复4次,因为表x有20行。如果表x有18行,那么在表y的最后一次迭代中,只应重复3个值。

Example : Table x:

示例:表x:

Id name
1  peter
2  john
3  robin
4  amy
5  joseph
6  king
7  brain
8  nancy

Now table y:

现在表y:

Rank
X
Y
Z

Final output I want,

我想要的最终输出,

Id name        rank
1  peter       X
2  john        Y
3  robin       Z
4  amy         X
5  joseph      Y
6  king        Z
7  brain       X
8  nancy       Y

I will appreciate for your help ,

我将感谢您的帮助,

Thanks and regards, Vijay dubey

谢谢和问候,Vijay dubey

1 个解决方案

#1

You can use row_number() and modular arithmetic:

您可以使用row_number()和模块化算法:

select x.*, y.rank
from (select x.*, row_number() over (order by id) as seqnum
      from x
     ) x join
     (select y.*, row_number() over (order by id) as seqnum,
             count(*) over () as cnt
      from y
     ) y
     on mod(x.seqnum, y.cnt) = mod(y.seqnum, y.cnt);