问题二: 假设开奖奖金的总金额不大于 7567 元,已知集合A,那么如何用SQL语句筛选出集合B里哪些是可以开奖的号码? 例如: select @num=notext FROM #tb2 where [序号]=1 奖金是11482592,不符合要求,不要这组号码 select @num=notext FROM #tb2 where [序号]=2 奖金是11482592,不符合要求,不要这组号码 select @num=notext FROM #tb2 where [序号]=3 奖金是11482592,不符合要求,不要这组号码select @num=notext FROM #tb2 where [序号]=4 奖金是3040,符合要求,选取这组号码 select @num=notext FROM #tb2 where [序号]=5 奖金是3050,符合要求,选取这组号码。 以此类推,如何筛选出集合B里符合要求的记录?请大侠援助,谢谢
如果需要,我可以按照要求弄成6个列,请大神援助,谢谢----> 建立数据表,条件一:简称集合A
if object_id('tempdb..#tb1') is not null drop table #tb1 CREATE TABLE #tb1([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb1 select '1','02','03','06','27','28','30','11' union all select '2','02','03','13','28','30','31','12' union all select '3','02','03','15','28','29','31','13' union all select '4','02','04','09','28','31','32','14' union all select '5','02','04','11','28','29','31','16' union all select '6','02','04','11','28','31','32','11' union all select '7','02','04','15','27','31','32','12' union all select '8','02','05','06','27','29','30','13' union all select '9','02','05','06','27','31','32','14' union all select '10','02','05','11','29','30','31','16' union all select '11','02','06','09','27','30','33','11' union all select '12','02','06','09','28','30','32','12' union all select '13','02','06','13','27','29','31','13' union all select '14','02','06','13','27','30','33','14' union all select '15','02','06','13','27','31','32','16' union all select '16','02','06','13','29','30','31','11' union all select '17','02','06','14','27','28','29','12' union all select '18','02','06','14','27','29','33','13' union all select '19','02','09','11','27','30','33','14' union all select '20','02','09','13','27','30','31','16' union all select '21','02','09','14','28','31','32','11' union all select '22','02','09','16','27','28','32','12' union all select '23','02','11','13','27','28','29','13' union all select '24','02','11','15','29','30','31','14' union all select '25','04','06','11','28','30','33','16' union all select '26','04','06','13','27','31','32','11' union all select '27','04','06','13','29','31','33','12' union all select '28','04','11','15','27','29','32','13'
----> 建立数据表,条件二:简称集合B
if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '1','02','03','06','27','28','30','11' union all select '2','02','03','13','28','30','31','12' union all select '3','02','03','15','28','29','31','13' union all select '4','03','04','09','28','31','32','14' union all select '5','03','04','11','28','29','31','16' union all select '6','03','04','11','28','31','32','11' union all select '7','03','04','15','27','31','32','12' union all select '8','04','05','06','27','29','30','13' union all select '9','04','05','06','27','31','32','14' union all select '10','04','05','11','29','30','31','16' union all select '11','04','06','09','27','30','33','11' union all select '12','04','06','09','28','30','32','12' union all select '13','04','06','13','27','29','31','13' union all select '14','04','06','13','27','30','33','14' union all select '15','04','06','13','27','31','32','16' union all select '16','04','06','13','29','30','31','11' union all select '17','04','06','14','27','28','29','12' union all select '18','04','06','14','27','29','33','13' union all select '19','04','09','11','27','30','33','14' union all select '20','04','09','13','27','30','31','16' union all select '21','04','09','14','28','31','32','11' union all select '22','04','09','16','27','28','32','12' union all select '23','04','11','13','27','28','29','13' union all select '24','04','11','15','29','30','31','14' union all select '25','04','06','11','28','30','33','16' union all select '26','04','06','13','27','31','32','11' union all select '27','04','06','13','29','31','33','12' union all select '28','04','06','13','29','31','33','13'
只为证明给彩民看~~ 同老虎之前的一个贴方法一样,参照用来的是二进制判断 下午没事练练手DECLARE @base bigint SET @base = 2IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb1Cte FROM #tb1IF OBJECT_ID('TempDB..#tb2Cte')IS NOT NULL DROP TABLE #tb2Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb2Cte FROM #tb2IF OBJECT_ID('TempDB..#result')IS NOT NULL DROP TABLE #result CREATE TABLE #result([序号1] [bigint] NULL,[序号2] [bigint] NULL,[jstatus] varchar(100)) --6+0 6+1 DELETE FROM T1 OUTPUT deleted.序号,T2.序号,CASE WHEN deleted.Blu=T2.Blu THEN '6+1' ELSE '6+0' END INTO #result FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value&T2.value=T2.valueIF OBJECT_ID('TempDB..#Number')IS NOT NULL DROP TABLE #Number SELECT 'No'+CAST(number AS VARCHAR(10))Nos INTO #Number FROM master..spt_values WHERE TYPE='P'AND number>=1 AND number<=6DECLARE @SQL VARCHAR(MAX)--5+0 5+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +'+POWER(@base,['+T4.Nos+']-1)' +'+POWER(@base,['+T5.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos JOIN #Number T4 on T3.Nos<T4.Nos JOIN #Number T5 on T4.Nos<T5.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) DELETE FROM T1 OUTPUT deleted.序号,T2.序号,CASE WHEN deleted.Blu=T2.Blu THEN '5+1' ELSE '5+0,4+1' END INTO #result FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value--4+0 4+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +'+POWER(@base,['+T4.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos JOIN #Number T4 on T3.Nos<T4.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) DELETE FROM T1 OUTPUT deleted.序号,T2.序号,CASE WHEN deleted.Blu=T2.Blu THEN '5+0,4+1' ELSE '4+0,3+1' END INTO #result FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value--3+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) DELETE FROM T1 OUTPUT deleted.序号,T2.序号,'4+0,3+1' INTO #result FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.Blu=T2.Blu AND (T1.value&T2.value)=T2.value--Blu 1 INSERT INTO #result SELECT T1.序号,T2.序号,'2+1,1+1,0+1' FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu=T2.BluSELECT T1.序号2 ,SUM(T2.jamount)jamount FROM #result T1 JOIN #tj T2 ON T1.jstatus=T2.jstatus GROUP BY T1.序号2 HAVING SUM(T2.jamount)<7567
首先感谢ky_min大师的指教,很受用,谢谢你。 现在有个问题请教 集合B里的每一行号码对于集合A而言,总奖金应该是一致的。 举例: 以第15行数据为例,如果仅仅取select '15','04','06','13','27','31','32','16'---总奖金为147259if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '15','04','06','13','27','31','32','16' ---总奖金为147259 如果28行全部取(包含select '15','04','06','13','27','31','32','16')但select '15','04','06','13','27','31','32','16'---总奖金为147194if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '1','02','03','06','27','28','30','11' union all select '2','02','03','13','28','30','31','12' union all select '3','02','03','15','28','29','31','13' union all select '4','03','04','09','28','31','32','14' union all select '5','03','04','11','28','29','31','16' union all select '6','03','04','11','28','31','32','11' union all select '7','03','04','15','27','31','32','12' union all select '8','04','05','06','27','29','30','13' union all select '9','04','05','06','27','31','32','14' union all select '10','04','05','11','29','30','31','16' union all select '11','04','06','09','27','30','33','11' union all select '12','04','06','09','28','30','32','12' union all select '13','04','06','13','27','29','31','13' union all select '14','04','06','13','27','30','33','14' union all select '15','04','06','13','27','31','32','16' union all -----总奖金为147194 select '16','04','06','13','29','30','31','11' union all select '17','04','06','14','27','28','29','12' union all select '18','04','06','14','27','29','33','13' union all select '19','04','09','11','27','30','33','14' union all select '20','04','09','13','27','30','31','16' union all select '21','04','09','14','28','31','32','11' union all select '22','04','09','16','27','28','32','12' union all select '23','04','11','13','27','28','29','13' union all select '24','04','11','15','29','30','31','14' union all select '25','04','06','11','28','30','33','16' union all select '26','04','06','13','27','31','32','11' union all select '27','04','06','13','29','31','33','12' union all select '28','04','06','13','29','31','33','13'问题:同一组号码'04','06','13','27','31','32','16' 对于集合A而言,总奖金应该是一致的,而实际验证有2个结果,找了很久,就不知道原因在哪里?请大师赐教。
首先感谢ky_min大师的指教,很受用,谢谢你。 现在有个问题请教 集合B里的每一行号码对于集合A而言,总奖金应该是一致的。 举例: 以第15行数据为例,如果仅仅取select '15','04','06','13','27','31','32','16'---总奖金为147259if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '15','04','06','13','27','31','32','16' ---总奖金为147259 如果28行全部取(包含select '15','04','06','13','27','31','32','16')但select '15','04','06','13','27','31','32','16'---总奖金为147194if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '1','02','03','06','27','28','30','11' union all select '2','02','03','13','28','30','31','12' union all select '3','02','03','15','28','29','31','13' union all select '4','03','04','09','28','31','32','14' union all select '5','03','04','11','28','29','31','16' union all select '6','03','04','11','28','31','32','11' union all select '7','03','04','15','27','31','32','12' union all select '8','04','05','06','27','29','30','13' union all select '9','04','05','06','27','31','32','14' union all select '10','04','05','11','29','30','31','16' union all select '11','04','06','09','27','30','33','11' union all select '12','04','06','09','28','30','32','12' union all select '13','04','06','13','27','29','31','13' union all select '14','04','06','13','27','30','33','14' union all select '15','04','06','13','27','31','32','16' union all -----总奖金为147194 select '16','04','06','13','29','30','31','11' union all select '17','04','06','14','27','28','29','12' union all select '18','04','06','14','27','29','33','13' union all select '19','04','09','11','27','30','33','14' union all select '20','04','09','13','27','30','31','16' union all select '21','04','09','14','28','31','32','11' union all select '22','04','09','16','27','28','32','12' union all select '23','04','11','13','27','28','29','13' union all select '24','04','11','15','29','30','31','14' union all select '25','04','06','11','28','30','33','16' union all select '26','04','06','13','27','31','32','11' union all select '27','04','06','13','29','31','33','12' union all select '28','04','06','13','29','31','33','13'问题:同一组号码'04','06','13','27','31','32','16' 对于集合A而言,总奖金应该是一致的,而实际验证有2个结果,找了很久,就不知道原因在哪里?请大师赐教。
首先感谢ky_min大师的指教,很受用,谢谢你。 现在有个问题请教 集合B里的每一行号码对于集合A而言,总奖金应该是一致的。 举例: 以第15行数据为例,如果仅仅取select '15','04','06','13','27','31','32','16'---总奖金为147259if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '15','04','06','13','27','31','32','16' ---总奖金为147259 如果28行全部取(包含select '15','04','06','13','27','31','32','16')但select '15','04','06','13','27','31','32','16'---总奖金为147194if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '1','02','03','06','27','28','30','11' union all select '2','02','03','13','28','30','31','12' union all select '3','02','03','15','28','29','31','13' union all select '4','03','04','09','28','31','32','14' union all select '5','03','04','11','28','29','31','16' union all select '6','03','04','11','28','31','32','11' union all select '7','03','04','15','27','31','32','12' union all select '8','04','05','06','27','29','30','13' union all select '9','04','05','06','27','31','32','14' union all select '10','04','05','11','29','30','31','16' union all select '11','04','06','09','27','30','33','11' union all select '12','04','06','09','28','30','32','12' union all select '13','04','06','13','27','29','31','13' union all select '14','04','06','13','27','30','33','14' union all select '15','04','06','13','27','31','32','16' union all -----总奖金为147194 select '16','04','06','13','29','30','31','11' union all select '17','04','06','14','27','28','29','12' union all select '18','04','06','14','27','29','33','13' union all select '19','04','09','11','27','30','33','14' union all select '20','04','09','13','27','30','31','16' union all select '21','04','09','14','28','31','32','11' union all select '22','04','09','16','27','28','32','12' union all select '23','04','11','13','27','28','29','13' union all select '24','04','11','15','29','30','31','14' union all select '25','04','06','11','28','30','33','16' union all select '26','04','06','13','27','31','32','11' union all select '27','04','06','13','29','31','33','12' union all select '28','04','06','13','29','31','33','13'问题:同一组号码'04','06','13','27','31','32','16' 对于集合A而言,总奖金应该是一致的,而实际验证有2个结果,找了很久,就不知道原因在哪里?请大师赐教。
已处理,另外有一个6+0的奖金怎么会是147194DECLARE @base bigint SET @base = 2IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb1Cte FROM #tb1IF OBJECT_ID('TempDB..#tb2Cte')IS NOT NULL DROP TABLE #tb2Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb2Cte FROM #tb2IF OBJECT_ID('TempDB..#result')IS NOT NULL DROP TABLE #result CREATE TABLE #result([序号1] [bigint] NULL,[序号2] [bigint] NULL,[jstatus] varchar(100)) --6+0 6+1 INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '6+1' ELSE '6+0' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value=T2.value DELETE FROM T1 FROM #tb1Cte T1 JOIN #result T2 ON T1.序号=T2.序号1IF OBJECT_ID('TempDB..#Number')IS NOT NULL DROP TABLE #Number SELECT 'No'+CAST(number AS VARCHAR(10))Nos INTO #Number FROM master..spt_values WHERE TYPE='P'AND number>=1 AND number<=6DECLARE @SQL VARCHAR(MAX)--5+0 5+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +'+POWER(@base,['+T4.Nos+']-1)' +'+POWER(@base,['+T5.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos JOIN #Number T4 on T3.Nos<T4.Nos JOIN #Number T5 on T4.Nos<T5.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+1' ELSE '5+0,4+1' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value DELETE FROM T1 FROM #tb1Cte T1 JOIN #result T2 ON T1.序号=T2.序号1--4+0 4+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +'+POWER(@base,['+T4.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos JOIN #Number T4 on T3.Nos<T4.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+0,4+1' ELSE '4+0,3+1' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value DELETE FROM T1 FROM #tb1Cte T1 JOIN #result T2 ON T1.序号=T2.序号1--3+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) INSERT INTO #result SELECT T1.序号,T2.序号,'4+0,3+1' FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value DELETE FROM T1 FROM #tb1Cte T1 JOIN #result T2 ON T1.序号=T2.序号1--Blu 1 INSERT INTO #result SELECT T1.序号,T2.序号,'2+1,1+1,0+1' FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu=T2.Blu--SELECT * FROM #result T1 -- JOIN #tj T2 ON T1.jstatus=T2.jstatus --ORDER BY 序号1SELECT T1.序号2 ,SUM(T2.jamount)jamount FROM #result T1 JOIN #tj T2 ON T1.jstatus=T2.jstatus GROUP BY T1.序号2 --HAVING SUM(T2.jamount)<7567
首先感谢ky_min大师的指教,很受用,好像处理过了还是2个不同的总奖金。集合B里的每一行号码对于集合A而言,总奖金应该是一致的。,以这个奖级表为准,请你核查 ----建立奖级表 #tj -- select * from #33 where 奖金>=1000 select * from #tj create table #tj (jname varchar(10), -- 奖级 jstatus varchar(100), -- 中奖条件 jamount int -- 奖金 ) insert into #tj select '一等奖','6+1',6335444 union all select '二等奖','6+0',144194 union all select '三等奖','5+1',3000 union all select '四等奖','5+0,4+1',200 union all select '五等奖','4+0,3+1',10 union all select '六等奖','2+1,1+1,0+1',5 举例: 以第15行数据为例,如果仅仅取select '15','04','06','13','27','31','32','16'---总奖金为147309if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '15','04','06','13','27','31','32','16' ---总奖金为147309 结果是序号2 jamount 15 147309 如果28行全部取(包含select '15','04','06','13','27','31','32','16')但select '15','04','06','13','27','31','32','16'---总奖金为147194if object_id('tempdb..#tb2') is not null drop table #tb2 CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int]) insert #tb2 select '1','02','03','06','27','28','30','11' union all select '2','02','03','13','28','30','31','12' union all select '3','02','03','15','28','29','31','13' union all select '4','03','04','09','28','31','32','14' union all select '5','03','04','11','28','29','31','16' union all select '6','03','04','11','28','31','32','11' union all select '7','03','04','15','27','31','32','12' union all select '8','04','05','06','27','29','30','13' union all select '9','04','05','06','27','31','32','14' union all select '10','04','05','11','29','30','31','16' union all select '11','04','06','09','27','30','33','11' union all select '12','04','06','09','28','30','32','12' union all select '13','04','06','13','27','29','31','13' union all select '14','04','06','13','27','30','33','14' union all select '15','04','06','13','27','31','32','16' union all -----总奖金为147194 select '16','04','06','13','29','30','31','11' union all select '17','04','06','14','27','28','29','12' union all select '18','04','06','14','27','29','33','13' union all select '19','04','09','11','27','30','33','14' union all select '20','04','09','13','27','30','31','16' union all select '21','04','09','14','28','31','32','11' union all select '22','04','09','16','27','28','32','12' union all select '23','04','11','13','27','28','29','13' union all select '24','04','11','15','29','30','31','14' union all select '25','04','06','11','28','30','33','16' union all select '26','04','06','13','27','31','32','11' union all select '27','04','06','13','29','31','33','12' union all select '28','04','06','13','29','31','33','13' 结果是序号2 jamount 15 147194 问题:同一组号码'04','06','13','27','31','32','16' 对于集合A而言,总奖金应该是一致的,而实际验证有2个结果,找了很久,就不知道原因在哪里?请大师z再次赐教。
发现原因了,这回你可以先自己校验一下一个简单点的数据,再批量看下DECLARE @base bigint SET @base = 2
IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb1Cte FROM #tb1
IF OBJECT_ID('TempDB..#tb2Cte')IS NOT NULL DROP TABLE #tb2Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb2Cte FROM #tb2
IF OBJECT_ID('TempDB..#result')IS NOT NULL DROP TABLE #result CREATE TABLE #result([序号1] [bigint] NULL,[序号2] [bigint] NULL,[jstatus] varchar(100)) --6+0 6+1 INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '6+1' ELSE '6+0' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value=T2.valueIF OBJECT_ID('TempDB..#Number')IS NOT NULL DROP TABLE #Number SELECT 'No'+CAST(number AS VARCHAR(10))Nos INTO #Number FROM master..spt_values WHERE TYPE='P'AND number>=1 AND number<=6
DECLARE @SQL VARCHAR(MAX)
--5+0 5+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +'+POWER(@base,['+T4.Nos+']-1)' +'+POWER(@base,['+T5.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos JOIN #Number T4 on T3.Nos<T4.Nos JOIN #Number T5 on T4.Nos<T5.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+1' ELSE '5+0,4+1' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2 WHERE T3.序号1 IS NULL
--4+0 4+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +'+POWER(@base,['+T4.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos JOIN #Number T4 on T3.Nos<T4.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+0,4+1' ELSE '4+0,3+1' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2 WHERE T3.序号1 IS NULL
--3+1 SET @SQL='' SELECT @SQL=@SQL+' UNION ALL SELECT *,' +'POWER(@base,['+T1.Nos+']-1)' +'+POWER(@base,['+T2.Nos+']-1)' +'+POWER(@base,['+T3.Nos+']-1)' +' FROM #tb2' FROM #Number T1 JOIN #Number T2 on T1.Nos<T2.Nos JOIN #Number T3 on T2.Nos<T3.Nos SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'') TRUNCATE TABLE #tb2Cte INSERT INTO #tb2Cte EXEC(@SQL) INSERT INTO #result SELECT T1.序号,T2.序号,'4+0,3+1' FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2 WHERE T3.序号1 IS NULL
--Blu 1 INSERT INTO #result SELECT T1.序号,T2.序号,'2+1,1+1,0+1' FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu=T2.Blu LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2 WHERE T3.序号1 IS NULL--SELECT * FROM #result T1 -- JOIN #tj T2 ON T1.jstatus=T2.jstatus --ORDER BY 序号1/*SELECT T1.序号2 ,SUM(T2.jamount)jamount FROM #result T1 JOIN #tj T2 ON T1.jstatus=T2.jstatus GROUP BY T1.序号2 --HAVING SUM(T2.jamount)<7567
谢谢大师ky_min 的回复,结贴全给分,我慢慢消化一下,非常感谢!!! 求说明与讲解 IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb1Cte FROM #tb1请详细指教一下, #tb1Cte的value是什么? 还要请教一个问题,如果集合A和集合B的记录超过50W的时候,跑了近3个小时还没有结果,能优化提速吗?谢谢
那个是自己建的列,延用老虎的命名 我优化了一下,如果TB2表不多的话,还是我之前那个 如果50W的话~~,试下这个 这个处理方式,近乎是两个表相乘的速度DECLARE @base bigint SET @base = 2
IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb1Cte FROM #tb1
IF OBJECT_ID('TempDB..#tb2Cte')IS NOT NULL DROP TABLE #tb2Cte SELECT * ,POWER(@base,no1-1) + POWER(@base,no2-1) + POWER(@base,no3-1) + POWER(@base,no4-1) + POWER(@base,no5-1) + POWER(@base,no6-1) AS value INTO #tb2Cte FROM #tb2
IF OBJECT_ID('TempDB..#result')IS NOT NULL DROP TABLE #result CREATE TABLE #result([序号1] [bigint] NULL,[序号2] [bigint] NULL,[jstatus] varchar(100)) --6+0 6+1 INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '6+1' ELSE '6+0' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value=T2.valueIF OBJECT_ID('TempDB..#Number')IS NOT NULL DROP TABLE #Number SELECT POWER(@base,number) value INTO #Number FROM master..spt_values WHERE TYPE='P'AND number<36
--5+0 5+1 --Start IF OBJECT_ID('TempDB..#2bitDiff')IS NOT NULL DROP TABLE #2bitDiff SELECT T1.value + T2.value value INTO #2bitDiff FROM #Number T1 JOIN #Number T2 ON T1.value < T2.value
INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+1' ELSE '5+0,4+1' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value^T2.value)IN(SELECT value FROM #2bitDiff) LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2 WHERE T3.序号1 IS NULL --end
--4+0 4+1 --Start IF OBJECT_ID('TempDB..#4bitDiff')IS NOT NULL DROP TABLE #4bitDiff SELECT T1.value + T2.value + T3.value + T4.value value INTO #4bitDiff FROM #Number T1 JOIN #Number T2 ON T1.value < T2.value JOIN #Number T3 ON T2.value < T3.value JOIN #Number T4 ON T3.value < T4.value
INSERT INTO #result SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+0,4+1' ELSE '4+0,3+1' END FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value^T2.value)IN(SELECT value FROM #4bitDiff) LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2 WHERE T3.序号1 IS NULL --end
--3+1 --Start IF OBJECT_ID('TempDB..#3bitDiff')IS NOT NULL DROP TABLE #3bitDiff SELECT T1.value + T2.value + T3.value value INTO #3bitDiff FROM #Number T1 JOIN #Number T2 ON T1.value < T2.value JOIN #Number T3 ON T2.value < T3.value INSERT INTO #result SELECT T1.序号,T2.序号,'4+0,3+1' FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)IN(SELECT value FROM #3bitDiff) LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2 WHERE T3.序号1 IS NULL
--Blu 1 INSERT INTO #result SELECT T1.序号,T2.序号,'2+1,1+1,0+1' FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu=T2.Blu LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2 WHERE T3.序号1 IS NULL--SELECT * FROM #result T1 -- JOIN #tj T2 ON T1.jstatus=T2.jstatus --ORDER BY 序号1,序号2/*SELECT T1.序号2 ,SUM(T2.jamount)jamount FROM #result T1 JOIN #tj T2 ON T1.jstatus=T2.jstatus GROUP BY T1.序号2 --HAVING SUM(T2.jamount)<7567
假设开奖奖金的总金额不大于 7567 元,已知集合A,那么如何用SQL语句筛选出集合B里哪些是可以开奖的号码?
例如:
select @num=notext FROM #tb2 where [序号]=1 奖金是11482592,不符合要求,不要这组号码
select @num=notext FROM #tb2 where [序号]=2 奖金是11482592,不符合要求,不要这组号码
select @num=notext FROM #tb2 where [序号]=3 奖金是11482592,不符合要求,不要这组号码select @num=notext FROM #tb2 where [序号]=4 奖金是3040,符合要求,选取这组号码
select @num=notext FROM #tb2 where [序号]=5 奖金是3050,符合要求,选取这组号码。
以此类推,如何筛选出集合B里符合要求的记录?请大侠援助,谢谢
if object_id('tempdb..#tb1') is not null drop table #tb1
CREATE TABLE #tb1([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb1
select '1','02','03','06','27','28','30','11' union all
select '2','02','03','13','28','30','31','12' union all
select '3','02','03','15','28','29','31','13' union all
select '4','02','04','09','28','31','32','14' union all
select '5','02','04','11','28','29','31','16' union all
select '6','02','04','11','28','31','32','11' union all
select '7','02','04','15','27','31','32','12' union all
select '8','02','05','06','27','29','30','13' union all
select '9','02','05','06','27','31','32','14' union all
select '10','02','05','11','29','30','31','16' union all
select '11','02','06','09','27','30','33','11' union all
select '12','02','06','09','28','30','32','12' union all
select '13','02','06','13','27','29','31','13' union all
select '14','02','06','13','27','30','33','14' union all
select '15','02','06','13','27','31','32','16' union all
select '16','02','06','13','29','30','31','11' union all
select '17','02','06','14','27','28','29','12' union all
select '18','02','06','14','27','29','33','13' union all
select '19','02','09','11','27','30','33','14' union all
select '20','02','09','13','27','30','31','16' union all
select '21','02','09','14','28','31','32','11' union all
select '22','02','09','16','27','28','32','12' union all
select '23','02','11','13','27','28','29','13' union all
select '24','02','11','15','29','30','31','14' union all
select '25','04','06','11','28','30','33','16' union all
select '26','04','06','13','27','31','32','11' union all
select '27','04','06','13','29','31','33','12' union all
select '28','04','11','15','27','29','32','13'
----> 建立数据表,条件二:简称集合B
if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '1','02','03','06','27','28','30','11' union all
select '2','02','03','13','28','30','31','12' union all
select '3','02','03','15','28','29','31','13' union all
select '4','03','04','09','28','31','32','14' union all
select '5','03','04','11','28','29','31','16' union all
select '6','03','04','11','28','31','32','11' union all
select '7','03','04','15','27','31','32','12' union all
select '8','04','05','06','27','29','30','13' union all
select '9','04','05','06','27','31','32','14' union all
select '10','04','05','11','29','30','31','16' union all
select '11','04','06','09','27','30','33','11' union all
select '12','04','06','09','28','30','32','12' union all
select '13','04','06','13','27','29','31','13' union all
select '14','04','06','13','27','30','33','14' union all
select '15','04','06','13','27','31','32','16' union all
select '16','04','06','13','29','30','31','11' union all
select '17','04','06','14','27','28','29','12' union all
select '18','04','06','14','27','29','33','13' union all
select '19','04','09','11','27','30','33','14' union all
select '20','04','09','13','27','30','31','16' union all
select '21','04','09','14','28','31','32','11' union all
select '22','04','09','16','27','28','32','12' union all
select '23','04','11','13','27','28','29','13' union all
select '24','04','11','15','29','30','31','14' union all
select '25','04','06','11','28','30','33','16' union all
select '26','04','06','13','27','31','32','11' union all
select '27','04','06','13','29','31','33','12' union all
select '28','04','06','13','29','31','33','13'
回复于: 2014-12-04 09:04:43
引用 3 楼 rockyljt 的回复:
找个软件开发的工作,实践中学习和成长了,这样来得快
言之有理!推荐娱乐一下双色球,学习SQL超快
言之有理,我也不买菜票,可同事好友是个彩迷,学SQL是从双色球入手的,一直接触的是双色球,所以有点基础,再转到其他的,没有合适的任务,也提高不快,娱乐的同时,学习SQL不枯燥啊,愿意帮助的大神请帮忙修改吧,谢谢三人行必有我师焉,但无法强人所难,谢谢楼上所有的提醒和建议
同老虎之前的一个贴方法一样,参照用来的是二进制判断
下午没事练练手DECLARE @base bigint
SET @base = 2IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb1Cte
FROM #tb1IF OBJECT_ID('TempDB..#tb2Cte')IS NOT NULL DROP TABLE #tb2Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb2Cte
FROM #tb2IF OBJECT_ID('TempDB..#result')IS NOT NULL DROP TABLE #result
CREATE TABLE #result([序号1] [bigint] NULL,[序号2] [bigint] NULL,[jstatus] varchar(100))
--6+0 6+1
DELETE FROM T1
OUTPUT deleted.序号,T2.序号,CASE WHEN deleted.Blu=T2.Blu THEN '6+1' ELSE '6+0' END INTO #result
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value&T2.value=T2.valueIF OBJECT_ID('TempDB..#Number')IS NOT NULL DROP TABLE #Number
SELECT 'No'+CAST(number AS VARCHAR(10))Nos
INTO #Number
FROM master..spt_values
WHERE TYPE='P'AND number>=1 AND number<=6DECLARE @SQL VARCHAR(MAX)--5+0 5+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+'+POWER(@base,['+T4.Nos+']-1)'
+'+POWER(@base,['+T5.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
JOIN #Number T4 on T3.Nos<T4.Nos
JOIN #Number T5 on T4.Nos<T5.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
DELETE FROM T1
OUTPUT deleted.序号,T2.序号,CASE WHEN deleted.Blu=T2.Blu THEN '5+1' ELSE '5+0,4+1' END INTO #result
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value--4+0 4+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+'+POWER(@base,['+T4.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
JOIN #Number T4 on T3.Nos<T4.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
DELETE FROM T1
OUTPUT deleted.序号,T2.序号,CASE WHEN deleted.Blu=T2.Blu THEN '5+0,4+1' ELSE '4+0,3+1' END INTO #result
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value--3+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
DELETE FROM T1
OUTPUT deleted.序号,T2.序号,'4+0,3+1' INTO #result
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.Blu=T2.Blu AND (T1.value&T2.value)=T2.value--Blu 1
INSERT INTO #result
SELECT T1.序号,T2.序号,'2+1,1+1,0+1'
FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu=T2.BluSELECT T1.序号2
,SUM(T2.jamount)jamount
FROM #result T1
JOIN #tj T2 ON T1.jstatus=T2.jstatus
GROUP BY T1.序号2
HAVING SUM(T2.jamount)<7567
首先感谢ky_min大师的指教,很受用,谢谢你。
现在有个问题请教
集合B里的每一行号码对于集合A而言,总奖金应该是一致的。
举例:
以第15行数据为例,如果仅仅取select '15','04','06','13','27','31','32','16'---总奖金为147259if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '15','04','06','13','27','31','32','16' ---总奖金为147259
如果28行全部取(包含select '15','04','06','13','27','31','32','16')但select '15','04','06','13','27','31','32','16'---总奖金为147194if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '1','02','03','06','27','28','30','11' union all
select '2','02','03','13','28','30','31','12' union all
select '3','02','03','15','28','29','31','13' union all
select '4','03','04','09','28','31','32','14' union all
select '5','03','04','11','28','29','31','16' union all
select '6','03','04','11','28','31','32','11' union all
select '7','03','04','15','27','31','32','12' union all
select '8','04','05','06','27','29','30','13' union all
select '9','04','05','06','27','31','32','14' union all
select '10','04','05','11','29','30','31','16' union all
select '11','04','06','09','27','30','33','11' union all
select '12','04','06','09','28','30','32','12' union all
select '13','04','06','13','27','29','31','13' union all
select '14','04','06','13','27','30','33','14' union all
select '15','04','06','13','27','31','32','16' union all -----总奖金为147194
select '16','04','06','13','29','30','31','11' union all
select '17','04','06','14','27','28','29','12' union all
select '18','04','06','14','27','29','33','13' union all
select '19','04','09','11','27','30','33','14' union all
select '20','04','09','13','27','30','31','16' union all
select '21','04','09','14','28','31','32','11' union all
select '22','04','09','16','27','28','32','12' union all
select '23','04','11','13','27','28','29','13' union all
select '24','04','11','15','29','30','31','14' union all
select '25','04','06','11','28','30','33','16' union all
select '26','04','06','13','27','31','32','11' union all
select '27','04','06','13','29','31','33','12' union all
select '28','04','06','13','29','31','33','13'问题:同一组号码'04','06','13','27','31','32','16' 对于集合A而言,总奖金应该是一致的,而实际验证有2个结果,找了很久,就不知道原因在哪里?请大师赐教。
首先感谢ky_min大师的指教,很受用,谢谢你。
现在有个问题请教
集合B里的每一行号码对于集合A而言,总奖金应该是一致的。
举例:
以第15行数据为例,如果仅仅取select '15','04','06','13','27','31','32','16'---总奖金为147259if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '15','04','06','13','27','31','32','16' ---总奖金为147259
如果28行全部取(包含select '15','04','06','13','27','31','32','16')但select '15','04','06','13','27','31','32','16'---总奖金为147194if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '1','02','03','06','27','28','30','11' union all
select '2','02','03','13','28','30','31','12' union all
select '3','02','03','15','28','29','31','13' union all
select '4','03','04','09','28','31','32','14' union all
select '5','03','04','11','28','29','31','16' union all
select '6','03','04','11','28','31','32','11' union all
select '7','03','04','15','27','31','32','12' union all
select '8','04','05','06','27','29','30','13' union all
select '9','04','05','06','27','31','32','14' union all
select '10','04','05','11','29','30','31','16' union all
select '11','04','06','09','27','30','33','11' union all
select '12','04','06','09','28','30','32','12' union all
select '13','04','06','13','27','29','31','13' union all
select '14','04','06','13','27','30','33','14' union all
select '15','04','06','13','27','31','32','16' union all -----总奖金为147194
select '16','04','06','13','29','30','31','11' union all
select '17','04','06','14','27','28','29','12' union all
select '18','04','06','14','27','29','33','13' union all
select '19','04','09','11','27','30','33','14' union all
select '20','04','09','13','27','30','31','16' union all
select '21','04','09','14','28','31','32','11' union all
select '22','04','09','16','27','28','32','12' union all
select '23','04','11','13','27','28','29','13' union all
select '24','04','11','15','29','30','31','14' union all
select '25','04','06','11','28','30','33','16' union all
select '26','04','06','13','27','31','32','11' union all
select '27','04','06','13','29','31','33','12' union all
select '28','04','06','13','29','31','33','13'问题:同一组号码'04','06','13','27','31','32','16' 对于集合A而言,总奖金应该是一致的,而实际验证有2个结果,找了很久,就不知道原因在哪里?请大师赐教。
首先感谢ky_min大师的指教,很受用,谢谢你。
现在有个问题请教
集合B里的每一行号码对于集合A而言,总奖金应该是一致的。
举例:
以第15行数据为例,如果仅仅取select '15','04','06','13','27','31','32','16'---总奖金为147259if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '15','04','06','13','27','31','32','16' ---总奖金为147259
如果28行全部取(包含select '15','04','06','13','27','31','32','16')但select '15','04','06','13','27','31','32','16'---总奖金为147194if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '1','02','03','06','27','28','30','11' union all
select '2','02','03','13','28','30','31','12' union all
select '3','02','03','15','28','29','31','13' union all
select '4','03','04','09','28','31','32','14' union all
select '5','03','04','11','28','29','31','16' union all
select '6','03','04','11','28','31','32','11' union all
select '7','03','04','15','27','31','32','12' union all
select '8','04','05','06','27','29','30','13' union all
select '9','04','05','06','27','31','32','14' union all
select '10','04','05','11','29','30','31','16' union all
select '11','04','06','09','27','30','33','11' union all
select '12','04','06','09','28','30','32','12' union all
select '13','04','06','13','27','29','31','13' union all
select '14','04','06','13','27','30','33','14' union all
select '15','04','06','13','27','31','32','16' union all -----总奖金为147194
select '16','04','06','13','29','30','31','11' union all
select '17','04','06','14','27','28','29','12' union all
select '18','04','06','14','27','29','33','13' union all
select '19','04','09','11','27','30','33','14' union all
select '20','04','09','13','27','30','31','16' union all
select '21','04','09','14','28','31','32','11' union all
select '22','04','09','16','27','28','32','12' union all
select '23','04','11','13','27','28','29','13' union all
select '24','04','11','15','29','30','31','14' union all
select '25','04','06','11','28','30','33','16' union all
select '26','04','06','13','27','31','32','11' union all
select '27','04','06','13','29','31','33','12' union all
select '28','04','06','13','29','31','33','13'问题:同一组号码'04','06','13','27','31','32','16' 对于集合A而言,总奖金应该是一致的,而实际验证有2个结果,找了很久,就不知道原因在哪里?请大师赐教。
SET @base = 2IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb1Cte
FROM #tb1IF OBJECT_ID('TempDB..#tb2Cte')IS NOT NULL DROP TABLE #tb2Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb2Cte
FROM #tb2IF OBJECT_ID('TempDB..#result')IS NOT NULL DROP TABLE #result
CREATE TABLE #result([序号1] [bigint] NULL,[序号2] [bigint] NULL,[jstatus] varchar(100))
--6+0 6+1
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '6+1' ELSE '6+0' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value=T2.value
DELETE FROM T1
FROM #tb1Cte T1 JOIN #result T2 ON T1.序号=T2.序号1IF OBJECT_ID('TempDB..#Number')IS NOT NULL DROP TABLE #Number
SELECT 'No'+CAST(number AS VARCHAR(10))Nos
INTO #Number
FROM master..spt_values
WHERE TYPE='P'AND number>=1 AND number<=6DECLARE @SQL VARCHAR(MAX)--5+0 5+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+'+POWER(@base,['+T4.Nos+']-1)'
+'+POWER(@base,['+T5.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
JOIN #Number T4 on T3.Nos<T4.Nos
JOIN #Number T5 on T4.Nos<T5.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+1' ELSE '5+0,4+1' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value
DELETE FROM T1
FROM #tb1Cte T1 JOIN #result T2 ON T1.序号=T2.序号1--4+0 4+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+'+POWER(@base,['+T4.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
JOIN #Number T4 on T3.Nos<T4.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+0,4+1' ELSE '4+0,3+1' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value
DELETE FROM T1
FROM #tb1Cte T1 JOIN #result T2 ON T1.序号=T2.序号1--3+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
INSERT INTO #result
SELECT T1.序号,T2.序号,'4+0,3+1'
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value
DELETE FROM T1
FROM #tb1Cte T1 JOIN #result T2 ON T1.序号=T2.序号1--Blu 1
INSERT INTO #result
SELECT T1.序号,T2.序号,'2+1,1+1,0+1'
FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu=T2.Blu--SELECT * FROM #result T1
-- JOIN #tj T2 ON T1.jstatus=T2.jstatus
--ORDER BY 序号1SELECT T1.序号2
,SUM(T2.jamount)jamount
FROM #result T1
JOIN #tj T2 ON T1.jstatus=T2.jstatus
GROUP BY T1.序号2
--HAVING SUM(T2.jamount)<7567
首先感谢ky_min大师的指教,很受用,好像处理过了还是2个不同的总奖金。集合B里的每一行号码对于集合A而言,总奖金应该是一致的。,以这个奖级表为准,请你核查
----建立奖级表 #tj -- select * from #33 where 奖金>=1000 select * from #tj
create table #tj
(jname varchar(10), -- 奖级
jstatus varchar(100), -- 中奖条件
jamount int -- 奖金
)
insert into #tj
select '一等奖','6+1',6335444 union all
select '二等奖','6+0',144194 union all
select '三等奖','5+1',3000 union all
select '四等奖','5+0,4+1',200 union all
select '五等奖','4+0,3+1',10 union all
select '六等奖','2+1,1+1,0+1',5
举例:
以第15行数据为例,如果仅仅取select '15','04','06','13','27','31','32','16'---总奖金为147309if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '15','04','06','13','27','31','32','16' ---总奖金为147309
结果是序号2 jamount
15 147309
如果28行全部取(包含select '15','04','06','13','27','31','32','16')但select '15','04','06','13','27','31','32','16'---总奖金为147194if object_id('tempdb..#tb2') is not null drop table #tb2
CREATE TABLE #tb2([序号] [bigint] NULL,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [NO6] [int], [Blu] [int])
insert #tb2
select '1','02','03','06','27','28','30','11' union all
select '2','02','03','13','28','30','31','12' union all
select '3','02','03','15','28','29','31','13' union all
select '4','03','04','09','28','31','32','14' union all
select '5','03','04','11','28','29','31','16' union all
select '6','03','04','11','28','31','32','11' union all
select '7','03','04','15','27','31','32','12' union all
select '8','04','05','06','27','29','30','13' union all
select '9','04','05','06','27','31','32','14' union all
select '10','04','05','11','29','30','31','16' union all
select '11','04','06','09','27','30','33','11' union all
select '12','04','06','09','28','30','32','12' union all
select '13','04','06','13','27','29','31','13' union all
select '14','04','06','13','27','30','33','14' union all
select '15','04','06','13','27','31','32','16' union all -----总奖金为147194
select '16','04','06','13','29','30','31','11' union all
select '17','04','06','14','27','28','29','12' union all
select '18','04','06','14','27','29','33','13' union all
select '19','04','09','11','27','30','33','14' union all
select '20','04','09','13','27','30','31','16' union all
select '21','04','09','14','28','31','32','11' union all
select '22','04','09','16','27','28','32','12' union all
select '23','04','11','13','27','28','29','13' union all
select '24','04','11','15','29','30','31','14' union all
select '25','04','06','11','28','30','33','16' union all
select '26','04','06','13','27','31','32','11' union all
select '27','04','06','13','29','31','33','12' union all
select '28','04','06','13','29','31','33','13'
结果是序号2 jamount
15 147194
问题:同一组号码'04','06','13','27','31','32','16' 对于集合A而言,总奖金应该是一致的,而实际验证有2个结果,找了很久,就不知道原因在哪里?请大师z再次赐教。
SET @base = 2
IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb1Cte
FROM #tb1
IF OBJECT_ID('TempDB..#tb2Cte')IS NOT NULL DROP TABLE #tb2Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb2Cte
FROM #tb2
IF OBJECT_ID('TempDB..#result')IS NOT NULL DROP TABLE #result
CREATE TABLE #result([序号1] [bigint] NULL,[序号2] [bigint] NULL,[jstatus] varchar(100))
--6+0 6+1
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '6+1' ELSE '6+0' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value=T2.valueIF OBJECT_ID('TempDB..#Number')IS NOT NULL DROP TABLE #Number
SELECT 'No'+CAST(number AS VARCHAR(10))Nos
INTO #Number
FROM master..spt_values
WHERE TYPE='P'AND number>=1 AND number<=6
DECLARE @SQL VARCHAR(MAX)
--5+0 5+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+'+POWER(@base,['+T4.Nos+']-1)'
+'+POWER(@base,['+T5.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
JOIN #Number T4 on T3.Nos<T4.Nos
JOIN #Number T5 on T4.Nos<T5.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+1' ELSE '5+0,4+1' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL
--4+0 4+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+'+POWER(@base,['+T4.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
JOIN #Number T4 on T3.Nos<T4.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+0,4+1' ELSE '4+0,3+1' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL
--3+1
SET @SQL=''
SELECT
@SQL=@SQL+' UNION ALL SELECT *,'
+'POWER(@base,['+T1.Nos+']-1)'
+'+POWER(@base,['+T2.Nos+']-1)'
+'+POWER(@base,['+T3.Nos+']-1)'
+' FROM #tb2'
FROM #Number T1
JOIN #Number T2 on T1.Nos<T2.Nos
JOIN #Number T3 on T2.Nos<T3.Nos
SET @SQL='DECLARE @base BIGINT;SET @base=2;'+STUFF(@SQL,1,11,'')
TRUNCATE TABLE #tb2Cte
INSERT INTO #tb2Cte
EXEC(@SQL)
INSERT INTO #result
SELECT T1.序号,T2.序号,'4+0,3+1'
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)=T2.value
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL
--Blu 1
INSERT INTO #result
SELECT T1.序号,T2.序号,'2+1,1+1,0+1'
FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu=T2.Blu
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL--SELECT * FROM #result T1
-- JOIN #tj T2 ON T1.jstatus=T2.jstatus
--ORDER BY 序号1/*SELECT T1.序号2
,SUM(T2.jamount)jamount
FROM #result T1
JOIN #tj T2 ON T1.jstatus=T2.jstatus
GROUP BY T1.序号2
--HAVING SUM(T2.jamount)<7567
求说明与讲解
IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb1Cte
FROM #tb1请详细指教一下, #tb1Cte的value是什么?
还要请教一个问题,如果集合A和集合B的记录超过50W的时候,跑了近3个小时还没有结果,能优化提速吗?谢谢
我优化了一下,如果TB2表不多的话,还是我之前那个
如果50W的话~~,试下这个
这个处理方式,近乎是两个表相乘的速度DECLARE @base bigint
SET @base = 2
IF OBJECT_ID('TempDB..#tb1Cte')IS NOT NULL DROP TABLE #tb1Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb1Cte
FROM #tb1
IF OBJECT_ID('TempDB..#tb2Cte')IS NOT NULL DROP TABLE #tb2Cte
SELECT *
,POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb2Cte
FROM #tb2
IF OBJECT_ID('TempDB..#result')IS NOT NULL DROP TABLE #result
CREATE TABLE #result([序号1] [bigint] NULL,[序号2] [bigint] NULL,[jstatus] varchar(100))
--6+0 6+1
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '6+1' ELSE '6+0' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value=T2.valueIF OBJECT_ID('TempDB..#Number')IS NOT NULL DROP TABLE #Number
SELECT POWER(@base,number) value
INTO #Number
FROM master..spt_values
WHERE TYPE='P'AND number<36
--5+0 5+1
--Start
IF OBJECT_ID('TempDB..#2bitDiff')IS NOT NULL DROP TABLE #2bitDiff
SELECT T1.value + T2.value value
INTO #2bitDiff
FROM #Number T1
JOIN #Number T2
ON T1.value < T2.value
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+1' ELSE '5+0,4+1' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value^T2.value)IN(SELECT value FROM #2bitDiff)
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL
--end
--4+0 4+1
--Start
IF OBJECT_ID('TempDB..#4bitDiff')IS NOT NULL DROP TABLE #4bitDiff
SELECT T1.value + T2.value + T3.value + T4.value value
INTO #4bitDiff
FROM #Number T1
JOIN #Number T2 ON T1.value < T2.value
JOIN #Number T3 ON T2.value < T3.value
JOIN #Number T4 ON T3.value < T4.value
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu=T2.Blu THEN '5+0,4+1' ELSE '4+0,3+1' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value^T2.value)IN(SELECT value FROM #4bitDiff)
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL
--end
--3+1
--Start
IF OBJECT_ID('TempDB..#3bitDiff')IS NOT NULL DROP TABLE #3bitDiff
SELECT T1.value + T2.value + T3.value value
INTO #3bitDiff
FROM #Number T1
JOIN #Number T2 ON T1.value < T2.value
JOIN #Number T3 ON T2.value < T3.value
INSERT INTO #result
SELECT T1.序号,T2.序号,'4+0,3+1'
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON (T1.value&T2.value)IN(SELECT value FROM #3bitDiff)
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL
--Blu 1
INSERT INTO #result
SELECT T1.序号,T2.序号,'2+1,1+1,0+1'
FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu=T2.Blu
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL--SELECT * FROM #result T1
-- JOIN #tj T2 ON T1.jstatus=T2.jstatus
--ORDER BY 序号1,序号2/*SELECT T1.序号2
,SUM(T2.jamount)jamount
FROM #result T1
JOIN #tj T2 ON T1.jstatus=T2.jstatus
GROUP BY T1.序号2
--HAVING SUM(T2.jamount)<7567
如果50W的话~~,试下这个
这个处理方式,近乎是两个表相乘的速度]谢谢ky_min 的指教,楼主在新发的贴里
http://bbs.csdn.net/topics/390948808
引用了2段代码,现在上传了数据表,含有字段名的txt文档,数据有945838行。
下载链接
http://www.access911.net/csdn/FileDescription.asp?id=3&mdb=2014-12-7&mode=1
请教问题如下:A代码是一个奖金的总和,B代码是大师ky_min 的优化代码
执行A和执行B的结果不一样,不知道是哪个有误,请指教,谢谢。