根据条件SQL复制行 [英] Duplicate a Row Based on a Condition SQL

问题描述

我有一个像这样的表

+-------+------+------+----------+ 
| Index |  F1  |  F2  | Multiply | 
+-------+------+------+----------+
|  0    |  LG  |  SC  |     1    |
|  1    |  WE  |  CC  |     1    |
|  2    |  TY  |  PL  |     2    |
+-------+------+------+----------+

我想将乘列乘以2，以确定要添加到结果中的行数。在上面的示例中，我希望我的SQL查询返回：

And I want to multiply the 'Multiply' Column by 2 to determine how many rows to add to the result. With the example above, I want my SQL Query to return:

+------+-----+-----+
|Index | F1  | F2  |
+------+-----+-----+
|  0   |  LG | SC  | 
|  0   |  LG | SC  |
|  1   |  WE | CC  |
|  1   |  WE | CC  |
|  2   |  TY | PL  |
|  2   |  TY | PL  |
|  2   |  TY | PL  |
|  2   |  TY | PL  |
+------+-----+-----+

我没有编写SQL查询的丰富经验，因此将不胜感激。

I don't have much experience writing SQL queries so help would be much appreciated.

推荐答案

尝试一下

with myTable as (
    select * from (values 
        (0,'LG','SC',1)
        ,(1,'WE','CC',1)
        ,(2,'TY','PL',2)
    ) t ([Index], F1, F2, Mutiply)
)
select
    [Index], F1, F2
from
    myTable m
    join master.dbo.spt_values v on m.Mutiply * 2 > v.number
where
    Type = 'P'

编辑：添加一些描述

通过编写类似

select * from table 
cross join (values (1),(2)) t(n)

我们可以表中的双行。并且交叉连接（值（1），（2），（3），（4））t（n）将使行增加四倍。在这种情况下，行乘法将取决于列 Multiply 。因此，我们需要将列 Multiply 的2值相乘，并与具有数字序列（0,1,2,3,4,5,6,7。 。）数字小于计算值。例如，对于 Multiply = 2 ，联接条件将是小于2 * 2的4个数字（0、1、2、3）。这4个数字将使初始行变成四倍。

we can double rows in the table. And cross join (values (1),(2),(3),(4)) t(n) will quadruple the rows. In this case row multiplication will depend on column Multiply. So we need to multiply by 2 value of column Multiply and join with table with sequence of numbers (0,1,2,3,4,5,6,7...) where number is less than calculated value. For example, for Multiply = 2, join condition will be 4 numbers (0, 1, 2, 3) which are less than 2*2. And those 4 numbers will quadruple initial row.

master.dbo.spt_values 仅用于获取数字序列。序列可以在运行时生成，也可以使用另一个带有数字序列的表

master.dbo.spt_values is only used to get numbers sequence. Sequence can be generated at runtime or another table with numbers sequence can be used

这篇关于根据条件SQL复制行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！