红球号码有0、1、2、3、4、5、6个相同时如何统计出来

Dlsongzhi1984 2010-10-22 11:42:14
已知开奖号码是‘06 12 15 18 29 32’（假设为【TBKJ】）
历史号码的记录是（假设为【TBLS】）

NO1NO2NO3NO4NO5NO6
10 11 12 15 26 28
06 12 15 20 21 29
01 07 10 23 28 32
04 06 07 10 13 25
04 06 15 17 30 31
01 03 10 21 26 27
01 09 19 21 23 26
05 08 09 14 17 23
05 09 18 20 22 30
01 02 08 13 17 24
04 05 11 12 30 32
02 12 16 17 27 30
08 13 17 21 23 32
03 05 07 08 21 31
04 11 19 25 26 32
11 17 28 30 31 33
05 08 18 23 25 31
05 16 19 20 25 28
04 08 12 13 16 33
07 10 25 26 27 32
14 15 18 25 26 30
02 06 11 12 15 32
01 10 20 22 26 31
02 07 15 17 22 30
01 05 11 13 14 27
08 13 15 26 29 31
06 12 15 18 29 33
06 12 17 28 29 31
06 12 15 18 29 31
06 12 15 18 29 32
06 12 15 18 29 30

问题：
[TBKJ]和[TBLS]一一对比后，
和‘06 12 15 18 29 32’没有相同号码的历史记录【TBLS】里有9注，
有1个相同号码的历史记录有10注，
有2个号码相同的历史记录有5注，
有3个相同号码的历史记录有1注，
有4个号码相同的历史记录有2注，
有5个相同号码的历史记录有3注，6个号码都相同有1注，以此类推，执行结果为


ID       号     码     0同 1同 2同 3同 4同 5同 6同

--- -----------------  --- --- --- --- --- --- ---

1   06 12 15 18 29 32  9   10   5   1   2   3   1

请问如何用SQL语句完成，谢谢
...全文
316 点赞 收藏 21
写回复
21 条回复
切换为时间正序
当前发帖距今超过3年，不再开放新的回复
发表回复
SQLCenter 2010-10-25
[Quote=引用 19 楼 chuifengde 的回复:]

11楼不是跟你写好了吗？
[/Quote]

MSSQL疑难问题版版务帖【对Dlsongzhi1984与HsuChine封杀处理】
 回复 赞
chuifengde 2010-10-25
11楼不是跟你写好了吗？
 回复 赞
chuifengde 2010-10-22
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 行）



*/
 回复 赞
Dlsongzhi1984 2010-10-22


-- 本期开奖号码

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


怎么没有其他几个的统计数据？
 回复 赞
playwarcraft 2010-10-22
友情提醒，如果是想找出规律去买彩票期望中大奖，劝LZ还是放弃，可以随便买着玩玩，怡情即可。

如果是本着写SQL语句来请教，这里当然欢迎你，
针对这题，


--建立测试环境

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
回复 赞
Dlsongzhi1984 2010-10-22
楼上有几个长舌妇？长嘴夫的废话真多，凑热闹的可多呀......有帮帮俺滴？？？郁闷
 回复 赞
kevn 2010-10-22
[Quote=引用 4 楼 dlsongzhi1984 的回复:]
LZ不买彩票，只是学习如何写语句，写好了或许会给大家分享的，谢谢支持我的朋友
[/Quote]
好执着，看到过LZ好几个关于这样的帖子
 回复 赞
kevin87923 2010-10-22
很有可能是买彩票的。
 回复 赞
Dlsongzhi1984 2010-10-22
LZ不买彩票，只是学习如何写语句，写好了或许会给大家分享的，谢谢支持我的朋友
 回复 赞
水族杰纶 2010-10-22
[Quote=引用 1 楼 playwarcraft 的回复:]
哥们你上个题，有没有推导出第N个蓝色球的号码啊？共享一下，让我也赚点。。。
[/Quote]
同求发财
 回复 赞
