sqlserver数据库中根据已知表结构创建一个视图
有一个表A，其结构如下：
id(int) fid(int) value(varchar) int_id
1 1 2 1
2 2 3 2
3 2 23 1
4 1 52 2
5 3 12 1
6 3 32 2
7 4 231 1
8 4 56 2
9 1 32 3
10 2 74 3
11 3 37 3
12 4 61 3
现在根据表A创建一个视图B，视图B里面包含A的所有数据，但是其中当int_id=3时 value的值为fid相同的int_id=2和int_id=1的value值的差值，除此以外视图B中还需增加int_id为4的记录，其value值为fid相等时int_id=1和int_id=2的value值的和。
请问用sql语句怎么表示
[解决办法]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-23 10:01:15
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]
(
[id] int,
[fid] int,
[value] int,
[int_id] int
)
insert [test]
select 1,1,2,1 union all
select 2,2,3,2 union all
select 3,2,23,1 union all
select 4,1,52,2 union all
select 5,3,12,1 union all
select 6,3,32,2 union all
select 7,4,231,1 union all
select 8,4,56,2 union all
select 9,1,32,3 union all
select 10,2,74,3 union all
select 11,3,37,3 union all
select 12,4,61,3
go
create view v_test
as
select
[id],
[fid],
[value]=case when [int_id]=3
then (select value from test b where b.fid=a.fid and b.int_id=2)
-(select value from test b where b.fid=a.fid and b.int_id=1)
else [value] end,
[int_id]
from
test a
go
select * from v_test
/*
id fid value int_id
----------- ----------- ----------- -----------
1 1 2 1
2 2 3 2
3 2 23 1
4 1 52 2
5 3 12 1
6 3 32 2
7 4 231 1
8 4 56 2
9 1 50 3
10 2 -20 3
11 3 20 3
12 4 -175 3
(12 行受影响)
*/
--不懂你增加的那个是什么意思
create view jack
as select [id],[fid],[int_id], (case [jack.value]
when [jack.int_id]=3
then [jack.value2]-[jack.value1]
when [jack.int_id]=4
then [jack.value2]+[jack.value1]
else "normal"
end)
from [jack]
