Geek asks and answers

Compute multiple columns with each other using CTE

I am very new to this site, to all. And I did not know how to ask. So I asked 1 problem earlier (I sent it as an answer), and I posted the solution again, just like me. All your ideas helped me solve this problem.

Now a new problem has appeared.

I want to build columns that compute with each other. (Sorry for my English) Example:

Id   Column1    Column2                                                           Column3
1    5          5 => Same as Column1                                              5 => Same as Column2
2    2          12 => column1 current + column2.prev + column3.previous = 2+5+5   17 => column2.current + column3.prev = 12+5
3    3          32 => 3+12+17                                                     49 => 32+17

easier way to see:

Id   Column1    Column2                  Column3
1    5          5 => Same as Column1     5 => Same as Column2
2    2          12 =>   2+5+5            17 => 12+5
3    3          32 =>   3+12+17          49 => 32+17

so difficult???: - (

The previous problem was calculating column 3 with the new calculated column as with column2. But now it should be updated with the just calculated Column2 column and the previous Column3 record. If you want to see the previous post, here it is .

, .

, , , .

, .

CTE. 1-, 2 (c.Column2) cteCalculation, 3 cte2 2 cteCalculation.

/ /

;with cteCalculation as (
    select t.Id, t.Column1, t.Column1 as Column2
        from table_1 t
        where t.Id = 1
    union all
    select t.Id, t.Column1, (t.Column1 + c.Column2) as Column2
        from table_1 t
            inner join cteCalculation c
                on t.Id-1 = c.id
),
cte2 as(
select t.Id, t.Column1 as Column3
        from table_1 t
        where t.Id = 1
    union all
    select t.Id, (select column2+1 from cteCalculation c where c.id = t.id)  as Column3
        from table_1 t
            inner join cte2 c2
                on t.Id-1 = c2.id
)

select c.Id, c.Column1, c.Column2, c2.column3
    from cteCalculation c
inner join cte2 c2 on c.id = c2. id

, 2 . , 2- 3- 3- 2- . , .

+3
3

, , CTE

create table #tmp (id int identity (1,1), Column1 int)
insert into #tmp values(5)
insert into #tmp values(2)
insert into #tmp values(3);

with counter as
(
    SELECT top 1 id, Column1, Column1 as Column2, Column1 as Column3 from #tmp
    UNION ALL 
    SELECT t.id, t.Column1, 
           t.Column1 + counter.Column2 + counter.Column3, 
           (t.Column1 + counter.Column2 + counter.Column3) + counter.Column3 FROM counter
    INNER JOIN #tmp t ON t.id =  counter.id + 1
)

select * from counter
+1

CTE, .

. Column1. ( CTE) Column2 ..

0

. , 1 .

col2 = col1 + col2 + col 3 col3 = col2 + col3 col3 = ( col1 + col2 + col 3) + col3

, INSTEAD OF Insert, , .

 CREATE TRIGGER tr_xxxxx ON Tablename

 INSTEAD OF INSERT

 AS

 INSERT INTO Tablename (Column1, Column2, Column3)
 SELECT ins.col1, ins.col1+t.col2+t.col3, ins.col1+t.col2+t.col3+t.col3
 FROM Tablename t INNER JOIN Inserted ins on t.Id = ins.Id

() Tablename t, (Inserted.col1).

0

More articles:


All Articles