create table tb061602(id int identity(1,1),a int,b int,c int) insert into tb061602(a,b,c) select 1,2,3 union all select null,null,null union all select 4,null,null union all select null,null,null union all select 5,null,null union all select null,null,null union all select 6,null,null
create function xxx_uf ( @id int ) returns int begin declare @xx int
;with cte as ( select id,a,b from tb061602 where id=@id-2 union all select b.id,b.a,b.b from cte a inner join tb061602 b on a.id-2=b.id ) select @xx=sum(isnull(a,0))+sum(isnull(b,0)) from cte return @xx end select a,case when id>=3 and id<>100 then dbo.xxx_uf(id) when id=100 then dbo.xxx_uf(8) else b end b, a+case when id>=3 and id<>100 then dbo.xxx_uf(id) when id=100 then dbo.xxx_uf(8) else b end c from tb061602 /* a b c ----------- ----------- ----------- 1 2 3 NULL NULL NULL 4 3 7 NULL 0 NULL 5 7 12 NULL 0 NULL 6 12 18(7 行受影响) */
select a,case when a.id>2 and a.id%2=1 then (select c from tb061602 where id=a.id-2) when a.id=100 then (select c from tb061602 where id=8) else b end b, (a+case when a.id>2 and a.id%2=1 then (select c from tb061602 where id=a.id-2) when a.id=100 then (select c from tb061602 where id=8) else b end) c from tb061602 a
create table tb061602(id int identity(1,1),a int,b int,c int)
insert into tb061602(a,b,c)
select 1,2,3 union all
select null,null,null union all
select 4,null,null union all
select null,null,null union all
select 5,null,null union all
select null,null,null union all
select 6,null,null
create function xxx_uf
(
@id int
)
returns int
begin
declare @xx int
;with cte
as
(
select id,a,b from tb061602 where id=@id-2
union all
select b.id,b.a,b.b from cte a inner join tb061602 b on a.id-2=b.id
)
select @xx=sum(isnull(a,0))+sum(isnull(b,0)) from cte
return @xx
end
select a,case when id>=3 and id<>100 then dbo.xxx_uf(id) when id=100 then dbo.xxx_uf(8) else b end b,
a+case when id>=3 and id<>100 then dbo.xxx_uf(id) when id=100 then dbo.xxx_uf(8) else b end c
from tb061602
/*
a b c
----------- ----------- -----------
1 2 3
NULL NULL NULL
4 3 7
NULL 0 NULL
5 7 12
NULL 0 NULL
6 12 18(7 行受影响)
*/
1526 10 12 44 44
1527 13 151 119 119
1530 4 8 67 214
1532 8 1 100 200
1533 5 99 16 11
1535 3 1 187 187
1536 2 82 100 100
1538 8 40 100 200
1539 8 79 200 200
1540 3 11 187 187
1541 7 69 111 213
1542 5 50 15 11
1543 8 35 200 200
1544 10 8 44 44
1545 5 9 10 11
1546 10 12 44 44
1548 5 17 10 11
1549 3 0 187 187
1550 3 4 187 187
1551 3 13 187 187
1552 13 1 119 119
1554 7 25 111 212
1555 6 39 175 175
1556 8 66 200 200
1557 4 102 15 213
1558 10 101 44 200
1559 10 40 44 44这里类别为3的A、B、C三列数据要按上面说的的公式关系处理掉,因为目前这里的数据是错误的。
现在应该可以看到数据结构了吧,再看不到我真要跳楼了。
select a,case when a.id>2 and a.id%2=1 then (select c from tb061602 where id=a.id-2)
when a.id=100 then (select c from tb061602 where id=8) else b end b,
(a+case when a.id>2 and a.id%2=1 then (select c from tb061602 where id=a.id-2)
when a.id=100 then (select c from tb061602 where id=8) else b end) c from tb061602 a
序号 类别 A B C
1526 10 12 44 44
1527 13 151 119 119
1530 4 8 67 214
1532 8 1 100 200
1533 5 99 16 11
1535 3 1 187 187
1536 2 82 100 100
1538 8 40 100 200
1539 8 79 200 200
1540 3 11 187 187
1541 7 69 111 213
1542 5 50 15 11
1543 8 35 200 200
1544 10 8 44 44
1545 5 9 10 11
1546 10 12 44 44
1548 5 17 10 11
1549 3 0 187 187
1550 3 4 187 187
1551 3 13 187 187
1552 13 1 119 119
1554 7 25 111 212
1555 6 39 175 175
1556 8 66 200 200
1557 4 102 15 213
1558 10 101 44 200
1559 10 40 44 44
这里类别为3的A、B、C三列数据要按上面说的的公式关系处理掉,因为目前这里的数据是错误的。是要计算,不是要查询结果啊。
1526 10 12 44 44
1527 13 151 119 119
1530 4 8 67 214
1532 8 1 100 200
1533 5 99 16 11
1535 3 1 187 187
1536 2 82 100 100
1538 8 40 100 200
1539 8 79 200 200
1540 3 11 187 187
1541 7 69 111 213
1542 5 50 15 11
1543 8 35 200 200
1544 10 8 44 44
1545 5 9 10 11
1546 10 12 44 44
1548 5 17 10 11
1549 3 0 187 187
1550 3 4 187 187
1551 3 13 187 187
1552 13 1 119 119
1554 7 25 111 212
1555 6 39 175 175
1556 8 66 200 200
1557 4 102 15 213
1558 10 101 44 200
1559 10 40 44 44
这里类别为3的A、B、C三列数据要按上面说的的公式关系处理掉,因为目前这里的数据是错误的。是要计算,不是要查询结果啊。