--小F-- 2010-10-22
力气活啊 先看看
 回复 赞
playwarcraft 2010-10-22
哥们你上个题，有没有推导出第N个蓝色球的号码啊？共享一下，让我也赚点。。。
 回复 赞
Dlsongzhi1984 2010-10-22
[Quote=引用 14 楼 chuifengde 的回复:]
1.假设开奖数据有100W 条，我初步算了一下，大概是2700百种奖一年的数据，或1种奖2700年的数据，假设不大成立，就是成立，应该也没问题，慢而已
2.你的方案是先将100w开奖号拆分，再作or条件连接，如果我的说慢，估计你的就更慢了
[/Quote]

我是想学习如何多条数据对比
例如


-- 本期开奖号码

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'

尽管知道多条数据输入错了，但不知道如何修改，请教啦.....

回复 赞
jiao3630 2010-10-22
 回复 赞
Dlsongzhi1984 2010-10-22
[Quote=引用 15 楼 mengnnou 的回复:]
跟楼主发的上个帖子几乎一样的，代码只要稍作改动就行。无论楼主是因为工作、学习还是兴趣做这个，自己琢磨一下，举一反三，对自己总是有好处的。
[/Quote]
本着学习，改变着一些条件虚心请教，谢谢
 回复 赞
mengnnou 2010-10-22
跟楼主发的上个帖子几乎一样的，代码只要稍作改动就行。无论楼主是因为工作、学习还是兴趣做这个，自己琢磨一下，举一反三，对自己总是有好处的。
 回复 赞
chuifengde 2010-10-22
1.假设开奖数据有100W 条，我初步算了一下，大概是2700百种奖一年的数据，或1种奖2700年的数据，假设不大成立，就是成立，应该也没问题，慢而已
2.你的方案是先将100w开奖号拆分，再作or条件连接，如果我的说慢，估计你的就更慢了
 回复 赞
Dlsongzhi1984 2010-10-22
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
这是另一种方案行吗
 回复 赞
Dlsongzhi1984 2010-10-22
假设开奖数据有100W 条，在数据表[TBKJ]里

历史数据2000条在数据表[TBLS]里
那么对比数据就很多，查询能这样吗




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


请教楼上可以吗？
 回复 赞
chuifengde 2010-10-22
这个意思？

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 行）



*/
回复 赞
相关推荐
speaker.h speaker.h
2设计数据库、创建数据库和数据表.sql 2设计数据库、创建数据库和数据表.sql
perl-Module-CoreList-2.76.02-293.el7.noarch.rpm 离线安装包，亲测可用
glibc-utils-2.17-260.el7.x86_64.rpm 离线安装包，亲测可用
nepomuk-core-libs-4.10.5-6.el7.x86_64.rpm 离线安装包，亲测可用
gdk-pixbuf2-devel-2.36.12-3.el7.x86_64.rpm 离线安装包，亲测可用
texlive-bookman-svn28614.0-43.el7.noarch.rpm texlive-bookman-svn28614.0-43.el7.noarch.rpm
spice-gtk3-0.35-2.el7.x86_64.rpm 离线安装包，亲测可用
grub2-tools-minimal-2.02-0.76.el7.centos.x86_64.rpm 离线安装包，亲测可用
system-storage-manager-0.4-8.el7.noarch.rpm 离线安装包，亲测可用
springboot-06-security.zip SpringBoot之----SpringSecurity注销、权限控制、记住我、以及首页定制源码
大数据汇聚端网络安全架构 大数据网络安全架构模版Visio版
Server-Side-Template-Injection-RCE-For-The-Modern-Web-App-wp.pdf 2015年黑帽大会上 James Kettle 讲解了《Server-Side Template Injection: RCE for the modern webapp》，从服务端模板注入的形成到检测,再到验证和利用都进行了详细的介绍，本资源为原文。
rubygem-psych-2.0.0-33.el7_4.x86_64.rpm 离线安装包，亲测可用
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-22 11:42
社区公告
暂无公告