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
ID NoText BLU1 BLU2 Number 中奖情况 奖级 奖金
1 01 02 20 21 23 07 12 01 02 20 21 23+07 12 2+0 0
2 01 02 20 21 24 07 12 01 02 20 21 24+07 12 2+0 0
3 01 02 20 21 26 07 12 01 02 20 21 26+07 12 2+0 0
4 01 02 20 21 27 07 12 01 02 20 21 27+07 12 2+0 0
5 01 02 20 21 29 07 12 01 02 20 21 29+07 12 2+0 0
6 01 02 20 21 32 07 12 01 02 20 21 32+07 12 2+0 0
7 01 02 20 21 34 07 12 01 02 20 21 34+07 12 3+0 0
8 01 02 20 21 35 07 12 01 02 20 21 35+07 12 2+0 0
9 01 02 20 22 23 07 12 01 02 20 22 23+07 12 2+0 0
10 01 02 20 22 24 07 12 01 02 20 22 24+07 12 2+0 0
11 01 02 20 22 26 07 12 01 02 20 22 26+07 12 2+0 0
12 01 02 20 22 27 07 12 01 02 20 22 27+07 12 2+0 0
13 01 02 20 22 28 07 12 01 02 20 22 28+07 12 2+0 0
ID NoText BLU1 BLU2 Number 中奖情况 奖级 奖金
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+0 六等奖 5
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
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
(@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
*/