# 基于条件的重复行 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查询返回:

```+------+-----+-----+
|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  |
+------+-----+-----+
```

## 推荐答案

```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)
```

master.dbo.spt_values仅用于获取数字序列.可以在运行时或其他具有数字序列的表生成序列

## 其他推荐答案

```;WITH cte AS (
SELECT *
FROM (VALUES
(0, 'LG', 'SC', 1),
(1, 'WE', 'CC', 1),
(2, 'TY', 'PL', 2)
) as t([Index], F1, F2, Multiply)
), final AS (
SELECT  [Index],
F1,
F2,
Multiply*2 as Multiply
FROM cte  -- At first we get basic recordset with x2 in Multiply
UNION ALL
SELECT  [Index],
F1,
F2,
Multiply-1
FROM final  -- On each iteration we select columns we need and
WHERE Multiply-1 > 0   -- use Multiply as counter
)

SELECT  [Index],
F1,
F2
FROM final
ORDER by [Index]
```

```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

(8 rows affected)
```