逗号隔开的sql语句查询

[废话很多]

1、合同表:leaseview
字段:
合同的编号:leaseid
合同的所有铺位:stallitems
合同到期地:closedate

CREATE TABLE [dbo].leaseview(
(
leaseid [nvarchar](20) ,
stallitems [nvarchar](50) ,
closedate datetime
}
２、分区表Block
CREATE TABLE [dbo].[Block](
[BlockID] [nvarchar](10) ,
[BlockName] [nvarchar](30) )



３、商铺表Stall
StallID商铺编号，BlockID为所在区块
CREATE TABLE [dbo].[Stall](
[StallID] [nvarchar](20)
[BlockID] [nvarchar](8)

[Acreage] [decimal](18, 2)
)
４、字定义函数GetStallOfBlockID ,输入商铺号StallID,得到商铺所在区块

CREATE Function [dbo].[GetStallOfBlockID](@StallID Nvarchar(20))

Returns Nvarchar(20)
As
Begin
Declare @temp Nvarchar(20)
Set @temp=''
Select @temp=BlockID from Stall Where Stall.StallID=@StallID
Return @temp
End


合同表数据如下所示：
leaseid 　stallitems closedate
2007095F1 5F0083,5F0084 2008-12-31
2007095F2 1F0086,2F0088,5F0089 2008-12-31
2007095F3 3F0082 2008-12-31
2007095F4 4F0090,5F0011 2008-12-31
分区表Block数据如下所示：

BlockID BlockName
1f １楼
2f 2楼
3f　　　　３楼
4f 4楼
5f 5楼


现在需要查询某些的楼层的合同,比如(1楼，3楼)的合同
如何查？

[废话很多]

比如输入1f,3f
出现下面的结果
leaseid 　stallitems closedate
2007095F2 1F0086,2F0088,5F0089 2008-12-31
2007095F3 3F0082 2008-12-31

[nzperfect]

看不懂

[jacklinchen]

select * from leaseview where stallitems like '%1f%' or stallitems like '%3f%'

[LYH1235568]

mark
等待高手，这个逗号分开的SQL语句一前还没处理过

[wgzaaa]

declare @a varchar(50)
set @a='1f,3f'--给定条件
set @a='['+replace(replace(@a,'f,',''),'f',']f')
select * from leaseview where patindex('%'+@a+'%',stallitems)>0
------------------------------------
2007095F2 1F0086,2F0088,5F0089 2008-12-31 00:00:00.000
2007095F3 3F0082 2008-12-31 00:00:00.000
2007095F4 4F0090,5F0011 2008-12-31 00:00:00.000

[-狙击手-]

看不懂

[arrow_gx]

[Quote=引用 6 楼 happyflystone 的回复:]
看不懂
[/Quote]

[hery2002]

[Quote=引用 2 楼 perfectaction 的回复:]
看不懂
[/Quote]

[lidonsa]

楼主的原意是：当多楼层的合同查询，输入条件用＂,＂作为分隔符.5楼的为正解(使用patindex).
另leaseid最后两位与BlockID的编码是否有一定对应关系（如F1与1f），如在修改前人所写，必须确认下之前的开发文档是何定义的．

[-晴天]

select * from leaseview where stallitems in
  (select stallid from stall where blockid in
    (select blockid from block where blockname in('1楼','3楼')))

[-晴天]

CREATE TABLE [dbo].leaseview( 
leaseid [nvarchar](20) , 
stallitems  [nvarchar](50) , 
closedate datetime 
)
CREATE TABLE [dbo].[Stall]( 
[StallID] [nvarchar](20),
[BlockID] [nvarchar](8),
[Acreage] [decimal](18, 2) 
)
CREATE TABLE [dbo].[Block]( 
[BlockID] [nvarchar](10), 
[BlockName] [nvarchar](30)
)
go
CREATE Function [dbo].[GetStallOfBlockID](@StallID Nvarchar(20)) 
Returns Nvarchar(20) 
As 
Begin 
    Declare @temp Nvarchar(20) 
    Set @temp='' 
    Select @temp=BlockID from Stall Where Stall.StallID=@StallID 
    Return @temp 
End 
go
insert into leaseview
select '2007095F1','5F0083,5F0084','2008-12-31' union all
select '2007095F2','1F0086,2F0088,5F0089',  '2008-12-31' union all
select '2007095F3','3F0082',                '2008-12-31' union all
select '2007095F4','4F0090,5F0011',        '2008-12-31'

insert into block
select '1F','1楼' union all
select '2F','2楼' union all
select '3F','3楼' union all
select '4F','4楼' union all
select '5F','5楼'
go
SELECT * FROM leaseview where leaseid in(
select leaseid from block a,leaseview b where charindex(a.blockid,b.stallitems)>0 and a.blockname in('1楼','3楼')
)
go
drop table leaseview,block,stall
drop function dbo.GetStallOfBlockID
/*
leaseid              stallitems                                         closedate
-------------------- -------------------------------------------------- -----------------------
2007095F2            1F0086,2F0088,5F0089                               2008-12-31 00:00:00.000
2007095F3            3F0082                                             2008-12-31 00:00:00.000

(2 行受影响)*/