急急急！结果是空的！！肯定我的代码有误。

RICHEER COCA 2014-12-10 05:04:48
引用ky_min的代码，首先感谢，学习中。
请问一下这段代码可以先改改吗？我弄了整整一天还是没有结果(结果是空的！！肯定我的代码有误。急急急！)，汗颜，急急急，本楼主是菜鸟水平啊
完整的代码如下(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.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

 

--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 NULL



SELECT 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

问题：2个蓝球号码blu1 blu2不知道如何处理， 恳请指教，谢谢。
...全文
154 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
RICHEER COCA 2014-12-12
  • 打赏
  • 举报
 回复
引用 6 楼 ky_min 的回复:
抱歉，让你弄了这么久，主要是我比较喜欢尝试，之前那个优化既然接手说要做了，就想调优下，也算是练习 然后，你自己调了段时间，我想你对这个模式的比对方法应该有所心得了，不过对大数据，我觉得可能还是不行，所以才想换其它思路，你急着出结果，你可以参照一下，以前那个模式的5+2 我不买彩票，也根本不信，到网上查了，还有追投柱的说法，那个调整一下，也可以达到效果
谢谢 ky_min 大师，您太谦虚了，看到您的回复，我也茅塞顿开，我会仔细揣摩学习，有问题的话还会前来讨教 另：还有追投注的说法，呵呵，我还不知道，我去看看，自己学着去调整，万一不会，定来讨教，多谢也恳请大师百忙之中继续6+1的优化，我会再开一个6+1的有待进一步优化的帖子，期盼佳音，这个贴结贴给分，多谢。
还在加载中灬 2014-12-12
  • 打赏
  • 举报
 回复
抱歉，让你弄了这么久，主要是我比较喜欢尝试，之前那个优化既然接手说要做了，就想调优下，也算是练习 然后，你自己调了段时间，我想你对这个模式的比对方法应该有所心得了，不过对大数据，我觉得可能还是不行，所以才想换其它思路，你急着出结果，你可以参照一下，以前那个模式的5+2 我不买彩票，也根本不信，到网上查了，还有追投柱的说法，那个调整一下，也可以达到效果
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

DECLARE @base bigint
SET @base = 2
  
----> 建立数据表，条件一：简称集合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'
--SELECT * FROM #tb1
  
----> 建立数据表，条件二：简称集合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','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'
--select '03','14','22','29','35','01','07'
--SELECT * FROM #tb2

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
    ,POWER(@base,BLU1-1) +
    POWER(@base,BLU2-1) AS value2
    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
    ,POWER(@base,BLU1-1) +
    POWER(@base,BLU2-1) AS value2
    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.value2=T2.value2 THEN '5+2' WHEN T1.value2&T2.value2>0 THEN '5+1' ELSE '5+0,4+2' 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
  
--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.value2=T2.value2 THEN '5+0,4+2' WHEN T1.value2&T2.value2>0 THEN '4+1,3+2' ELSE '4+0,3+1,2+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..#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.序号,CASE WHEN T1.value2=T2.value2 THEN '4+1,3+2' WHEN T1.value2&T2.value2>0 THEN '4+0,3+1,2+2' ELSE '3+0,1+2,2+1,0+2' END
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
--end
  
--2+2,2+1
--Start
INSERT INTO #result
SELECT T1.序号,T2.序号,CASE WHEN T1.value2=T2.value2 THEN '4+0,3+1,2+2' ELSE '3+0,1+2,2+1,0+2' END
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value2&T2.value2>0 AND (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
  
--1+2,0+2
INSERT INTO #result
SELECT T1.序号,T2.序号,'3+0,1+2,2+1,0+2'
FROM #tb1Cte T1 JOIN #tb2Cte T2 ON T1.value2=T2.value2
    LEFT JOIN #result T3 ON T1.序号=T3.序号1 AND T2.序号=T3.序号2
WHERE T3.序号1 IS NULL
 
--SELECT T1.序号1,T1.序号2,T1.jstatus,T3.* FROM #result T1
--    JOIN #tj T2 ON T1.jstatus=T2.jstatus
--    JOIN #tb1Cte T3 ON T1.序号1=T3.序号
--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
RICHEER COCA 2014-12-11
  • 打赏
  • 举报
 回复
引用 4 楼 ky_min 的回复:
我在考虑之前那个6+1的优化，如果那个可以提高一下，可以应用到这边
那个6+1的优化我准备单独再发一个帖 5+2的统计 急急急啊。俺弄了2整天，居然没有任何进展，求教大师了
还在加载中灬 2014-12-11
  • 打赏
  • 举报
 回复
我在考虑之前那个6+1的优化，如果那个可以提高一下，可以应用到这边
RICHEER COCA 2014-12-11
  • 打赏
  • 举报
 回复
依然修改中，学习纠错，自己顶一下，杰杰分
Neo_whl 2014-12-10
  • 打赏
  • 举报
 回复
你是体彩中心的么？路过。。。
惊！！！！！某大学生在教室里和女生........聊虾皮校招
大佬们，这两个地方哪个更好一点呀，一个是西安的7416厂，地方比较偏，一个是济南的齐鲁空天院，19年成立的好像是，两者钱差不多，请问有没有对这两个比较了解的大佬。如题，bg末9本科，电子信息工程，浙江人，技术...
ElasticSearch深度解析入门篇：高效搜索解决方案的
两个面试官，一个开摄像头，一个没有问项目，分布式和微服务理解，sql优化，查询接口慢怎么解决没有问最有成就感的事之类的问题有反问，什么时候有结果(一到两周内会通。求大家帮选offer，本人电子专业+复合背景，...
C语言面试题
1.理解main函数的参数,运行一下看看结果 #include int main(int argc, char* argv[], char *envp[]) { int i = 0; // walk through all the arguments // way 1: // for (i=0; i // { // printf("argv...
凉宫春日的阴谋7
序曲 凉宫春日很安分。 虽然看似忧郁，有时也唉声叹气，可事实上，她看起来并不寂寞。...总之要是发生了那样的事，结果我可能会变得很为难。因此，事到如今我也不想去矫正她。怎么说呢，有一种就像终
经典JavaScript正则表达式实战
匹配结尾的数字 来源：如何用 javascript 正则表达式 取出字符串最后一组数字，谢谢 ...字符串内字符键有空格，但是空格的数量可能不一致，通过正则将空格的个数统一变为一个。 例如：蓝 色 理 想 变成：蓝 色
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章