22,207
社区成员
发帖
与我相关
我的任务
分享
--->集合A建表#tball ,数据仅仅导入 969890 行。
if object_id('tempdb..#tball') is not null drop table [dbo].[#tball]
GO
CREATE 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 ---核对数据总行数,数据有 969890 行。
---->集合A 拆分为no1,no2,no3,no4,no5,no6,Blu共7列 -- select *from #tb1 order by 序号
if object_id('tempdb..#tb1') is not null drop table #tb1
go
CREATE TABLE #tb2([序号][bigint]IDENTITY(1,1),[NO1][int],[NO2][int],[NO3][int],[NO4][int],[NO5][int],[NO6][int],[Blu][int])
insert #tb1
select TOP 969890 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,数据有8024 行。
if object_id('tempdb..#test') is not null drop table [dbo].[#test]
GO
CREATE 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 ---核对数据总行数,数据有8024 行。
---->集合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 TOP 8024 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 '一等奖','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
---->测试
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.value
IF 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.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
--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)<457567