例如开奖号码为
08 16 18 25 26 27 + 06 而我买了如下的号码,如何用SQL语句查询这些号码中了什么级别的奖呢?
08 16 18 19 21 30+06
08 13 18 21 27 32+06
08 18 19 22 27 30+06
08 11 16 18 26 32+06
08 11 18 20 23 27+06
11 18 26 30 31 32+06
08 16 18 25 26 30+06
11 17 25 26 27 30+06
08 15 18 21 22 30+06
11 14 18 20 26 30+06
08 11 18 20 22 23+06
11 13 16 21 26 30+06
11 13 18 20 21 26+06
08 10 11 18 26 29+06
08 10 11 18 26 31+06
08 09 10 11 18 26+06
11 18 21 27 29 30+06
08 11 22 23 27 30+06
11 18 25 26 27 30+06
11 21 25 26 28 30+06
11 18 19 22 28 30+06
11 18 21 24 27 30+06
11 13 14 18 26 30+06
08 13 14 18 31 32+06
08 13 18 26 29 31+06
08 13 19 22 27 30+06
08 09 11 18 29 32+06
08 09 11 19 21 30+06
11 13 14 18 29 31+06
11 12 20 25 26 30+06
08 11 25 26 27 30+06
11 13 18 22 26 30+06
08 13 16 18 19 21+06
08 16 18 25 26 27+06
08 10 13 18 26 28+06
08 12 13 18 19 33+06
11 13 18 23 29 31+06
08 11 13 18 19 23+06
08 11 13 24 29 31+06
11 13 16 17 18 24+06
11 12 13 17 18 23+06
08 09 11 12 13 19+06
11 13 16 18 28 31+06
08 11 13 24 28 31+06
11 12 13 17 18 33+06
08 11 13 16 24 26+06
08 09 11 25 28 31+06
11 12 13 25 26 31+06
11 18 23 24 26 30+06
11 13 16 25 28 29+06
08 09 11 25 29 31+06
11 13 18 27 31 33+06
08 11 13 15 26 31+06
11 12 13 15 17 18+06
08 09 11 25 26 31+06
08 11 17 25 28 29+06
08 11 12 13 16 33+06
11 13 28 30 31 33+06
08 11 23 25 31 32+06
11 13 15 17 18 25+06
08 11 12 13 17 25+06
11 12 13 15 18 25+06
11 18 23 25 26 31+06
11 12 13 21 23 25+06
08 11 12 13 19 33+06
08 11 13 15 29 31+06
08 11 16 17 24 25+06
08 13 17 22 23 25+06
08 11 13 25 29 31+06
13 16 18 25 27 33+06
11 12 13 17 23 25+06
08 09 11 25 27 31+06
08 11 16 19 24 25+06
08 09 11 12 13 31+06
11 13 15 19 21 25+06
08 11 13 27 31 33+06补充说明:
中奖级别是:
一等奖为6+1 -----(6个红球和1个蓝球)
二等奖为6+0 -----6个红球相符
三等奖为5+1 ----5个红球和1个蓝球相符
四等奖为5+0或者4+1 ----5个红球或4个红球和1个蓝球号码相符
五等奖为3+1或者4+0 ---4个红球或3个红球+1个蓝球号码相符
六等奖为2+1或者1+1或者0+1 ------1个蓝球号码相符(有1/2/无红球号码相符均可)
08 16 18 25 26 27 + 06 而我买了如下的号码,如何用SQL语句查询这些号码中了什么级别的奖呢?
08 16 18 19 21 30+06
08 13 18 21 27 32+06
08 18 19 22 27 30+06
08 11 16 18 26 32+06
08 11 18 20 23 27+06
11 18 26 30 31 32+06
08 16 18 25 26 30+06
11 17 25 26 27 30+06
08 15 18 21 22 30+06
11 14 18 20 26 30+06
08 11 18 20 22 23+06
11 13 16 21 26 30+06
11 13 18 20 21 26+06
08 10 11 18 26 29+06
08 10 11 18 26 31+06
08 09 10 11 18 26+06
11 18 21 27 29 30+06
08 11 22 23 27 30+06
11 18 25 26 27 30+06
11 21 25 26 28 30+06
11 18 19 22 28 30+06
11 18 21 24 27 30+06
11 13 14 18 26 30+06
08 13 14 18 31 32+06
08 13 18 26 29 31+06
08 13 19 22 27 30+06
08 09 11 18 29 32+06
08 09 11 19 21 30+06
11 13 14 18 29 31+06
11 12 20 25 26 30+06
08 11 25 26 27 30+06
11 13 18 22 26 30+06
08 13 16 18 19 21+06
08 16 18 25 26 27+06
08 10 13 18 26 28+06
08 12 13 18 19 33+06
11 13 18 23 29 31+06
08 11 13 18 19 23+06
08 11 13 24 29 31+06
11 13 16 17 18 24+06
11 12 13 17 18 23+06
08 09 11 12 13 19+06
11 13 16 18 28 31+06
08 11 13 24 28 31+06
11 12 13 17 18 33+06
08 11 13 16 24 26+06
08 09 11 25 28 31+06
11 12 13 25 26 31+06
11 18 23 24 26 30+06
11 13 16 25 28 29+06
08 09 11 25 29 31+06
11 13 18 27 31 33+06
08 11 13 15 26 31+06
11 12 13 15 17 18+06
08 09 11 25 26 31+06
08 11 17 25 28 29+06
08 11 12 13 16 33+06
11 13 28 30 31 33+06
08 11 23 25 31 32+06
11 13 15 17 18 25+06
08 11 12 13 17 25+06
11 12 13 15 18 25+06
11 18 23 25 26 31+06
11 12 13 21 23 25+06
08 11 12 13 19 33+06
08 11 13 15 29 31+06
08 11 16 17 24 25+06
08 13 17 22 23 25+06
08 11 13 25 29 31+06
13 16 18 25 27 33+06
11 12 13 17 23 25+06
08 09 11 25 27 31+06
08 11 16 19 24 25+06
08 09 11 12 13 31+06
11 13 15 19 21 25+06
08 11 13 27 31 33+06补充说明:
中奖级别是:
一等奖为6+1 -----(6个红球和1个蓝球)
二等奖为6+0 -----6个红球相符
三等奖为5+1 ----5个红球和1个蓝球相符
四等奖为5+0或者4+1 ----5个红球或4个红球和1个蓝球号码相符
五等奖为3+1或者4+0 ---4个红球或3个红球+1个蓝球号码相符
六等奖为2+1或者1+1或者0+1 ------1个蓝球号码相符(有1/2/无红球号码相符均可)
SQL只适合集操作,不适合行操作。
例如开奖号码为
08 16 18 25 26 27
而我买了如下的号码,如何用SQL语句查询这些号码中了几个?
08 16 18 19 21 30 ---3个同号
08 13 18 21 27 32 ---3个同号
08 18 19 22 27 30 ---3个同号
08 11 16 18 26 32 --以此类推
08 11 18 20 23 27
11 18 26 30 31 32
08 16 18 25 26 30
11 17 25 26 27 30
08 15 18 21 22 30
11 14 18 20 26 30
08 11 18 20 22 23
11 13 16 21 26 30
11 13 18 20 21 26
08 10 11 18 26 29
08 10 11 18 26 31
08 09 10 11 18 26
11 18 21 27 29 30
08 11 22 23 27 30
11 18 25 26 27 30
11 21 25 26 28 30
11 18 19 22 28 30
11 18 21 24 27 30
11 13 14 18 26 30
08 13 14 18 31 32
08 13 18 26 29 31
08 13 19 22 27 30
08 09 11 18 29 32
08 09 11 19 21 30
11 13 14 18 29 31
11 12 20 25 26 30
08 11 25 26 27 30
11 13 18 22 26 30
08 13 16 18 19 21
08 16 18 25 26 27
08 10 13 18 26 28
08 12 13 18 19 33
11 13 18 23 29 31
08 11 13 18 19 23
08 11 13 24 29 31
11 13 16 17 18 24
11 12 13 17 18 23
08 09 11 12 13 19
11 13 16 18 28 31
08 11 13 24 28 31
11 12 13 17 18 33
08 11 13 16 24 26
08 09 11 25 28 31
11 12 13 25 26 31
11 18 23 24 26 30
11 13 16 25 28 29
08 09 11 25 29 31
11 13 18 27 31 33
08 11 13 15 26 31
11 12 13 15 17 18
08 09 11 25 26 31
08 11 17 25 28 29
08 11 12 13 16 33
11 13 28 30 31 33
08 11 23 25 31 32
11 13 15 17 18 25
08 11 12 13 17 25
11 12 13 15 18 25
11 18 23 25 26 31
11 12 13 21 23 25
08 11 12 13 19 33
08 11 13 15 29 31
08 11 16 17 24 25
08 13 17 22 23 25
08 11 13 25 29 31
13 16 18 25 27 33
11 12 13 17 23 25
08 09 11 25 27 31
08 11 16 19 24 25
08 09 11 12 13 31
11 13 15 19 21 25
08 11 13 27 31 33
(
fid INT IDENTITY(1, 1) ,
BallNo INT
)CREATE TABLE #Buy
(
fid INT IDENTITY(1, 1) ,
Bule1 INT ,
Bule2 INT ,
Bule3 INT ,
Bule4 INT ,
Bule5 INT ,
Bule6 INT ,
Red1 INT
)
INSERT INTO #Result(BallNo)VALUES (08) --篮球中奖号码
INSERT INTO #Result(BallNo)VALUES (16) --蓝球中奖号码
INSERT INTO #Result(BallNo)VALUES (18) --蓝球中奖号码
INSERT INTO #Result(BallNo)VALUES (25) --蓝球中奖号码
INSERT INTO #Result(BallNo)VALUES (26) --蓝球中奖号码
INSERT INTO #Result(BallNo)VALUES (27) --蓝球中奖号码
INSERT INTO #Result(BallNo)VALUES (06) --红球中奖号码INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,16,18,25,26,27,06)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,16,18,19,21,30,06)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,13,18,21,27,32,09)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,18,19,22,27,30,06)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,11,16,18,26,32,06)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,11,18,20,23,27,04)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(11,18,26,30,31,32,06)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,16,18,25,26,30,07)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(11,17,25,26,27,30,06)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,15,18,21,22,30,02)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(11,14,18,20,26,30,06)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(08,11,18,20,22,23,06)
INSERT INTO #Buy(Bule1,Bule2,Bule3,Bule4,Bule5,Bule6,Red1)VALUES(11,13,16,21,26,30,06)
SELECT *,ltrim((CASE WHEN bule1 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule2 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule3 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule4 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule5 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule6 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)) + '+' +
LTRIM(CASE WHEN red1 IN (SELECT ballno FROM #Result WHERE fid =7) THEN 1 ELSE 0 END) ,
CASE (ltrim((CASE WHEN bule1 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule2 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule3 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule4 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule5 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN bule6 IN (SELECT ballno FROM #Result WHERE fid <7) THEN 1 ELSE 0 END)) + '+' +
LTRIM(CASE WHEN red1 IN (SELECT ballno FROM #Result WHERE fid =7) THEN 1 ELSE 0 END) )
WHEN '6+1' THEN '一等奖'
WHEN '6+0' THEN '二等奖'
WHEN '5+1' THEN '三等奖'
WHEN '5+0' THEN '四等奖'
WHEN '4+1' THEN '四等奖'
WHEN '3+1' THEN '五等奖'
WHEN '4+0' THEN '五等奖'
WHEN '2+1' THEN '六等奖'
WHEN '1+1' THEN '六等奖'
WHEN '0+1' THEN '六等奖'
ELSE '鸭蛋'
END
FROM #Buy DROP TABLE #Result
DROP TABLE #Buy结果:
1 8 16 18 25 26 27 6 6+1 一等奖
2 8 16 18 19 21 30 6 3+1 五等奖
3 8 13 18 21 27 32 9 3+0 鸭蛋
4 8 18 19 22 27 30 6 3+1 五等奖
5 8 11 16 18 26 32 6 4+1 四等奖
6 8 11 18 20 23 27 4 3+0 鸭蛋
7 11 18 26 30 31 32 6 2+1 六等奖
8 8 16 18 25 26 30 7 5+0 四等奖
9 11 17 25 26 27 30 6 3+1 五等奖
10 8 15 18 21 22 30 2 2+0 鸭蛋
11 11 14 18 20 26 30 6 2+1 六等奖
12 8 11 18 20 22 23 6 2+1 六等奖
13 11 13 16 21 26 30 6 2+1 六等奖
SELECT * FROM TB1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB1]') AND type in (N'U'))
DROP TABLE [dbo].[TB1]
GO
CREATE TABLE TB1 (ID bigint,NO1 char(2), NO2 char(2), NO3 char(2), NO4 char(2), NO5 char(2), NO6 char(2))
GO
INSERT INTO TB1
SELECT '8','16','18','25','26','27'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB2]') AND type in (N'U'))
DROP TABLE [dbo].[TB2]
GO
CREATE TABLE TB2 (ID bigint,NO1 char(2), NO2 int, NO3 char(2), NO4 char(2), NO5 char(2), NO6 char(2))
GO
INSERT INTO TB2
SELECT '8','16','18','19','21','30'UNION ALL
--表变量
declare @temp table
(
rowid bigint identity(1,1),
id bigint,
no1 char(2),
no2 char(2),
no3 char(2),
no4 char(2),
no5 char(2),
no6 char(2)
)
--表变量赋值,循环准备
insert into @temp(id,no1,no2,no3,no4,no5,no6)
select id,no1,no2,no3,no4,no5,no6 from tb1
--总行数
declare @rowid int
select @rowid = count(*) from @temp
--赋值变量
declare @id bigint
declare @r0 int
declare @r1 int
declare @r2 int
declare @r3 int
declare @r4 int
declare @r5 int
declare @r6 intwhile @rowid > 0
begin
select @id = id from @temp where rowid = @rowid select
@r0 = sum(case num when 0 then 1 else 0 end),
@r1 = sum(case num when 1 then 1 else 0 end),
@r2 = sum(case num when 2 then 1 else 0 end),
@r3 = sum(case num when 3 then 1 else 0 end),
@r4 = sum(case num when 4 then 1 else 0 end),
@r5 = sum(case num when 5 then 1 else 0 end),
@r6 = sum(case num when 6 then 1 else 0 end)
from
(
select
case when 1 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 1 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 2 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 2 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 3 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 3 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 4 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 4 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 5 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 5 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 6 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 6 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 7 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 7 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 8 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 8 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 9 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 9 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 10 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 10 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 11 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 11 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 12 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 12 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 13 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 13 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 14 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 14 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 15 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 15 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 16 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 16 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 17 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 17 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 18 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 18 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 19 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 19 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 20 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 20 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 21 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 21 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 22 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 22 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 23 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 23 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 24 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 24 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 25 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 25 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 26 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 26 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 27 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 27 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 28 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 28 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 29 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 29 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 30 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 30 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 31 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 31 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 32 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 32 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 33 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 33 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end as num
from tb2 a join @temp b on b.rowid = @rowid
) x
update tb1 set
r0 = @r0,
r1 = @r1,
r2 = @r2,
r3 = @r3,
r4 = @r4,
r5 = @r5,
r6 = @r6
where id = @id select @rowid = @rowid-1end
][/code]
SELECT'8', '13', '18', '21', '27', '32'UNION ALL SELECT'
8', '18', '19', '22', '27', '30'UNION ALL SELECT'8', '11', '16', '18', '26', '32'UNION ALL SELECT'
8', '11', '18', '20', '23', '27'UNION ALL SELECT'
11', '18', '26', '30', '31', '32'UNION ALL SELECT'
8', '16', '18', '25', '26', '30'UNION ALL SELECT'
11', '17', '25', '26', '27', '30'UNION ALL SELECT'
8', '15', '18', '21', '22', '30'UNION ALL SELECT'
11', '14', '18', '20', '26', '30'UNION ALL SELECT'
8', '11', '18', '20', '22', '23'UNION ALL SELECT'
11', '13', '16', '21', '26', '30'UNION ALL SELECT'
11', '13', '18', '20', '21', '26'UNION ALL SELECT'
8', '10', '11', '18', '26', '29'UNION ALL SELECT'
8', '10', '11', '18', '26', '31'UNION ALL SELECT'
8', '9', '10', '11', '18', '26'UNION ALL SELECT'
11', '18', '21', '27', '29', '30'UNION ALL SELECT'
8', '11', '22', '23', '27', '30'UNION ALL SELECT'
11', '18', '25', '26', '27', '30'UNION ALL SELECT'
11', '21', '25', '26', '28', '30'UNION ALL SELECT'
11', '18', '19', '22', '28', '30'UNION ALL SELECT'
11', '18', '21', '24', '27', '30'UNION ALL SELECT'
11', '13', '14', '18', '26', '30'UNION ALL SELECT'
8', '13', '14', '18', '31', '32'UNION ALL SELECT'
8', '13', '18', '26', '29', '31'UNION ALL SELECT'
8', '13', '19', '22', '27', '30'UNION ALL SELECT'
8', '9', '11', '18', '29', '32'UNION ALL SELECT'
8', '9', '11', '19', '21', '30'UNION ALL SELECT'
11', '13', '14', '18', '29', '31'UNION ALL SELECT'
11', '12', '20', '25', '26', '30'UNION ALL SELECT'
8', '11', '25', '26', '27', '30'UNION ALL SELECT'
11', '13', '18', '22', '26', '30'UNION ALL SELECT'
8', '13', '16', '18', '19', '21'UNION ALL SELECT'
8', '16', '18', '25', '26', '27'UNION ALL SELECT'
8', '10', '13', '18', '26', '28'UNION ALL SELECT'
8', '12', '13', '18', '19', '33'UNION ALL SELECT'
11', '13', '18', '23', '29', '31'UNION ALL SELECT'
8', '11', '13', '18', '19', '23'UNION ALL SELECT'
8', '11', '13', '24', '29', '31'UNION ALL SELECT'
11', '13', '16', '17', '18', '24'UNION ALL SELECT'
11', '12', '13', '17', '18', '23'UNION ALL SELECT'
8', '9', '11', '12', '13', '19'UNION ALL SELECT'
11', '13', '16', '18', '28', '31'UNION ALL SELECT'
8', '11', '13', '24', '28', '31'UNION ALL SELECT'
11', '12', '13', '17', '18', '33'UNION ALL SELECT'
8', '11', '13', '16', '24', '26'UNION ALL SELECT'
8', '9', '11', '25', '28', '31'UNION ALL SELECT'
11', '12', '13', '25', '26', '31'UNION ALL SELECT'
11', '18', '23', '24', '26', '30'UNION ALL SELECT'
11', '13', '16', '25', '28', '29'UNION ALL SELECT'
8', '9', '11', '25', '29', '31'UNION ALL SELECT'
11', '13', '18', '27', '31', '33'UNION ALL SELECT'
8', '11', '13', '15', '26', '31'UNION ALL SELECT'
11', '12', '13', '15', '17', '18'UNION ALL SELECT'
8', '9', '11', '25', '26', '31'UNION ALL SELECT'
8', '11', '17', '25', '28', '29'UNION ALL SELECT'
8', '11', '12', '13', '16', '33'UNION ALL SELECT'
11', '13', '28', '30', '31', '33'UNION ALL SELECT'
8', '11', '23', '25', '31', '32'UNION ALL SELECT'
11', '13', '15', '17', '18', '25'UNION ALL SELECT'
8', '11', '12', '13', '17', '25'UNION ALL SELECT'
11', '12', '13', '15', '18', '25'UNION ALL SELECT'
11', '18', '23', '25', '26', '31'UNION ALL SELECT'
11', '12', '13', '21', '23', '25'UNION ALL SELECT'
8', '11', '12', '13', '19', '33'UNION ALL SELECT'
8', '11', '13', '15', '29', '31'UNION ALL SELECT'
8', '11', '16', '17', '24', '25'UNION ALL SELECT'
8', '13', '17', '22', '23', '25'UNION ALL SELECT'
8', '11', '13', '25', '29', '31'UNION ALL SELECT'
13', '16', '18', '25', '27', '33'UNION ALL SELECT'
11', '12', '13', '17', '23', '25'UNION ALL SELECT'
8', '9', '11', '25', '27', '31'UNION ALL SELECT'
8', '11', '16', '19', '24', '25'UNION ALL SELECT'
8', '9', '11', '12', '13', '31'UNION ALL SELECT'
11', '13', '15', '19', '21', '25'UNION ALL SELECT'
08', '11', '13', '27', '31', '33'
SELECT * FROM TB1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB1]') AND type in (N'U'))
DROP TABLE [dbo].[TB1]
GO
CREATE TABLE TB1 (ID bigint,NO1 char(2), NO2 char(2), NO3 char(2), NO4 char(2), NO5 char(2), NO6 char(2))
GO
INSERT INTO TB1
SELECT '8','16','18','25','26','27'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB2]') AND type in (N'U'))
DROP TABLE [dbo].[TB2]
GO
CREATE TABLE TB2 (ID bigint,NO1 char(2), NO2 int, NO3 char(2), NO4 char(2), NO5 char(2), NO6 char(2))
GO
INSERT INTO TB2
SELECT '8','16','18','19','21','30'UNION ALL
--表变量
declare @temp table
(
rowid bigint identity(1,1),
id bigint,
no1 char(2),
no2 char(2),
no3 char(2),
no4 char(2),
no5 char(2),
no6 char(2)
)
--表变量赋值,循环准备
insert into @temp(id,no1,no2,no3,no4,no5,no6)
select id,no1,no2,no3,no4,no5,no6 from tb1
--总行数
declare @rowid int
select @rowid = count(*) from @temp
--赋值变量
declare @id bigint
declare @r0 int
declare @r1 int
declare @r2 int
declare @r3 int
declare @r4 int
declare @r5 int
declare @r6 intwhile @rowid > 0
begin
select @id = id from @temp where rowid = @rowid select
@r0 = sum(case num when 0 then 1 else 0 end),
@r1 = sum(case num when 1 then 1 else 0 end),
@r2 = sum(case num when 2 then 1 else 0 end),
@r3 = sum(case num when 3 then 1 else 0 end),
@r4 = sum(case num when 4 then 1 else 0 end),
@r5 = sum(case num when 5 then 1 else 0 end),
@r6 = sum(case num when 6 then 1 else 0 end)
from
(
select
case when 1 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 1 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 2 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 2 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 3 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 3 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 4 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 4 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 5 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 5 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 6 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 6 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 7 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 7 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 8 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 8 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 9 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 9 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 10 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 10 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 11 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 11 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 12 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 12 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 13 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 13 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 14 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 14 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 15 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 15 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 16 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 16 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 17 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 17 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 18 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 18 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 19 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 19 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 20 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 20 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 21 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 21 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 22 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 22 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 23 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 23 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 24 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 24 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 25 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 25 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 26 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 26 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 27 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 27 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 28 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 28 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 29 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 29 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 30 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 30 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 31 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 31 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 32 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 32 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end +
case when 33 in ( a.NO1, a.NO2, a.NO3, a.NO4, a.NO5, a.NO6 ) AND 33 in ( b.NO1, b.NO2, b.NO3, b.NO4, b.NO5, b.NO6 ) then 1 else 0 end as num
from tb2 a join @temp b on b.rowid = @rowid
) x
update tb1 set
r0 = @r0,
r1 = @r1,
r2 = @r2,
r3 = @r3,
r4 = @r4,
r5 = @r5,
r6 = @r6
where id = @id select @rowid = @rowid-1end
INSERT INTO TB2
SELECT '8','16','18','19','21','30'UNION ALL SELECT'8', '13', '18', '21', '27', '32'UNION ALL SELECT'
8', '18', '19', '22', '27', '30'UNION ALL SELECT'8', '11', '16', '18', '26', '32'UNION ALL SELECT'
8', '11', '18', '20', '23', '27'UNION ALL SELECT'
11', '18', '26', '30', '31', '32'UNION ALL SELECT'
8', '16', '18', '25', '26', '30'UNION ALL SELECT'
11', '17', '25', '26', '27', '30'UNION ALL SELECT'
8', '15', '18', '21', '22', '30'UNION ALL SELECT'
11', '14', '18', '20', '26', '30'UNION ALL SELECT'
8', '11', '18', '20', '22', '23'UNION ALL SELECT'
11', '13', '16', '21', '26', '30'UNION ALL SELECT'
11', '13', '18', '20', '21', '26'UNION ALL SELECT'
8', '10', '11', '18', '26', '29'UNION ALL SELECT'
8', '10', '11', '18', '26', '31'UNION ALL SELECT'
8', '9', '10', '11', '18', '26'UNION ALL SELECT'
11', '18', '21', '27', '29', '30'UNION ALL SELECT'
8', '11', '22', '23', '27', '30'UNION ALL SELECT'
11', '18', '25', '26', '27', '30'UNION ALL SELECT'
11', '21', '25', '26', '28', '30'UNION ALL SELECT'
11', '18', '19', '22', '28', '30'UNION ALL SELECT'
11', '18', '21', '24', '27', '30'UNION ALL SELECT'
11', '13', '14', '18', '26', '30'UNION ALL SELECT'
8', '13', '14', '18', '31', '32'UNION ALL SELECT'
8', '13', '18', '26', '29', '31'UNION ALL SELECT'
8', '13', '19', '22', '27', '30'UNION ALL SELECT'
8', '9', '11', '18', '29', '32'UNION ALL SELECT'
8', '9', '11', '19', '21', '30'UNION ALL SELECT'
11', '13', '14', '18', '29', '31'UNION ALL SELECT'
11', '12', '20', '25', '26', '30'UNION ALL SELECT'
8', '11', '25', '26', '27', '30'UNION ALL SELECT'
11', '13', '18', '22', '26', '30'UNION ALL SELECT'
8', '13', '16', '18', '19', '21'UNION ALL SELECT'
8', '16', '18', '25', '26', '27'UNION ALL SELECT'
8', '10', '13', '18', '26', '28'UNION ALL SELECT'
8', '12', '13', '18', '19', '33'UNION ALL SELECT'
11', '13', '18', '23', '29', '31'UNION ALL SELECT'
8', '11', '13', '18', '19', '23'UNION ALL SELECT'
8', '11', '13', '24', '29', '31'UNION ALL SELECT'
11', '13', '16', '17', '18', '24'UNION ALL SELECT'
11', '12', '13', '17', '18', '23'UNION ALL SELECT'
8', '9', '11', '12', '13', '19'UNION ALL SELECT'
11', '13', '16', '18', '28', '31'UNION ALL SELECT'
8', '11', '13', '24', '28', '31'UNION ALL SELECT'
11', '12', '13', '17', '18', '33'UNION ALL SELECT'
8', '11', '13', '16', '24', '26'UNION ALL SELECT'
8', '9', '11', '25', '28', '31'UNION ALL SELECT'
11', '12', '13', '25', '26', '31'UNION ALL SELECT'
11', '18', '23', '24', '26', '30'UNION ALL SELECT'
11', '13', '16', '25', '28', '29'UNION ALL SELECT'
8', '9', '11', '25', '29', '31'UNION ALL SELECT'
11', '13', '18', '27', '31', '33'UNION ALL SELECT'
8', '11', '13', '15', '26', '31'UNION ALL SELECT'
11', '12', '13', '15', '17', '18'UNION ALL SELECT'
8', '9', '11', '25', '26', '31'UNION ALL SELECT'
8', '11', '17', '25', '28', '29'UNION ALL SELECT'
8', '11', '12', '13', '16', '33'UNION ALL SELECT'
11', '13', '28', '30', '31', '33'UNION ALL SELECT'
8', '11', '23', '25', '31', '32'UNION ALL SELECT'
11', '13', '15', '17', '18', '25'UNION ALL SELECT'
8', '11', '12', '13', '17', '25'UNION ALL SELECT'
11', '12', '13', '15', '18', '25'UNION ALL SELECT'
11', '18', '23', '25', '26', '31'UNION ALL SELECT'
11', '12', '13', '21', '23', '25'UNION ALL SELECT'
8', '11', '12', '13', '19', '33'UNION ALL SELECT'
8', '11', '13', '15', '29', '31'UNION ALL SELECT'
8', '11', '16', '17', '24', '25'UNION ALL SELECT'
8', '13', '17', '22', '23', '25'UNION ALL SELECT'
8', '11', '13', '25', '29', '31'UNION ALL SELECT'
13', '16', '18', '25', '27', '33'UNION ALL SELECT'
11', '12', '13', '17', '23', '25'UNION ALL SELECT'
8', '9', '11', '25', '27', '31'UNION ALL SELECT'
8', '11', '16', '19', '24', '25'UNION ALL SELECT'
8', '9', '11', '12', '13', '31'UNION ALL SELECT'
11', '13', '15', '19', '21', '25'UNION ALL SELECT'
08', '11', '13', '27', '31', '33'
SELECT *
,ltrim
(( CASE WHEN NO1 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO2 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO3 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO4 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO5 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO6 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END))
+ '+' +
LTRIM(CASE WHEN NO7 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid =7) THEN 1 ELSE 0 END) as 中奖个数,
CASE (ltrim((CASE WHEN NO1 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO2 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO3 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO4 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO5 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO6 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END))
+ '+' +
LTRIM(CASE WHEN NO7 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid =7) THEN 1 ELSE 0 END))
WHEN '6+1' THEN '一等奖'
WHEN '6+0' THEN '二等奖'
WHEN '5+1' THEN '三等奖'
WHEN '5+0' THEN '四等奖'
WHEN '4+1' THEN '四等奖'
WHEN '3+1' THEN '五等奖'
WHEN '4+0' THEN '五等奖'
WHEN '2+1' THEN '六等奖'
WHEN '1+1' THEN '六等奖'
WHEN '0+1' THEN '六等奖'
ELSE '0000'
END
---INTO #MM
FROM [JGCSTai].[dbo].[TB22] ---SELECT *,'' AS 等级 FROM #MM---DROP TABLE #MM
*/----------------
fid NO1 NO2 NO3 NO4 NO5 NO6 NO7 中奖个数 ????
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------------------- ------
1 8 16 18 19 21 30 6 3+1 五等奖
2 8 13 18 21 27 32 6 3+1 五等奖
3 8 18 19 22 27 30 6 3+1 五等奖
4 8 11 16 18 26 32 6 4+1 四等奖
...........
......
CASE (ltrim((CASE WHEN NO1 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO2 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO3 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO4 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO5 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END)
+(CASE WHEN NO6 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid <7) THEN 1 ELSE 0 END))
+ '+' +
LTRIM(CASE WHEN NO7 IN (SELECT ballno FROM [JGCSTai].[dbo].[TB11] WHERE fid =7) THEN 1 ELSE 0 END))
WHEN '6+1' THEN '一等奖'
WHEN '6+0' THEN '二等奖'
WHEN '5+1' THEN '三等奖'
WHEN '5+0' THEN '四等奖'
WHEN '4+1' THEN '四等奖'
WHEN '3+1' THEN '五等奖'
WHEN '4+0' THEN '五等奖'
WHEN '2+1' THEN '六等奖'
WHEN '1+1' THEN '六等奖'
WHEN '0+1' THEN '六等奖'
ELSE '0000'
END 这段代码就是判断中奖等级的。
基本都是如出一辙。
建议斑竹们关注下IP等情况。
建议这两个账号的朋友慎重!这点分没意思,主要是怕坏了这么多年来大家辛辛苦苦培养的良好风气,
需要可用分给我说,我给你捐赠。
--建表
IF OBJECT_ID(N'TB',N'U') IS NOT NULL
DROP TABLE [dbo].[TB]
GO
CREATE TABLE TB (ID INT IDENTITY(1,1),NO1 VARCHAR(2), NO2 VARCHAR(2), NO3 VARCHAR(2), NO4 VARCHAR(2), NO5 VARCHAR(2), NO6 VARCHAR(2))
GO--插入
INSERT INTO TB
SELECT '8','16','18','19','21','30' UNION ALL
SELECT '8', '13', '18', '21', '27', '32' UNION ALL
SELECT '8', '18', '19', '22', '27', '30' UNION ALL
SELECT '8', '11', '16', '18', '26', '32' UNION ALL
SELECT '8', '11', '18', '20', '23', '27' UNION ALL
SELECT '11', '18', '26', '30', '31', '32'
-- UNION ALL ...DECLARE @s VARCHAR(50)
SET @s = ',8,16,18,25,26,27,' --如果存在某个表中,也很容易生成SELECT b.*, a.HitNum as 中奖号码数
FROM (
SELECT ID, SUM(
CASE WHEN CHARINDEX(','+NO1+',',@s) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(','+NO2+',',@s) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(','+NO3+',',@s) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(','+NO4+',',@s) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(','+NO5+',',@s) > 0 THEN 1 ELSE 0 END +
CASE WHEN CHARINDEX(','+NO6+',',@s) > 0 THEN 1 ELSE 0 END) AS HitNum
FROM TB
GROUP BY ID
) a
JOIN TB b ON a.ID = b.ID/*
ID NO1 NO2 NO3 NO4 NO5 NO6 中奖号码数
----------- ---- ---- ---- ---- ---- ---- -----------
1 8 16 18 19 21 30 3
2 8 13 18 21 27 32 3
3 8 18 19 22 27 30 3
4 8 11 16 18 26 32 4
5 8 11 18 20 23 27 3
6 11 18 26 30 31 32 2
......
*/
http://database.51cto.com/art/200903/114942.htm
CREATE TABLE #Table1(ID INT IDENTITY (1,1),Number1 int,Number2 int,Number3 int,Number4 int,Number5 int,Number6 int,Number7 INT)
INSERT #Table1(Number1,Number2,Number3,Number4,Number5,Number6,Number7)
SELECT '08','16','18','19','21','30','06' UNION ALL
SELECT '08','13','18','21','27','32','06' UNION ALL
SELECT '08','18','19','22','27','30','06' UNION ALL
SELECT '08','11','16','18','26','32','06' UNION ALL
SELECT '08','11','18','20','23','27','06' UNION ALL
SELECT '11','18','26','30','31','32','06' UNION ALL
SELECT '08','16','18','25','26','30','06' UNION ALL
SELECT '11','17','25','26','27','30','06' UNION ALL
SELECT '08','15','18','21','22','30','06' UNION ALL
SELECT '11','14','18','20','26','30','06' UNION ALL
SELECT '08','11','18','20','22','23','06' UNION ALL
SELECT '11','13','16','21','26','30','06' UNION ALL
SELECT '11','13','18','20','21','26','06' UNION ALL
SELECT '08','10','11','18','26','29','06' UNION ALL
SELECT '08','10','11','18','26','31','06' UNION ALL
SELECT '08','09','10','11','18','26','06' UNION ALL
SELECT '11','18','21','27','29','30','06' UNION ALL
SELECT '08','11','22','23','27','30','06' UNION ALL
SELECT '11','18','25','26','27','30','06' UNION ALL
SELECT '11','21','25','26','28','30','06' UNION ALL
SELECT '11','18','19','22','28','30','06' UNION ALL
SELECT '11','18','21','24','27','30','06' UNION ALL
SELECT '11','13','14','18','26','30','06' UNION ALL
SELECT '08','13','14','18','31','32','06' UNION ALL
SELECT '08','13','18','26','29','31','06' UNION ALL
SELECT '08','13','19','22','27','30','06' UNION ALL
SELECT '08','09','11','18','29','32','06' UNION ALL
SELECT '08','09','11','19','21','30','06' UNION ALL
SELECT '11','13','14','18','29','31','06' UNION ALL
SELECT '11','12','20','25','26','30','06' UNION ALL
SELECT '08','11','25','26','27','30','06' UNION ALL
SELECT '11','13','18','22','26','30','06' UNION ALL
SELECT '08','13','16','18','19','21','06' UNION ALL
SELECT '08','16','18','25','26','27','06' UNION ALL
SELECT '08','10','13','18','26','28','06' UNION ALL
SELECT '08','12','13','18','19','33','06' UNION ALL
SELECT '11','13','18','23','29','31','06' UNION ALL
SELECT '08','11','13','18','19','23','06' UNION ALL
SELECT '08','11','13','24','29','31','06' UNION ALL
SELECT '11','13','16','17','18','24','06' UNION ALL
SELECT '11','12','13','17','18','23','06' UNION ALL
SELECT '08','09','11','12','13','19','06' UNION ALL
SELECT '11','13','16','18','28','31','06' UNION ALL
SELECT '08','11','13','24','28','31','06' UNION ALL
SELECT '11','12','13','17','18','33','06' UNION ALL
SELECT '08','11','13','16','24','26','06' UNION ALL
SELECT '08','09','11','25','28','31','06' UNION ALL
SELECT '11','12','13','25','26','31','06' UNION ALL
SELECT '11','18','23','24','26','30','06' UNION ALL
SELECT '11','13','16','25','28','29','06' UNION ALL
SELECT '08','09','11','25','29','31','06' UNION ALL
SELECT '11','13','18','27','31','33','06' UNION ALL
SELECT '08','11','13','15','26','31','06' UNION ALL
SELECT '11','12','13','15','17','18','06' UNION ALL
SELECT '08','09','11','25','26','31','06' UNION ALL
SELECT '08','11','17','25','28','29','06' UNION ALL
SELECT '08','11','12','13','16','33','06' UNION ALL
SELECT '11','13','28','30','31','33','06' UNION ALL
SELECT '08','11','23','25','31','32','06' UNION ALL
SELECT '11','13','15','17','18','25','06' UNION ALL
SELECT '08','11','12','13','17','25','06' UNION ALL
SELECT '11','12','13','15','18','25','06' UNION ALL
SELECT '11','18','23','25','26','31','06' UNION ALL
SELECT '11','12','13','21','23','25','06' UNION ALL
SELECT '08','11','12','13','19','33','06' UNION ALL
SELECT '08','11','13','15','29','31','06' UNION ALL
SELECT '08','11','16','17','24','25','06' UNION ALL
SELECT '08','13','17','22','23','25','06' UNION ALL
SELECT '08','11','13','25','29','31','06' UNION ALL
SELECT '13','16','18','25','27','33','06' UNION ALL
SELECT '11','12','13','17','23','25','06' UNION ALL
SELECT '08','09','11','25','27','31','06' UNION ALL
SELECT '08','11','16','19','24','25','06' UNION ALL
SELECT '08','09','11','12','13','31','06' UNION ALL
SELECT '11','13','15','19','21','25','06' UNION ALL
SELECT '08','11','13','27','31','33','06'
GO
SELECT t.*,x.Result
FROM (
SELECT a.ID
, CASE
WHEN SUM(1) = 7 THEN 1
WHEN SUM(CASE WHEN a.NumberColor = 0 THEN 1 ELSE 0 END ) = 6 AND SUM(CASE WHEN a.NumberColor = 0 THEN 0 ELSE 1 END) = 0 THEN 2
WHEN SUM(CASE WHEN a.NumberColor = 0 THEN 1 ELSE 0 END ) = 5 AND SUM(CASE WHEN a.NumberColor = 0 THEN 0 ELSE 1 END) = 1 THEN 3
WHEN SUM(CASE WHEN a.NumberColor = 0 THEN 1 ELSE 0 END ) = 4 AND SUM(CASE WHEN a.NumberColor = 0 THEN 0 ELSE 1 END) = 1
OR SUM(CASE WHEN a.NumberColor = 0 THEN 1 ELSE 0 END ) = 5 AND SUM(CASE WHEN a.NumberColor = 0 THEN 0 ELSE 1 END) = 0 THEN 4
WHEN SUM(CASE WHEN a.NumberColor = 0 THEN 1 ELSE 0 END ) = 3 AND SUM(CASE WHEN a.NumberColor = 0 THEN 0 ELSE 1 END) = 1
OR SUM(CASE WHEN a.NumberColor = 0 THEN 1 ELSE 0 END ) = 4 AND SUM(CASE WHEN a.NumberColor = 0 THEN 0 ELSE 1 END) = 0 THEN 5
WHEN SUM(CASE WHEN a.NumberColor = 0 THEN 1 ELSE 0 END ) IN (0,1,2) AND SUM(CASE WHEN a.NumberColor = 0 THEN 0 ELSE 1 END) = 1 THEN 6
ELSE NULL
END AS Result
FROM (
SELECT ID,a.Number1 AS Number, 0 AS NumberColor FROM #Table1 a UNION ALL
SELECT ID,a.Number2 AS Number, 0 AS NumberColor FROM #Table1 a UNION ALL
SELECT ID,a.Number3 AS Number, 0 AS NumberColor FROM #Table1 a UNION ALL
SELECT ID,a.Number4 AS Number, 0 AS NumberColor FROM #Table1 a UNION ALL
SELECT ID,a.Number5 AS Number, 0 AS NumberColor FROM #Table1 a UNION ALL
SELECT ID,a.Number6 AS Number, 0 AS NumberColor FROM #Table1 a UNION ALL
SELECT ID,a.Number7 AS Number, 1 AS NumberColor FROM #Table1 a
) a
JOIN (
SELECT '08' AS Number,0 as NumberColor UNION ALL
SELECT '16' AS Number,0 as NumberColor UNION ALL
SELECT '18' AS Number,0 as NumberColor UNION ALL
SELECT '25' AS Number,0 as NumberColor UNION ALL
SELECT '26' AS Number,0 as NumberColor UNION ALL
SELECT '27' AS Number,0 as NumberColor UNION ALL
SELECT '06' AS Number,1 as NumberColor
) b ON a.Number = b.Number AND a.NumberColor = b.NumberColor
GROUP BY a.ID
) x
JOIN #Table1 t ON t.ID = x.ID
WHERE x.Result IS NOT NULL
GO
DROP TABLE #Table1
GO
/*-- ID Number1 Number2 Number3 Number4 Number5 Number6 Number7 中奖等级
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 8 16 18 19 21 30 6 5
2 8 13 18 21 27 32 6 5
3 8 18 19 22 27 30 6 5
4 8 11 16 18 26 32 6 4
5 8 11 18 20 23 27 6 5
6 11 18 26 30 31 32 6 6
7 8 16 18 25 26 30 6 3
8 11 17 25 26 27 30 6 5
9 8 15 18 21 22 30 6 6
10 11 14 18 20 26 30 6 6
11 8 11 18 20 22 23 6 6
12 11 13 16 21 26 30 6 6
13 11 13 18 20 21 26 6 6
14 8 10 11 18 26 29 6 5
15 8 10 11 18 26 31 6 5
16 8 9 10 11 18 26 6 5
17 11 18 21 27 29 30 6 6
18 8 11 22 23 27 30 6 6
19 11 18 25 26 27 30 6 4
20 11 21 25 26 28 30 6 6
21 11 18 19 22 28 30 6 6
22 11 18 21 24 27 30 6 6
23 11 13 14 18 26 30 6 6
24 8 13 14 18 31 32 6 6
25 8 13 18 26 29 31 6 5
26 8 13 19 22 27 30 6 6
27 8 9 11 18 29 32 6 6
28 8 9 11 19 21 30 6 6
29 11 13 14 18 29 31 6 6
30 11 12 20 25 26 30 6 6
31 8 11 25 26 27 30 6 4
32 11 13 18 22 26 30 6 6
33 8 13 16 18 19 21 6 5
34 8 16 18 25 26 27 6 1
35 8 10 13 18 26 28 6 5
36 8 12 13 18 19 33 6 6
37 11 13 18 23 29 31 6 6
38 8 11 13 18 19 23 6 6
39 8 11 13 24 29 31 6 6
40 11 13 16 17 18 24 6 6
41 11 12 13 17 18 23 6 6
42 8 9 11 12 13 19 6 6
43 11 13 16 18 28 31 6 6
44 8 11 13 24 28 31 6 6
45 11 12 13 17 18 33 6 6
46 8 11 13 16 24 26 6 5
47 8 9 11 25 28 31 6 6
48 11 12 13 25 26 31 6 6
49 11 18 23 24 26 30 6 6
50 11 13 16 25 28 29 6 6
51 8 9 11 25 29 31 6 6
52 11 13 18 27 31 33 6 6
53 8 11 13 15 26 31 6 6
54 11 12 13 15 17 18 6 6
55 8 9 11 25 26 31 6 5
56 8 11 17 25 28 29 6 6
57 8 11 12 13 16 33 6 6
58 11 13 28 30 31 33 6 6
59 8 11 23 25 31 32 6 6
60 11 13 15 17 18 25 6 6
61 8 11 12 13 17 25 6 6
62 11 12 13 15 18 25 6 6
63 11 18 23 25 26 31 6 5
64 11 12 13 21 23 25 6 6
65 8 11 12 13 19 33 6 6
66 8 11 13 15 29 31 6 6
67 8 11 16 17 24 25 6 5
68 8 13 17 22 23 25 6 6
69 8 11 13 25 29 31 6 6
70 13 16 18 25 27 33 6 4
71 11 12 13 17 23 25 6 6
72 8 9 11 25 27 31 6 5
73 8 11 16 19 24 25 6 5
74 8 9 11 12 13 31 6 6
75 11 13 15 19 21 25 6 6
76 8 11 13 27 31 33 6 6*/