求一个视图!高手帮忙,郁闷很久了!
表:table1
id fieldname dy_field val
10 dw_id dw 1001
56 km_id km 9423
12 dw_id dw 2002
表:table2
km_id dw_id money
9423 1001 123.00
9424 2002 300.00
9423 2002 500.00
求一个视图,查询结果为(表1中没有的数据不能查出来)
km_iid dw_iid money
56 10 123.00
56 12 500.00
大侠帮帮忙了!!!!!!!!!!!!
[解决办法]
create table A(id int, fieldname varchar(10), dy_field varchar(10), val int)
insert A select 10, 'dw_id ', 'dw ', 1001
union all select 56, 'km_id ', 'km ', 9423
union all select 12, 'dw_id ', 'dw ', 2002
create table B(km_id int, dw_id int, [money] decimal(10,2))
insert B select 9423, 1001, 123.00
union all select 9424, 2002, 300.00
union all select 9423, 2002, 500.00
select
km_iid=tmp2.id,
dw_iid=tmp1.id,
[money]=B.[money]
from B
inner join A as tmp1 on (tmp1.fieldname= 'dw_id ' and tmp1.val=B.dw_id)
inner join A as tmp2 on (tmp2.fieldname= 'km_id ' and tmp2.val=B.km_id)
--result
km_iid dw_iid money
----------- ----------- ------------
56 10 123.00
56 12 500.00
(2 row(s) affected)
