求依据规则创建一视图_Sql Server

求根据规则创建一视图
有一个表odd有3个列,
分别是 Name, fNum, lNum
另外还有一个表bill,其中有一个列 Num
现在我想创建一个视图,规则如下:

当Num属于某行fNum和lNum之间(fNum<=Num<=lNum),就返回这行的Name
例如:
odd
Name fNum LNum
A 1 5
B 6 10
C 11 15
D 16 20

bill
... Num
... 3
... 7
... 13
... 18
得到的视图
view
... Num Name
... 3 A
... 7 B
... 13 C
... 18 D

------解决方案--------------------

SQL code

--> 测试数据：[odd]IF OBJECT_ID('[odd]') IS NOT NULL DROP TABLE [odd]GO CREATE TABLE [odd]([Name] VARCHAR(1),[fNum] INT,[LNum] INT)INSERT [odd]SELECT 'A',1,5 UNION ALLSELECT 'B',6,10 UNION ALLSELECT 'C',11,15 UNION ALLSELECT 'D',16,20--> 测试数据：[bill]IF OBJECT_ID('[bill]') IS NOT NULL DROP TABLE [bill]GO CREATE TABLE [bill]([Num] INT)INSERT [bill]SELECT 3 UNION ALLSELECT 7 UNION ALLSELECT 13 UNION ALLSELECT 18--------------开始查询--------------------------IF OBJECT_ID('[vv]') IS NOT NULL DROP VIEW [vv]GO CREATE VIEW vv AS SELECT a.[Name],b.[Num] FROM [odd] a, [bill] bWHERE b.[Num] BETWEEN a.[fNum] AND a.[LNum]GO SELECT * FROM vv----------------结果----------------------------/* Name    NumA    3B    7C    13D    18*/
------解决方案--------------------
SQL codecreate view V_odd_billasselect b.Num,a.Name from odd a, bill b where a.id=b.id and b.Num>a.fNum and b.Num<a.LNum go
------解决方案--------------------
探讨

SQL code
create view V_odd_bill
as
select b.Num,a.Name from odd a, bill b where a.id=b.id and b.Num>a.fNum and b.Num<a.LNum 
go

------解决方案--------------------
SQL codeuse tempdb if OBJECT_ID('tb1')=NULL drop table ta1goif OBJECT_ID('tb2')=NULL drop table ta2create table tb1 (name nvarchar(10) ,fNum int , LNum int)insert into tb1 select 'A',1,5union allselect 'B',6,10union allselect 'C',11,15union allselect 'D',16,20CREATE TABLE TB2 (NUM INT)INSERT TB2SELECT 3UNION ALLSELECT 7UNION ALLSELECT 13UNION ALLSELECT 18SELECT * ,(SELECT name FROM tb1 WHERE NUM BETWEEN fNum AND LNum)FROM TB2