22,300
社区成员




ID 号 码 0同 1同 2同 3同 4同 5同 6同
--- ----------------- --- --- --- --- --- --- ---
1 06 12 15 18 29 32 9 10 5 1 2 3 1
DECLARE @t TABLE(no CHAR(2))
INSERT @t SELECT '06'
UNION ALL SELECT '12'
UNION ALL SELECT '15'
UNION ALL SELECT '18'
UNION ALL SELECT '29'
UNION ALL SELECT '32'
DECLARE @a TABLE(id INT IDENTITY(1,1), no1 CHAR(2),no2 CHAR(2),no3 CHAR(2),no4 CHAR(2),no5 CHAR(2),no6 CHAR(2))
insert @a select '10','11','12','15','26','28'
union all select '06','12','15','20','21','29'
union all select '01','07','10','23','28','32'
union all select '04','06','07','10','13','25'
union all select '04','06','15','17','30','31'
union all select '01','03','10','21','26','27'
union all select '01','09','19','21','23','26'
union all select '05','08','09','14','17','23'
union all select '05','09','18','20','22','30'
union all select '01','02','08','13','17','24'
union all select '04','05','11','12','30','32'
union all select '02','12','16','17','27','30'
union all select '08','13','17','21','23','32'
union all select '03','05','07','08','21','31'
union all select '04','11','19','25','26','32'
union all select '11','17','28','30','31','33'
union all select '05','08','18','23','25','31'
union all select '05','16','19','20','25','28'
union all select '04','08','12','13','16','33'
union all select '07','10','25','26','27','32'
union all select '14','15','18','25','26','30'
union all select '02','06','11','12','15','32'
union all select '01','10','20','22','26','31'
union all select '02','07','15','17','22','30'
union all select '01','05','11','13','14','27'
union all select '08','13','15','26','29','31'
union all select '06','12','15','18','29','33'
union all select '06','12','17','28','29','31'
union all select '06','12','15','18','29','31'
union all select '06','12','15','18','29','32'
union all select '06','12','15','18','29','30'
SELECT [0]=SUM(CASE WHEN aa=0 THEN 1 ELSE 0 END),
[1]=SUM(CASE WHEN aa=1 THEN 1 ELSE 0 END),
[2]=SUM(CASE WHEN aa=2 THEN 1 ELSE 0 END),
[3]=SUM(CASE WHEN aa=3 THEN 1 ELSE 0 END),
[4]=SUM(CASE WHEN aa=4 THEN 1 ELSE 0 END),
[5]=SUM(CASE WHEN aa=5 THEN 1 ELSE 0 END),
[6]=SUM(CASE WHEN aa=6 THEN 1 ELSE 0 END)
FROM (
SELECT id,sum(CASE WHEN no IS NULL THEN 0 ELSE 1 END ) aa
FROM @t a right join @a b
on CHARINDEX(','+no+',', ','+no1+','+no2+','+no3+','+no4+','+no5+','+no6+',')>0
GROUP BY id
)aaa
--result
/*0 1 2 3 4 5 6
----------- ----------- ----------- ----------- ----------- ----------- -----------
9 10 5 1 2 3 1
(所影响的行数为 1 行)
*/
-- 本期开奖号码
declare @str varchar(40)
set @str='06,12,15,18,29,32'
set @str='03,12,15,18,29,32'
set @str='04,12,15,18,29,32'
set @str='05,12,15,18,29,32'
set @str='09,12,15,18,29,32'
开 奖 号 码 0同 1同 2同 3同 4同 5同 6同
---------------- --- --- --- ---- --- --- ---
09,12,15,18,29,32 8 11 6 2 3 1 0
--建立测试环境
Create table Caipiao_list(NO1 int,NO2 int,NO3 int,NO4 int,NO5 int,NO6 int)
insert into Caipiao_list
select 10,11,12,15,26,28
union all select 06,12,15,20,21,29
union all select 01,07,10,23,28,32
union all select 04,06,07,10,13,25
union all select 04,06,15,17,30,31
union all select 01,03,10,21,26,27
union all select 01,09,19,21,23,26
union all select 05,08,09,14,17,23
union all select 05,09,18,20,22,30
union all select 01,02,08,13,17,24
union all select 04,05,11,12,30,32
union all select 02,12,16,17,27,30
union all select 08,13,17,21,23,32
union all select 03,05,07,08,21,31
union all select 04,11,19,25,26,32
union all select 11,17,28,30,31,33
union all select 05,08,18,23,25,31
union all select 05,16,19,20,25,28
union all select 04,08,12,13,16,33
union all select 07,10,25,26,27,32
union all select 14,15,18,25,26,30
union all select 02,06,11,12,15,32
union all select 01,10,20,22,26,31
union all select 02,07,15,17,22,30
union all select 01,05,11,13,14,27
union all select 08,13,15,26,29,31
union all select 06,12,15,18,29,33
union all select 06,12,17,28,29,31
union all select 06,12,15,18,29,31
union all select 06,12,15,18,29,32
union all select 06,12,15,18,29,30
-- 本期开奖号码
declare @str varchar(40)
set @str='06,12,15,18,29,32'
--查询
select @str as [开奖号码]
,[0同]= (select count(*) from Caipiao_list)- count(*)
,[1同]= sum(case when SameNo=1 then 1 else 0 end)
,[2同]= sum(case when SameNo=2 then 1 else 0 end)
,[3同]= sum(case when SameNo=3 then 1 else 0 end)
,[4同]= sum(case when SameNo=4 then 1 else 0 end)
,[5同]= sum(case when SameNo=5 then 1 else 0 end)
,[6同]= sum(case when SameNo=6 then 1 else 0 end)
from
(
select A.id,count(*) as [SameNo]
from
(
select id=row_number() over (order by getdate()),
NO1,NO2,NO3,NO4,NO5,NO6
from Caipiao_list
) A
inner join
(
SELECT
SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) as [col]
FROM master.dbo.spt_values
where type='p' AND number BETWEEN 1 AND LEN(@str)
AND SUBSTRING(','+@str,number,1)=','
) B
on A.NO1=B.COL or A.NO2=B.COL or A.NO3=B.COL or A.NO4=B.COL or A.NO5=B.COL or A.NO6=B.COL
group by A.id
) X
/*
结果
开奖号码 0同 1同 2同 3同 4同 5同 6同
----------------- --- --- --- --- --- --- ---
06,12,15,18,29,32 9 10 5 1 2 3 1
*/
--删除测试环境
drop table Caipiao_list
-- 本期开奖号码
declare @str varchar(40)
set @str='06,12,15,18,29,32'
-- 本期开奖号码
declare @str varchar(40)
set @str='06,12,15,18,29,32'
set @str='03,12,15,18,29,32'
set @str='04,12,15,18,29,32'
set @str='05,12,15,18,29,32'
set @str='09,12,15,18,29,32'
尽管知道多条数据输入错了,但不知道如何修改,请教啦.....
select ID,NO1,NO2,NO3,NO4,NO5,NO6 INTO @str FROM [TBKJ]
select ID,NO1,NO2,NO3,NO4,NO5,NO6 INTO Caipiao_list FROM [TBLS]
--查询
select @str as [开奖号码]
,[0同]= (select count(*) from Caipiao_list)- count(*)
,[1同]= sum(case when SameNo=1 then 1 else 0 end)
,[2同]= sum(case when SameNo=2 then 1 else 0 end)
,[3同]= sum(case when SameNo=3 then 1 else 0 end)
,[4同]= sum(case when SameNo=4 then 1 else 0 end)
,[5同]= sum(case when SameNo=5 then 1 else 0 end)
,[6同]= sum(case when SameNo=6 then 1 else 0 end)
from
(
select A.id,count(*) as [SameNo]
from
(
select id=row_number() over (order by getdate()),
NO1,NO2,NO3,NO4,NO5,NO6
from Caipiao_list
) A
inner join
(
SELECT
SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) as [col]
FROM master.dbo.spt_values
where type='p' AND number BETWEEN 1 AND LEN(@str)
AND SUBSTRING(','+@str,number,1)=','
) B
on A.NO1=B.COL or A.NO2=B.COL or A.NO3=B.COL or A.NO4=B.COL or A.NO5=B.COL or A.NO6=B.COL
group by A.id
) X
这是另一种方案行吗
select ID,NO1,NO2,NO3,NO4,NO5,NO6 INTO @t FROM [TBKJ]
select ID,NO1,NO2,NO3,NO4,NO5,NO6 INTO @a FROM [TBLS]
SELECT [0]=SUM(CASE WHEN aa=0 THEN 1 ELSE 0 END),
[1]=SUM(CASE WHEN aa=1 THEN 1 ELSE 0 END),
[2]=SUM(CASE WHEN aa=2 THEN 1 ELSE 0 END),
[3]=SUM(CASE WHEN aa=3 THEN 1 ELSE 0 END),
[4]=SUM(CASE WHEN aa=4 THEN 1 ELSE 0 END),
[5]=SUM(CASE WHEN aa=5 THEN 1 ELSE 0 END),
[6]=SUM(CASE WHEN aa=6 THEN 1 ELSE 0 END)
FROM (
SELECT id,sum(CASE WHEN no IS NULL THEN 0 ELSE 1 END ) aa
FROM @t a right join @a b
on CHARINDEX(','+no+',', ','+no1+','+no2+','+no3+','+no4+','+no5+','+no6+',')>0
GROUP BY id
)aaa
这个意思?
DECLARE @t TABLE(id INT IDENTITY(1,1), no varchar(20))
INSERT @t SELECT '06,12,15,18,29,32'
UNION ALL SELECT '03,12,15,18,29,32'
UNION ALL SELECT '04,12,15,18,29,32'
UNION ALL SELECT '05,12,15,18,29,32'
UNION ALL SELECT '09,12,15,18,29,32'
DECLARE @a TABLE(id INT IDENTITY(1,1), no1 CHAR(2),no2 CHAR(2),no3 CHAR(2),no4 CHAR(2),no5 CHAR(2),no6 CHAR(2))
insert @a select '10','11','12','15','26','28'
union all select '06','12','15','20','21','29'
union all select '01','07','10','23','28','32'
union all select '04','06','07','10','13','25'
union all select '04','06','15','17','30','31'
union all select '01','03','10','21','26','27'
union all select '01','09','19','21','23','26'
union all select '05','08','09','14','17','23'
union all select '05','09','18','20','22','30'
union all select '01','02','08','13','17','24'
union all select '04','05','11','12','30','32'
union all select '02','12','16','17','27','30'
union all select '08','13','17','21','23','32'
union all select '03','05','07','08','21','31'
union all select '04','11','19','25','26','32'
union all select '11','17','28','30','31','33'
union all select '05','08','18','23','25','31'
union all select '05','16','19','20','25','28'
union all select '04','08','12','13','16','33'
union all select '07','10','25','26','27','32'
union all select '14','15','18','25','26','30'
union all select '02','06','11','12','15','32'
union all select '01','10','20','22','26','31'
union all select '02','07','15','17','22','30'
union all select '01','05','11','13','14','27'
union all select '08','13','15','26','29','31'
union all select '06','12','15','18','29','33'
union all select '06','12','17','28','29','31'
union all select '06','12','15','18','29','31'
union all select '06','12','15','18','29','32'
union all select '06','12','15','18','29','30'
SELECT id1 id,no, [0]=SUM(CASE WHEN aaa=0 THEN 1 ELSE 0 END ),
[1]=SUM(CASE WHEN aaa=1 THEN 1 ELSE 0 END),
[2]=SUM(CASE WHEN aaa=2 THEN 1 ELSE 0 END),
[3]=SUM(CASE WHEN aaa=3 THEN 1 ELSE 0 END),
[4]=SUM(CASE WHEN aaa=4 THEN 1 ELSE 0 END),
[5]=SUM(CASE WHEN aaa=5 THEN 1 ELSE 0 END),
[6]=SUM(CASE WHEN aaa=6 THEN 1 ELSE 0 END)
FROM
(
SELECT b.*,a.id id1,no,
CASE WHEN CHARINDEX(','+no1+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no2+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no3+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no4+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no5+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no6+',',','+no+',')>0 THEN 1 ELSE 0 END
aaa
FROM @t a,@a b
)aaa
GROUP BY id1,no
ORDER BY id1
--result
/*
id no 0 1 2 3 4 5 6
----------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 06,12,15,18,29,32 9 10 5 1 2 3 1
2 03,12,15,18,29,32 8 12 5 2 3 1 0
3 04,12,15,18,29,32 9 8 7 3 3 1 0
4 05,12,15,18,29,32 6 12 6 3 3 1 0
5 09,12,15,18,29,32 8 11 6 2 3 1 0
(所影响的行数为 5 行)
*/