解决方案 »
- 求助!本地服务器的自动上传新的数据到远程服务器如何操作
- SQL 分析
- 为什么数据库备份文件几百兆,而还原出来的日志文件却有几十G
- 请教高手一个sql语句怎么写?
- group by 问题,奇怪的现象,小第累死N个脑细胞也没弄明白,请高手帮忙!!!
- 记录的统计排序问题??
- 好苦呀.編程.....樂在其中.請大家幫幫小弟吧.
- alter table ActiveSubscriptions with nocheck add CONSTRAINT [FK_ActiveSubscriptions_Subscriptions] foreign key ([SubscriptionId]
- 储存过程的问题
- 请高手谈谈sql server 的复制
- 关于递归sql求解
- sql2012没有足够的可用内存来运行此程序
下载链接为
http://www.access911.net/csdn/FileDescription.asp?id=3&mdb=2014-12-7&mode=1
请用语句建立[tball]if object_id('SSBallDB..tball') is not null drop table [dbo].[tball]
GOCREATE TABLE [dbo].[tball]([序号][int]IDENTITY(1,1),
[notext] [varchar](20) NULL,
[blu] [int] NULL
)
--直接用SQL语句将文本内容插入到表,注意:表的字段需要与文本中分割的字段相同
--插入到指定的表
BULK INSERT tball FROM 'D:\我的文档\tall.txt' --注意改变你的路径,此为楼主的文档路径
WITH
(
FIELDTERMINATOR =' ', --tab空格分割
ROWTERMINATOR = '\r\n', --换行符
FirstRow=2 --指定第2行开始 (tall.txt文本有字段名)
)
--select top 11 * from tball ---检查核对数据格式
--select count(*) from tball ---核对数据总行数,数据有945838行。
--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.Blu=T2.Blu AND (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
--end
谢谢大师ky_min 的回复,修改后的代码运行后数据正确,现在数据量大的在测试中。
集合A 导入了1417547行,集合B 导入了185956行,跑了3个多小时,结果还没有出来,不知可否优化提速,还是一句话“非常感谢您!”
再等待运行结果的时候,继续消化和学习,写了一下以下代码,但运行是错误的,特此前来请教,请大师帮忙修改,谢谢。----建立奖级表 #tj -- select * from #tj
if object_id('tempdb..#tj') is not null drop table #tj
create table #tj
(jname varchar(10), -- 奖级
jstatus varchar(100), -- 中奖条件
jamount int -- 奖金
)
insert into #tj
select '一等奖','5+2',10000000 union all
select '二等奖','5+1',307985 union all
select '三等奖','5+0,4+2',13062 union all
select '四等奖','4+1,3+2',200 union all
select '五等奖','4+0,3+1,2+2',10 union all
select '六等奖','3+0,1+2,2+1,0+2',5
----> 建立数据表,条件一:简称集合A
if object_id('tempdb..#tb1') is not null drop table #tb1-- select *from #tb1 order by 序号
CREATE TABLE #tb1([序号] [bigint] IDENTITY(1,1),[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [BLU1] [int], [BlU2] [int])
insert #tb1
select '03','15','21','27','29','04','05' union all
select '03','15','21','27','33','04','05' union all
select '03','15','21','27','35','04','05' union all
select '03','15','21','29','33','04','05' union all
select '03','15','21','29','35','04','05' union all
select '03','15','21','33','35','04','05' union all
select '03','15','22','27','29','04','05' union all
select '03','15','22','27','33','04','05'
----> 建立数据表,条件二:简称集合B
if object_id('tempdb..#tb2') is not null drop table #tb2 -- select *from #tb2 order by 序号
CREATE TABLE #tb2([序号] [bigint] IDENTITY(1,1) ,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [BLU1] [int], [BlU2] [int])
insert #tb2
select '03','05','21','27','29','01','07' union all
select '03','11','21','27','31','01','07' union all
select '03','12','21','27','35','01','07' union all
select '03','13','21','29','31','01','07' union all
select '03','14','22','29','35','01','07' union all
select '03','15','21','33','35','01','07' union all
select '03','16','23','27','29','01','07' union all
select '03','17','23','27','35','01','07'--->测试
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) 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) 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))--5+0 5+1 5+2
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu1=T2.Blu1 THEN '5+0,5+1,5+2' ELSE '5+0,5+1,5+2' 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
--4+0 4+1 4+2
--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.Blu1=T2.Blu1 THEN '4+0,4+1,4+2' ELSE '4+0,4+1,4+2' 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
--3+0 3+1 3+2
--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.Blu1=T2.Blu1 THEN '3+0,3+1,3+2' ELSE '3+0,3+1,3+2' 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
--2+2 2+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.序号,'2+2,2+1'
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.Blu1=T2.Blu1 AND (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
--end
--1+2 0+2
INSERT INTO #result
SELECT T1.序号,T2.序号,'1+2,0+2'
FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu1=T2.Blu1
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULLSELECT T1.序号2
,SUM(T2.jamount)jamount
FROM #result T1
JOIN #tj T2 ON T1.jstatus=T2.jstatus
GROUP BY T1.序号2问题:2个蓝球号码blu1 blu2不知道如何处理, 恳请指教,谢谢。
if object_id('tempdb..#tb2') is not null drop table #tb2 -- select *from #tb2 order by 序号
CREATE TABLE #tb2([序号] [bigint] IDENTITY(1,1) ,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [BLU1] [int], [BlU2] [int])
insert #tb2
select '03','05','21','27','29','01','07' union all
select '03','11','21','27','31','04','07' union all
select '03','12','21','27','35','04','07' union all
select '03','13','21','29','31','01','05' union all
select '03','14','22','29','35','01','07' union all
select '03','15','21','33','35','04','05' union all
select '03','16','23','27','29','01','07' union all
select '03','17','23','27','35','04','05'
您给的已经优化的代码正在运行,现在大数据量的正在测试中。说明
集合A 导入了1417547行,集合B 导入了185956行,跑了11个多小时,结果还没有出来,只好停下了,不知可否优化提速,还是一句话“非常感谢您的指导和帮助!”
集合A的数据在tall文档里,数据下载链接是
http://access911.net/csdn/filedescription.asp?mdb=2014-12-9&id=4
通过上述地址可以直接下载本文件,请复制上述 URL 链接地址集合B的数据在tEST文档里,数据下载链接是
http://access911.net/csdn/filedescription.asp?mdb=2014-12-9&id=5
通过上述地址可以直接下载本文件,请复制上述 URL 链接地址集合A建表#tball ,数据有1417547行。--->集合A建表#tball ,数据有1417547行。
if object_id('tempdb..#tball') is not null drop table [dbo].[#tball]
GOCREATE TABLE [dbo].[#tball]([序号][int]IDENTITY(1,1),
[notext] [varchar](20) NULL,
[blu] [int] NULL
)
--->插入到指定的表
BULK INSERT #tball FROM 'D:\我的文档\tall.txt' --注意改变你的路径,此为楼主的文档路径
WITH
(
FIELDTERMINATOR =',', --分割符号为逗号 ','
ROWTERMINATOR = '\r\n' --换行符
)
--select top 11 * from #tball ---检查核对数据格式
--select count(*) from #tball ---核对数据总行数,数据有1417547行。---->集合A 拆分为no1,no2,no3,no4,no5,no6,Blu共7列 -- select *from #tb2 order by 序号
if object_id('tempdb..#tb2') is not null drop table #tb2
go
CREATE TABLE #tb2([序号][bigint]IDENTITY(1,1),[NO1][int],[NO2][int],[NO3][int],[NO4][int],[NO5][int],[NO6][int],[Blu][int])
insert #tb2
select SUBSTRING(notext,1,2)as no1,
SUBSTRING(notext,4,2)as no2,
SUBSTRING(notext,7,2)as no3,
SUBSTRING(notext,10,2)as no4,
SUBSTRING(notext,13,2)as no5,
SUBSTRING(notext,16,2)as no6,blu
FROM [#tball]
集合B建表#test,数据有185956行。
--->集合B建表#test,数据有185956行。
if object_id('tempdb..#test') is not null drop table [dbo].[#test]
GOCREATE TABLE [dbo].[#test]([序号][int]IDENTITY(1,1),
[notext] [varchar](20) NULL,
[blu] [int] NULL
)
--->插入到指定的表
BULK INSERT #test FROM 'D:\我的文档\#test.txt' --注意改变你的路径,此为楼主的文档路径
WITH
(
FIELDTERMINATOR ='+', --分割符号为加号 '+'
ROWTERMINATOR = '\r\n' --换行符
)
--select top 11 * from #test ---检查核对数据格式
--select count(*) from #test ---核对数据总行数,数据有185956行。---->集合B 拆分为no1,no2,no3,no4,no5,no6,Blu共7列 -- select *from #tb2 order by 序号
if object_id('tempdb..#tb2') is not null drop table #tb2
go
CREATE TABLE #tb2([序号][bigint]IDENTITY(1,1),[NO1][int],[NO2][int],[NO3][int],[NO4][int],[NO5][int],[NO6][int],[Blu][int])
insert #tb2
select SUBSTRING(notext,1,2)as no1,
SUBSTRING(notext,4,2)as no2,
SUBSTRING(notext,7,2)as no3,
SUBSTRING(notext,10,2)as no4,
SUBSTRING(notext,13,2)as no5,
SUBSTRING(notext,16,2)as no6,blu
FROM [#test]
----建立奖级表 #tj -- select * from #tj
if object_id('tempdb..#tj') is not null drop table #tj
create table #tj
(jname varchar(10), -- 奖级
jstatus varchar(100), -- 中奖条件
jamount int -- 奖金
)
insert into #tj
select '一等奖','5+2',10000000 union all
select '二等奖','5+1',307985 union all
select '三等奖','5+0,4+2',13062 union all
select '四等奖','4+1,3+2',200 union all
select '五等奖','4+0,3+1,2+2',10 union all
select '六等奖','3+0,1+2,2+1,0+2',5
----> 建立数据表,条件一:简称集合A
if object_id('tempdb..#tb1') is not null drop table #tb1-- select *from #tb1 order by 序号
CREATE TABLE #tb1([序号] [bigint] IDENTITY(1,1),[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [BLU1] [int], [BlU2] [int])
insert #tb1
select '03','15','21','27','29','04','05' union all
select '03','15','21','27','33','04','05' union all
select '03','15','21','27','35','04','05' union all
select '03','15','21','29','33','04','05' union all
select '03','15','21','29','35','04','05' union all
select '03','15','21','33','35','04','05' union all
select '03','15','22','27','29','04','05' union all
select '03','15','22','27','33','04','05'
----> 建立数据表,条件二:简称集合B
if object_id('tempdb..#33') is not null drop table #33
if object_id('tempdb..#tb2') is not null drop table #tb2 -- select *from #tb2 order by 序号
CREATE TABLE #tb2([序号] [bigint] IDENTITY(1,1) ,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [BLU1] [int], [BlU2] [int])
insert #tb2
select '03','05','21','27','29','01','07' union all
select '03','11','21','27','31','04','07' union all
select '03','12','21','27','35','04','07' union all
select '03','13','21','29','31','04','05' union all
select '03','14','22','29','35','01','07' union all
select '03','15','21','33','35','04','05' union all
select '03','16','23','27','29','01','07' union all
select '03','17','23','27','35','04','05'--->测试
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) 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) 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))--5+0 5+1 5+2
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu1=T2.Blu1 THEN '5+0,5+1,5+2' ELSE '5+0,5+1,5+2' 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
--4+0 4+1 4+2
--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.Blu1=T2.Blu1 THEN '4+0,4+1,4+2' ELSE '4+0,4+1,4+2' 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
--3+0 3+1 3+2
--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.Blu1=T2.Blu1 THEN '3+0,3+1,3+2' ELSE '3+0,3+1,3+2' 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
--2+2 2+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.序号,'2+2,2+1'
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.Blu1=T2.Blu1 AND (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
--end
--1+2 0+2
INSERT INTO #result
SELECT T1.序号,T2.序号,'1+2,0+2'
FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu1=T2.Blu1
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULLSELECT T1.序号2
,SUM(T2.jamount)jamount
into #33
FROM #result T1
JOIN #tj T2 ON T1.jstatus=T2.jstatus
GROUP BY T1.序号2
if object_id('tempdb..#44') is not null drop table #44
go
select a.序号2,a.jamount,b.[序号],b.[NO1],b.[NO2],b.[NO3],b.[NO4],b.[NO5],b.[NO6],b.[Blu]
into #44
from #33 a left join #tb2 b on a.序号2=b.序号 order by jamount
if object_id('tempdb..#55') is not null drop table #55
go
select *,right('00'+cast(no1 as varchar),2)+' '+
right('00'+cast(no2 as varchar),2)+' '+
right('00'+cast(no3 as varchar),2)+' '+
right('00'+cast(no4 as varchar),2)+' '+
right('00'+cast(no5 as varchar),2)+' '+
right('00'+cast(no6 as varchar),2)+'+'+
right('00'+cast(blu as varchar),2)as notext into #55 from #44
select * from #55 --where jamount < 318025
这段代码纠正一下if object_id('tempdb..#44') is not null drop table #44
go
select a.序号2,a.jamount,b.[序号],b.[NO1],b.[NO2],b.[NO3],b.[NO4],b.[NO5],b.[Blu]
into #44
from #33 a left join #tb2 b on a.序号2=b.序号 order by jamount
if object_id('tempdb..#55') is not null drop table #55
go
select *,right('00'+cast(no1 as varchar),2)+' '+
right('00'+cast(no2 as varchar),2)+' '+
right('00'+cast(no3 as varchar),2)+' '+
right('00'+cast(no4 as varchar),2)+' '+
right('00'+cast(no5 as varchar),2)+'+'+
right('00'+cast(blu1 as varchar),2)+' '+
right('00'+cast(blu2 as varchar),2)as notext into #55 from #44
select * from #55 --where jamount < 318025
另外,那个错误应该是越界,具体也在研究~
请问一下这段代码可以先改改吗?我弄了整整一天还是没有结果,汗颜,急急急,本楼主是菜鸟水平啊
#3楼 完整的代码修改如下(大乐透5+2的奖金统计)
----建立奖级表 #tj -- select * from #tj
if object_id('tempdb..#tj') is not null drop table #tj
create table #tj
(jname varchar(10), -- 奖级
jstatus varchar(100), -- 中奖条件
jamount int -- 奖金
)
insert into #tj
select '一等奖','5+2',10000000 union all
select '二等奖','5+1',307985 union all
select '三等奖','5+0,4+2',13062 union all
select '四等奖','4+1,3+2',200 union all
select '五等奖','4+0,3+1,2+2',10 union all
select '六等奖','3+0,1+2,2+1,0+2',5
----> 建立数据表,条件一:简称集合A
if object_id('tempdb..#tb1') is not null drop table #tb1-- select *from #tb1 order by 序号
CREATE TABLE #tb1([序号] [bigint] IDENTITY(1,1),[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [BLU1] [int], [BlU2] [int])
insert #tb1
select '03','15','21','27','29','04','05' union all
select '03','15','21','27','33','04','05' union all
select '03','15','21','27','35','04','05' union all
select '03','15','21','29','33','04','05' union all
select '03','15','21','29','35','04','05' union all
select '03','15','21','33','35','04','05' union all
select '03','15','22','27','29','04','05' union all
select '03','15','22','27','33','04','05'
----> 建立数据表,条件二:简称集合B
if object_id('tempdb..#33') is not null drop table #33
if object_id('tempdb..#tb2') is not null drop table #tb2 -- select *from #tb2 order by 序号
CREATE TABLE #tb2([序号] [bigint] IDENTITY(1,1) ,[NO1] [int], [NO2] [int], [NO3] [int], [NO4] [int], [NO5] [int], [BLU1] [int], [BlU2] [int])
insert #tb2
select '03','05','21','27','29','01','07' union all
select '03','11','21','27','31','04','07' union all
select '03','12','21','27','35','04','07' union all
select '03','13','21','29','31','04','05' union all
select '03','14','22','29','35','01','07' union all
select '03','15','21','33','35','04','05' union all
select '03','16','23','27','29','01','07' union all
select '03','17','23','27','35','04','05'--->测试
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) 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) 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))--5+0 5+1 5+2
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.Blu1=T2.Blu1 THEN '5+0,5+1,5+2' ELSE '5+0,5+1,5+2' 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
--4+0 4+1 4+2
--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.Blu1=T2.Blu1 THEN '4+0,4+1,4+2' ELSE '4+0,4+1,4+2' 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
--3+0 3+1 3+2
--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.Blu1=T2.Blu1 THEN '3+0,3+1,3+2' ELSE '3+0,3+1,3+2' 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
--2+2 2+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.序号,'2+2,2+1'
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.Blu1=T2.Blu1 AND (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
--end
--1+2 0+2
INSERT INTO #result
SELECT T1.序号,T2.序号,'1+2,0+2'
FROM #tb1Cte T1 JOIN #tb2 T2 ON T1.Blu1=T2.Blu1
LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULLSELECT T1.序号2
,SUM(T2.jamount)jamount
into #33
FROM #result T1
JOIN #tj T2 ON T1.jstatus=T2.jstatus
GROUP BY T1.序号2
if object_id('tempdb..#44') is not null drop table #44
go
select a.序号2,a.jamount,b.[序号],b.[NO1],b.[NO2],b.[NO3],b.[NO4],b.[NO5],b.[Blu1] ,b.[Blu2]
into #44
from #33 a left join #tb2 b on a.序号2=b.序号 order by jamount
if object_id('tempdb..#55') is not null drop table #55
go
select *,right('00'+cast(no1 as varchar),2)+' '+
right('00'+cast(no2 as varchar),2)+' '+
right('00'+cast(no3 as varchar),2)+' '+
right('00'+cast(no4 as varchar),2)+' '+
right('00'+cast(no5 as varchar),2)+'+'+
right('00'+cast(blu1 as varchar),2)+' '+
right('00'+cast(blu2 as varchar),2)as notext into #55 from #44
select * from #55 --where jamount < 318025