SQL查询以根据列值显示记录数

Question

我正在研究查询，其中我需要根据列值显示n个记录的数量。

例如。我的输入表如下

ITMREF_0    STOFCY_0    ITCSEQ_0    VLTTOT_0
110205        11          7          7.00000
110205        18          1          22.30130
110206        11          5          0.00000

输出我想要的是第三列中的任何值，对于第一列的不同列值。将重复第一列的列值。

例如。对于第1列（ITMREF_0），我们有两个不同的数字。110205 and 110206

对于110205，第三列中有两个不同的值，即7和1。这意味着110205的第二和第4列将从1重复1到7，然后为1

因此，输出应为

ITMREF_0    STOFCY_0    ITCSEQ_0    VLTTOT_0
110205        11          1        7.00000
110205        11          2        7.00000
110205        11          3        7.00000
110205        11          4        7.00000
110205        11          5        7.00000
110205        11          6        7.00000
110205        11          7        7.00000
110205        18          1        22.30130
110206        11          1        0.00000
110206        11          2        0.00000
110206        11          3        0.00000
110206        11          4        0.00000
110206        11          5        0.00000

Answer 1

with        cte as
            (
                select  * 
                from    mytable 
                union all   
                select  ITMREF_0,STOFCY_0,ITCSEQ_0 - 1,VLTTOT_0 
                from    cte 
                where   ITCSEQ_0 > 1
            )
select      * 
from        cte 
order by    ITMREF_0,STOFCY_0,ITCSEQ_0

---

+----------+----------+----------+----------+
| ITMREF_0 | STOFCY_0 | ITCSEQ_0 | VLTTOT_0 |
+----------+----------+----------+----------+
| 110205   | 11       | 1        | 7.00     |
+----------+----------+----------+----------+
| 110205   | 11       | 2        | 7.00     |
+----------+----------+----------+----------+
| 110205   | 11       | 3        | 7.00     |
+----------+----------+----------+----------+
| 110205   | 11       | 4        | 7.00     |
+----------+----------+----------+----------+
| 110205   | 11       | 5        | 7.00     |
+----------+----------+----------+----------+
| 110205   | 11       | 6        | 7.00     |
+----------+----------+----------+----------+
| 110205   | 11       | 7        | 7.00     |
+----------+----------+----------+----------+
| 110205   | 18       | 1        | 22.30    |
+----------+----------+----------+----------+
| 110206   | 11       | 1        | 0.00     |
+----------+----------+----------+----------+
| 110206   | 11       | 2        | 0.00     |
+----------+----------+----------+----------+
| 110206   | 11       | 3        | 0.00     |
+----------+----------+----------+----------+
| 110206   | 11       | 4        | 0.00     |
+----------+----------+----------+----------+
| 110206   | 11       | 5        | 0.00     |
+----------+----------+----------+----------+

Answer 2

这很容易使用数字表或数字cte：

rextester：http：//rextester.com/ucem72371

create table t (
    ITMREF_0 int 
  , STOFCY_0 int 
  , ITCSEQ_0 int 
  , VLTTOT_0 decimal(19,6) 
);
insert into t values 
    (110205 ,11 ,7 ,7) 
  , (110205 ,18 ,1 ,22.3013) 
  , (110206 ,11 ,5 ,0) ;

数字CTE：

  /* numbers table */
with a as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, numbers as (
  select n = row_number() over (order by (select 1))
    from         a as deka
      cross join a as hecto
      cross join a as kilo
      --cross join a as [tenK]
      --cross join a as [hundredk]
      --cross join a as mega
    )

查询：

select 
    t.ITMREF_0
  , t.STOFCY_0
  , ITCSEQ_0 = n
  , VLTTOT_0
from t 
  inner join numbers 
    on numbers.n <= t.ITCSEQ_0
order by 
    t.ITMREF_0
  , t.STOFCY_0
  , n

结果：

+----------+----------+----------+-----------+
| ITMREF_0 | STOFCY_0 | ITCSEQ_0 | VLTTOT_0  |
+----------+----------+----------+-----------+
|   110205 |       11 |        1 | 7,000000  |
|   110205 |       11 |        2 | 7,000000  |
|   110205 |       11 |        3 | 7,000000  |
|   110205 |       11 |        4 | 7,000000  |
|   110205 |       11 |        5 | 7,000000  |
|   110205 |       11 |        6 | 7,000000  |
|   110205 |       11 |        7 | 7,000000  |
|   110205 |       18 |        1 | 22,301300 |
|   110206 |       11 |        1 | 0,000000  |
|   110206 |       11 |        2 | 0,000000  |
|   110206 |       11 |        3 | 0,000000  |
|   110206 |       11 |        4 | 0,000000  |
|   110206 |       11 |        5 | 0,000000  |
+----------+----------+----------+-----------+

Answer 3

Select A.ITMREF_0
      ,A.STOFCY_0
      ,ITCSEQ_0 = B.N
      ,A.VLTTOT_0
 From  YourTable A
 Cross Apply (Select Top (A.ITCSEQ_0) N=Row_Number() Over (Order By Number) From master..spt_values ) b

返回

ITMREF_0    STOFCY_0    ITCSEQ_0    VLTTOT_0
110205      11          1           7.000000
110205      11          2           7.000000
110205      11          3           7.000000
110205      11          4           7.000000
110205      11          5           7.000000
110205      11          6           7.000000
110205      11          7           7.000000
110205      18          1           22.301300
110206      11          1           0.000000
110206      11          2           0.000000
110206      11          3           0.000000
110206      11          4           0.000000
110206      11          5           0.000000