-- 中奖情况分析函数
create function dbo.dltfnd -- drop function dbo.dltfnd
(@x varchar(20), --> 购买号码
@y varchar(20) --> 开奖号码
) returns varchar(10) --> 中奖结果
as
begin
declare @z varchar(10),@p varchar(20),@b varchar(20)
select @p=substring(@x,1,14),
@b=right(@x,5)
select @z=rtrim(count(1))
+'+'
+case when @b=right(@y,5) then '1' else '0' end
from
(select substring(a.s,b.number,charindex(' ',a.s+' ',b.number)-b.number) 'x'
from (select @p 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(' '+a.s,b.number,1)=' ') t
where charindex(' '+x+' ',' '+substring(@y,1,14)+' ',1)>0
return @z
end
--- 购买的号码
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
create table #tb1([notext] varchar(14),[blu1] varchar(2),[blu2] varchar(2))
insert #tb1
select '01 02 20 21 23','07','12' union all
select '01 02 20 21 24','07','12' union all
select '01 02 20 21 26','07','12' union all
select '01 02 20 21 27','07','12' union all
select '01 02 20 21 29','07','12' union all
select '01 02 20 21 32','07','12' union all
select '01 02 20 21 34','07','12' union all
select '01 02 20 21 35','07','12' union all
select '01 02 20 22 23','07','12' union all
select '01 02 20 22 24','07','12' union all
select '01 02 20 22 26','07','12' union all
select '01 02 20 22 27','07','12' union all
select '01 02 20 22 28','07','12'
if object_id('tempdb..#tj') is not null drop table #tj
if object_id('tempdb..#33') is not null drop table #33
go
----建立奖级表 #tj -- select * from #33 where 奖金>=1000 select * from #tj
create table #tj
(jname varchar(10), -- 奖级
jstatus varchar(100), -- 中奖条件
jamount int -- 奖金
)
insert into #tj
select '一等奖','5+2',10000000 union all
select '二等奖','5+1',307985 union all
select '三等奖','5+0,4+2',13062 union all
select '四等奖','4+1,3+2',200 union all
select '五等奖','4+0,3+1,2+2',10 union all
select '六等奖','3+0,1+2,2+1,0+2',5
----> 测试
declare @num varchar(20)
select @num='01 19 20 25 34+07 12' --> 开奖号码
select row_number() over(order by getdate()) 'ID',
NoText,BLU1,BLU2,NoText+'+'+BLU1+' '+BLU2 'Number',
dbo.dltfnd(NoText+'+'+BLU1+BLU2,@num) '中奖情况',
isnull(jname,'') '奖级',
isnull(jamount,0) '奖金'
FROM #tb1
outer apply
(select top 1 jname,jamount
from #tj c
where charindex(','+dbo.dltfnd(NoText+'+'+BLU1+BLU2,@num)+',',
','+jstatus+',',
1)>0) d
问题：如何修改代码才可以找到5+2的结果？
------解决思路----------------------
create function dbo.dltfnd -- drop function dbo.dltfnd
(@x varchar(20), --> 购买号码
@y varchar(20) --> 开奖号码
) returns varchar(10) --> 中奖结果
as
begin
declare @z varchar(10),@p varchar(20),@b varchar(20)
select @p=substring(@x,1,14),
@b=right(@x,5)
select @z=rtrim(count(1))
+'+'
+convert(varchar(10),
case when substring(@x,16,2)=substring(@y,16,2)
OR substring(@x,16,2)=substring(@y,19,2)
then 1 else 0 end
+
case when substring(@x,19,2)=substring(@y,16,2)
OR substring(@x,19,2)=substring(@y,19,2)
then 1 else 0 end
)
from
(select substring(a.s,b.number,charindex(' ',a.s+' ',b.number)-b.number) 'x'
from (select @p 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(' '+a.s,b.number,1)=' ') t
where charindex(' '+x+' ',' '+substring(@y,1,14)+' ',1)>0
return @z
end
--- 购买的号码
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
create table #tb1([notext] varchar(14),[blu1] varchar(2),[blu2] varchar(2))
insert #tb1
select '01 02 20 21 23','07','12' union all
select '01 02 20 21 24','07','12' union all
select '01 02 20 21 26','07','12' union all
select '01 02 20 21 27','07','12' union all
select '01 02 20 21 29','07','12' union all
select '01 02 20 21 32','07','12' union all
select '01 02 20 21 34','07','12' union all
select '01 02 20 21 35','07','12' union all
select '01 02 20 22 23','07','12' union all
select '01 02 20 22 24','07','12' union all
select '01 02 20 22 26','07','12' union all
select '01 02 20 22 27','07','12' union all
select '01 02 20 22 28','07','12'
if object_id('tempdb..#tj') is not null drop table #tj
if object_id('tempdb..#33') is not null drop table #33
go
----建立奖级表 #tj -- select * from #33 where 奖金>=1000 select * from #tj
create table #tj
(jname varchar(10), -- 奖级
jstatus varchar(100), -- 中奖条件
jamount int -- 奖金
)
insert into #tj
select '一等奖','5+2',10000000 union all
select '二等奖','5+1',307985 union all
select '三等奖','5+0,4+2',13062 union all
select '四等奖','4+1,3+2',200 union all
select '五等奖','4+0,3+1,2+2',10 union all
select '六等奖','3+0,1+2,2+1,0+2',5
----> 测试
declare @num varchar(20)
select @num='01 19 20 25 34+07 12' --> 开奖号码
select row_number() over(order by getdate()) 'ID',
NoText,BLU1,BLU2,NoText+'+'+BLU1+' '+BLU2 'Number',
dbo.dltfnd(NoText+'+'+BLU1+' '+BLU2,@num) '中奖情况',
isnull(jname,'') '奖级',
isnull(jamount,0) '奖金'
FROM #tb1
outer apply
(select top 1 jname,jamount
from #tj c
where charindex(','+dbo.dltfnd(NoText+'+'+BLU1+' '+BLU2,@num)+',',','+jstatus+',',1)>0) d
/*
1 01 02 20 21 23 07 12 01 02 20 21 23+07 12 2+2 五等奖 10
2 01 02 20 21 24 07 12 01 02 20 21 24+07 12 2+2 五等奖 10
3 01 02 20 21 26 07 12 01 02 20 21 26+07 12 2+2 五等奖 10
4 01 02 20 21 27 07 12 01 02 20 21 27+07 12 2+2 五等奖 10
5 01 02 20 21 29 07 12 01 02 20 21 29+07 12 2+2 五等奖 10
6 01 02 20 21 32 07 12 01 02 20 21 32+07 12 2+2 五等奖 10
7 01 02 20 21 34 07 12 01 02 20 21 34+07 12 3+2 四等奖 200
8 01 02 20 21 35 07 12 01 02 20 21 35+07 12 2+2 五等奖 10
9 01 02 20 22 23 07 12 01 02 20 22 23+07 12 2+2 五等奖 10
10 01 02 20 22 24 07 12 01 02 20 22 24+07 12 2+2 五等奖 10
11 01 02 20 22 26 07 12 01 02 20 22 26+07 12 2+2 五等奖 10
12 01 02 20 22 27 07 12 01 02 20 22 27+07 12 2+2 五等奖 10
13 01 02 20 22 28 07 12 01 02 20 22 28+07 12 2+2 五等奖 10
*/