在想要的结果里，如何添加“第N组”

RICHEER COCA 2017-06-12 07:41:23 


----表一（数据库已经建立该表）

if object_id('tempdb..#DLT2017066') is not null drop table #DLT2017066

go 

create table #DLT2017066([id] int,[Notext] varchar(17),[NO1] varchar(2),[NO2] varchar(2),[NO3] varchar(2),[NO4] varchar(2),[NO5] varchar(2) )

insert #DLT2017066

select '224766','07 20 23 33 35','07','20','23','33','35' union all

select '224768','07 20 24 25 26','07','20','24','25','26' union all

select '224770','07 20 24 25 28','07','20','24','25','28' union all

select '224776','07 20 24 25 34','07','20','24','25','34' union all

select '224777','07 20 24 25 35','07','20','24','25','35' union all

select '224783','07 20 24 26 32','07','20','24','26','32' union all

select '224793','07 20 24 27 34','07','20','24','27','34' union all

select '224795','07 20 24 28 29','07','20','24','28','29' union all

select '224800','07 20 24 28 34','07','20','24','28','34' union all

select '224801','07 20 24 28 35','07','20','24','28','35' union all

select '224802','07 20 24 29 30','07','20','24','29','30'



----表二（数据库已经建立该表）

if object_id('tempdb.dbo.#BLU') is not null drop table #BLU 

go

create table #BLU([blu1] varchar(2),[blu2] varchar(2))

insert #BLU 

select '01','07' union all 

select '04','07' union all

select '06','07' union all

select '07','09' union all

select '07','11'



---->表三  是表一记录与表二的记录一一组合，执行代码生成  table #tbb1



if object_id('tempdb.dbo.#tbb1') is not null drop table #tbb1 

go

select 

rtrim(a.id)AS ID,

rtrim(a.Notext)AS NOTEXT,

rtrim(a.no1)AS NO1,

rtrim(a.no2)AS NO2,

rtrim(a.no3)AS NO3,

rtrim(a.NO4)AS NO4,

rtrim(a.no5)AS NO5,

ltrim(b.blu1)AS BLU1,

ltrim(b.blu2)AS BLU2  into #tbb1 

from  #DLT2017066 a,#BLU b  --检查 select *from  #DLT2017066  检查  select *from  #BLU



---->表四   设定条件值查询结果， 执行代码生成  table #tbb2



if object_id('tempdb.dbo.#tbb2') is not null drop table #tbb2  

                                                                                

Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT 

into #tbb2

From #tbb1

WHERE Notext like '%24%'and  Notext like '%25%' or  Notext like '%26%'



----->表五   统计出#tbb2的数量值   执行代码生成  table #tbb3

if object_id('tempdb.dbo.#tbb3') is not null drop table #tbb3

SELECT COUNT(*) AS num 

into #tbb3

FROM #tbb2



----->表六  添加记录值的单位 执行代码生成  table #tbb4

if object_id('tempdb.dbo.#tbb4') is not null drop table #tbb4

select (cast('' as varchar(50))+'大乐透 2017066期单式投注')as [name],

(cast(num as varchar(10))+'注')as num,(cast(num*2 as varchar(10))+'元') as Rvalue 

into #tbb4

from #tbb3

 



 ----->表七  5个记录为一组 执行代码生成  table #tbb5

if object_id('tempdb.dbo.#tbb5') is not null drop table #tbb5 

;WITH CTE AS(

    SELECT  ROW_NUMBER()OVER(ORDER BY GETDATE()) -1 RN

        ,*

    FROM  #tbb2  

)

,Nrs

AS

(

SELECT TOP ((SELECT COUNT(1)+COUNT(1)/5 FROM Cte)) ROW_NUMBER()OVER(ORDER BY GETDATE())-1 AS Nr FROM sys.columns AS a,sys.columns AS b,sys.columns AS c,sys.columns AS d

)

SELECT ISNULL(LTRIM(T2.[NOTEXT]),'')AS [DLTT] into  #tbb5



FROM Nrs T1

    LEFT JOIN CTE T2 ON T1.Nr%6=T2.RN%5 AND T1.Nr/6=T2.RN/5

ORDER BY T1.Nr

 

----->需要的结果是5行记录分为一组，再编号“第N组”

DECLARE @sql nvarchar(max)

SELECT @sql=N'SELECT t.[DLTT] as '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+N' FROM #tbb5 AS t ' FROM #tbb4 AS T4

EXEC (@sql)


----->想要的结果是5行记录分为一组，再编号“第N组”


代买2017066期大乐透25注50元

第1组

07 20 24 25 26+01 07

07 20 24 25 28+01 07

07 20 24 25 34+01 07

07 20 24 25 35+01 07

07 20 24 26 32+01 07

第2组

07 20 24 25 26+04 07

07 20 24 25 28+04 07

07 20 24 25 34+04 07

07 20 24 25 35+04 07

07 20 24 26 32+04 07

第3组

07 20 24 25 26+06 07

07 20 24 25 28+06 07

07 20 24 25 34+06 07

07 20 24 25 35+06 07

07 20 24 26 32+06 07

第4组

07 20 24 25 26+07 09

07 20 24 25 28+07 09

07 20 24 25 34+07 09

07 20 24 25 35+07 09

07 20 24 26 32+07 09

第5组

07 20 24 25 26+07 11

07 20 24 25 28+07 11

07 20 24 25 34+07 11

07 20 24 25 35+07 11

07 20 24 26 32+07 11
...全文
184 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
道素 2017-06-30
  • 打赏
  • 举报
 回复
我没有全看完上面的帖子，你说的是将组自定编号吗？ 那利用你现在语句就可以了，可能你们已经想到了，我先贴出来： 就是把原来最后动态语句中的 ISNULL(LTRIM(T2.notext),LTRIM(T3.BHao)) 改为了 ISNULL(LTRIM(T2.notext),LTRIM(T3.BHao)+ LTRIM(t1.number/6+1)+N''#'') 不知道是不是你想要的 

----->结果5行记录分组，但在第三句代码里无法添加自动编号。
DECLARE @sql nvarchar(max)
SELECT  @sql=N'
SELECT ISNULL(LTRIM(T2.notext),LTRIM(T3.BHao)+ LTRIM(t1.number/6+1)+N''#'') AS '+QUOTENAME(T3.name+t3.num+t3.Rvalue)+CHAR(13)
 +N'FROM (select row_number()over(order by getdate()) as number from master..spt_values n1, master..spt_values n2 where n1.type=''P'' AND n2.type=n1.type and n1.number between 1 and 10000 and n2.number between 1 and 10000) as T1
   INNER JOIN #tbb4 AS T3 ON 1=1
      LEFT JOIN #tbb3 T2 ON T1.number=T2.RN1+2
WHERE T1.number<=t3.MaxNumber+2
'
FROM #tbb4 AS T3     ----->INNER JOIN表 #tbb4  添加记录值的单位       --> LEFT JOIN表 #tbb3   条件值进行缩水的结果
PRINT @sql
EXEC(@sql)


大乐透17067期单式投注50注100元
编号1#
07 20 24 25 26+01 07
07 20 24 25 28+01 07
07 20 24 25 34+01 07
07 20 24 25 35+01 07
07 20 24 26 32+01 07
编号2#
07 20 24 27 34+01 07
07 20 24 28 29+01 07
07 20 24 28 34+01 07
07 20 24 28 35+01 07
07 20 24 29 30+01 07
编号3#
07 20 24 25 26+04 07
07 20 24 25 28+04 07
07 20 24 25 34+04 07
07 20 24 25 35+04 07
07 20 24 26 32+04 07
编号4#
07 20 24 27 34+04 07
07 20 24 28 29+04 07
07 20 24 28 34+04 07
07 20 24 28 35+04 07
07 20 24 29 30+04 07
编号5#
07 20 24 25 26+06 07
07 20 24 25 28+06 07
07 20 24 25 34+06 07
07 20 24 25 35+06 07
07 20 24 26 32+06 07
编号6#
07 20 24 27 34+06 07
07 20 24 28 29+06 07
07 20 24 28 34+06 07
07 20 24 28 35+06 07
07 20 24 29 30+06 07
编号7#
07 20 24 25 26+07 09
07 20 24 25 28+07 09
07 20 24 25 34+07 09
07 20 24 25 35+07 09
07 20 24 26 32+07 09
编号8#
07 20 24 27 34+07 09
07 20 24 28 29+07 09
07 20 24 28 34+07 09
07 20 24 28 35+07 09
07 20 24 29 30+07 09
编号9#
07 20 24 25 26+07 11
07 20 24 25 28+07 11
07 20 24 25 34+07 11
07 20 24 25 35+07 11
07 20 24 26 32+07 11
编号10#
07 20 24 27 34+07 11
07 20 24 28 29+07 11
07 20 24 28 34+07 11
07 20 24 28 35+07 11
07 20 24 29 30+07 11
RICHEER COCA 2017-06-13
  • 打赏
  • 举报
 回复
引用 12 楼 sinat_28984567 的回复:
非常感谢 12 楼 sinat_28984567 的回复，我必须结帖给100分了，学习了，尝试修改代码 
 from(SELECT TOP ((SELECT MAX(nr)+1 FROM #tbb6)) t.[DLTT] as
殊途同归 感谢这样的学习机会
引用 7 楼 u011709039 的回复:
[quote=引用 5 楼 sinat_28984567 的回复:]
能否在动态语句里 添加“自动编号”，借用了ch21st 道素大师的代码（借此感谢ch21st 道素大师），现在修改后如下： 

----表一（数据库已经建立该表）
if object_id('tempdb..#DLT2017066') is not null drop table #DLT2017066
go 
create table #DLT2017066([id] int,[Notext] varchar(17),[NO1] varchar(2),[NO2] varchar(2),[NO3] varchar(2),[NO4] varchar(2),[NO5] varchar(2) )
insert #DLT2017066
select '224766','07 20 23 33 35','07','20','23','33','35' union all
select '224768','07 20 24 25 26','07','20','24','25','26' union all
select '224770','07 20 24 25 28','07','20','24','25','28' union all
select '224776','07 20 24 25 34','07','20','24','25','34' union all
select '224777','07 20 24 25 35','07','20','24','25','35' union all
select '224783','07 20 24 26 32','07','20','24','26','32' union all
select '224793','07 20 24 27 34','07','20','24','27','34' union all
select '224795','07 20 24 28 29','07','20','24','28','29' union all
select '224800','07 20 24 28 34','07','20','24','28','34' union all
select '224801','07 20 24 28 35','07','20','24','28','35' union all
select '224802','07 20 24 29 30','07','20','24','29','30'

----表二（数据库已经建立该表）
if object_id('tempdb.dbo.#BLU') is not null drop table #BLU 
go
create table #BLU([blu1] varchar(2),[blu2] varchar(2))
insert #BLU 
select '01','07' union all 
select '04','07' union all
select '06','07' union all
select '07','09' union all
select '07','11'

---->表 #tbb1  是当期预测前区红球与后区蓝球的记录一一组合，执行代码生成 #tbb1 
if object_id('tempdb.dbo.#tbb1') is not null drop table #tbb1   --检查 select *from  #tbb1
go
select 
rtrim(a.id)AS ID,rtrim(a.Notext)AS NOTEXT,rtrim(right('0'+cast([no1] as varchar(10)),2)) AS NO1,rtrim(right('0'+cast([no2] as varchar(10)),2)) AS NO2,rtrim(right('0'+cast([no3] as varchar(10)),2)) AS NO3,rtrim(right('0'+cast([no4] as varchar(10)),2)) AS NO4,rtrim(right('0'+cast([no5] as varchar(10)),2)) AS NO5,ltrim(right('0'+cast([blu1] as varchar(10)),2))AS BLU1,ltrim(right('0'+cast([blu2] as varchar(10)),2)) AS BLU2  into #tbb1 
from  #DLT2017066 a,#BLU b  --检查 select *from  #DLT2017066  检查  select *from  #BLU

---->表 #tbb3   设定条件值进行缩水， 执行代码生成  #tbb3    ************核心表！！！ 
if object_id('tempdb.dbo.#tbb3') is not null drop table #tbb3  --检查 select *from  #tbb3
                                                                                 
Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT ,ROW_NUMBER()OVER(ORDER BY GETDATE())RN,ROW_NUMBER()OVER(ORDER BY GETDATE())+(ROW_NUMBER()OVER(ORDER BY GETDATE())-1)/5-1 AS RN1
into #tbb3
From #tbb1
WHERE Notext like '%24%' OR  Notext like '%25%' OR  Notext like '%26%'

----->表 #tbb4  添加记录值的单位 执行代码生成 #tbb4   --检查 select *from  #tbb4
if object_id('tempdb.dbo.#tbb4') is not null drop table #tbb4
select COUNT(*) AS qty, MAX(RN+(RN-1)/5-1) AS MaxNumber,
N'大乐透17067期单式投注' AS [name],LTRIM(COUNT(*)) +N'注' as num,LTRIM(2*COUNT(*))+N'元' as Rvalue,N'编号' AS [BHao] 
into #tbb4 ----->表 #tbb4  添加记录值的单位 执行代码生成 #tbb4 
from #tbb3 ---->表 #tbb3   设定条件值进行缩水的结果

----->结果5行记录分组，但在第三句代码里无法添加自动编号。
DECLARE @sql nvarchar(max)
SELECT  @sql='
SELECT ISNULL(LTRIM(T2.notext),LTRIM(T3.BHao)) AS '+QUOTENAME(T3.name+t3.num+t3.Rvalue)+CHAR(13)
 +N'FROM (select row_number()over(order by getdate()) as number from master..spt_values n1, master..spt_values n2 where n1.type=''P'' AND n2.type=n1.type and n1.number between 1 and 10000 and n2.number between 1 and 10000) as T1
   INNER JOIN #tbb4 AS T3 ON 1=1
      LEFT JOIN #tbb3 T2 ON T1.number=T2.RN1+2
WHERE T1.number<=t3.MaxNumber+2
'
FROM #tbb4 AS T3     ----->INNER JOIN表 #tbb4  添加记录值的单位       --> LEFT JOIN表 #tbb3   条件值进行缩水的结果
PRINT @sql
EXEC(@sql)
楼主自己修改的，菜鸟级的楼主无法在编号里完成自动填充编号！ 同时请教：动态语句里能够完成自动编号吗？结贴后想继续学习一下，谢谢继续指教！ [/quote]
二月十六 2017-06-13
  • 打赏
  • 举报
 回复
如果这种情况试试这个可以不，这样就是不是正好够一组的可以显示出来，钩一族的会多一个“第几组”
----表一（数据库已经建立该表）

if object_id('tempdb..#DLT2017066') is not null drop table #DLT2017066

go 

create table #DLT2017066([id] int,[Notext] varchar(17),[NO1] varchar(2),[NO2] varchar(2),[NO3] varchar(2),[NO4] varchar(2),[NO5] varchar(2) )

insert #DLT2017066

select '224766','07 20 23 33 35','07','20','23','33','35' union all

select '224768','07 20 24 25 26','07','20','24','25','26' union all

select '224770','07 20 24 25 28','07','20','24','25','28' union all

select '224776','07 20 24 25 34','07','20','24','25','34' union all

select '224777','07 20 24 25 35','07','20','24','25','35' union all

select '224783','07 20 24 26 32','07','20','24','26','32' union all

select '224793','07 20 24 27 34','07','20','24','27','34' union all

select '224795','07 20 24 28 29','07','20','24','28','29' union all

select '224800','07 20 24 28 34','07','20','24','28','34' union all

select '224801','07 20 24 28 35','07','20','24','28','35' union all

select '224802','07 20 24 29 30','07','20','24','29','30'

 

----表二（数据库已经建立该表）

if object_id('tempdb.dbo.#BLU') is not null drop table #BLU 

go

create table #BLU([blu1] varchar(2),[blu2] varchar(2))

insert #BLU 

select '01','07' union all 

select '04','07' union all

select '06','07' union all

select '07','09' union all

select '07','11'

 

---->表三  是表一记录与表二的记录一一组合，执行代码生成  table #tbb1

 

if object_id('tempdb.dbo.#tbb1') is not null drop table #tbb1 

go

select 

rtrim(a.id)AS ID,

rtrim(a.Notext)AS NOTEXT,

rtrim(a.no1)AS NO1,

rtrim(a.no2)AS NO2,

rtrim(a.no3)AS NO3,

rtrim(a.NO4)AS NO4,

rtrim(a.no5)AS NO5,

ltrim(b.blu1)AS BLU1,

ltrim(b.blu2)AS BLU2  into #tbb1 

from  #DLT2017066 a,#BLU b  --检查 select *from  #DLT2017066  检查  select *from  #BLU

 

---->表四   设定条件值查询结果， 执行代码生成  table #tbb2

 

if object_id('tempdb.dbo.#tbb2') is not null drop table #tbb2  

                                                                                 

Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT 

into #tbb2

From #tbb1

WHERE Notext like '%24%' OR  Notext like '%25%' OR  Notext like '%26%'   ----修改为“OR”

 

----->表五   统计出#tbb2的数量值   执行代码生成  table #tbb3

if object_id('tempdb.dbo.#tbb3') is not null drop table #tbb3

SELECT COUNT(*) AS num 

into #tbb3

FROM #tbb2

 

----->表六  添加记录值的单位 执行代码生成  table #tbb4

if object_id('tempdb.dbo.#tbb4') is not null drop table #tbb4

select (cast('' as varchar(50))+'大乐透 2017066期单式投注')as [name],

(cast(num as varchar(10))+'注')as num,(cast(num*2 as varchar(10))+'元') as Rvalue 

into #tbb4

from #tbb3

  

 ----->表七  5个记录为一组 执行代码生成  table #tbb5

if object_id('tempdb.dbo.#tbb5') is not null drop table #tbb5 

;WITH CTE AS(

    SELECT  ROW_NUMBER()OVER(ORDER BY GETDATE()) -1 RN

        ,*

    FROM  #tbb2  

)

,Nrs

AS

(

SELECT TOP ((SELECT COUNT(1)+COUNT(1)/5 FROM Cte)) ROW_NUMBER()OVER(ORDER BY GETDATE())-1 AS Nr FROM sys.columns AS a,sys.columns AS b,sys.columns AS c,sys.columns AS d

)

SELECT ISNULL(LTRIM(T2.[NOTEXT]),'第'+RTRIM((T1.Nr+7)/6)+'组')AS [DLTT],T1.Nr into  #tbb5

 

FROM Nrs T1

    LEFT JOIN CTE T2 ON T1.Nr%6=T2.RN%5 AND T1.Nr/6=T2.RN/5

ORDER BY T1.Nr

  

----->需要的结果是5行记录分为一组，再编号“第N组”

DECLARE @sql nvarchar(max)

SELECT @sql=N'select '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+' from(SELECT t.[DLTT] as '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+N' ,Nr FROM #tbb5 AS t union  SELECT ''第1组'',-1 )t order by t.Nr' FROM #tbb4 AS T4

EXEC(@sql)
RICHEER COCA 2017-06-13
  • 打赏
  • 举报
 回复
RICHEER COCA 2017-06-13
  • 打赏
  • 举报
 回复
引用 9 楼 sinat_28984567 的回复:
执行的是6#的代码

非常感谢 9 楼 sinat_28984567 的回复
执行的是6#的代码


二月十六 2017-06-13
  • 打赏
  • 举报
 回复
执行的是6#的代码
二月十六 2017-06-13
  • 打赏
  • 举报
 回复
把你的结果截图发上来看看，我执行是有第六组的，也就一个第一组
大乐透 2017066期单式投注50注100元

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

第1组

07 20 24 25 26+01 07

07 20 24 25 28+01 07

07 20 24 25 34+01 07

07 20 24 25 35+01 07

07 20 24 26 32+01 07

第2组

07 20 24 27 34+01 07

07 20 24 28 29+01 07

07 20 24 28 34+01 07

07 20 24 28 35+01 07

07 20 24 29 30+01 07

第3组

07 20 24 25 26+04 07

07 20 24 25 28+04 07

07 20 24 25 34+04 07

07 20 24 25 35+04 07

07 20 24 26 32+04 07

第4组

07 20 24 27 34+04 07

07 20 24 28 29+04 07

07 20 24 28 34+04 07

07 20 24 28 35+04 07

07 20 24 29 30+04 07

第5组

07 20 24 25 26+06 07

07 20 24 25 28+06 07

07 20 24 25 34+06 07

07 20 24 25 35+06 07

07 20 24 26 32+06 07

第6组

07 20 24 27 34+06 07

07 20 24 28 29+06 07

07 20 24 28 34+06 07

07 20 24 28 35+06 07

07 20 24 29 30+06 07

第7组

07 20 24 25 26+07 09

07 20 24 25 28+07 09

07 20 24 25 34+07 09

07 20 24 25 35+07 09

07 20 24 26 32+07 09

第8组

07 20 24 27 34+07 09

07 20 24 28 29+07 09

07 20 24 28 34+07 09

07 20 24 28 35+07 09

07 20 24 29 30+07 09

第9组

07 20 24 25 26+07 11

07 20 24 25 28+07 11

07 20 24 25 34+07 11

07 20 24 25 35+07 11

07 20 24 26 32+07 11

第10组

07 20 24 27 34+07 11

07 20 24 28 29+07 11

07 20 24 28 34+07 11

07 20 24 28 35+07 11

07 20 24 29 30+07 11


RICHEER COCA 2017-06-13
  • 打赏
  • 举报
 回复
引用 5 楼 sinat_28984567 的回复:
非常感谢 5 楼 sinat_28984567 的回复，执行结果：少了“第6组”，但多了一个编号“第1组” 能否在动态语句里 添加“自动编号”，借用了ch21st 道素大师的代码（借此感谢ch21st 道素大师），现在修改后如下： 

----表一（数据库已经建立该表）
if object_id('tempdb..#DLT2017066') is not null drop table #DLT2017066
go 
create table #DLT2017066([id] int,[Notext] varchar(17),[NO1] varchar(2),[NO2] varchar(2),[NO3] varchar(2),[NO4] varchar(2),[NO5] varchar(2) )
insert #DLT2017066
select '224766','07 20 23 33 35','07','20','23','33','35' union all
select '224768','07 20 24 25 26','07','20','24','25','26' union all
select '224770','07 20 24 25 28','07','20','24','25','28' union all
select '224776','07 20 24 25 34','07','20','24','25','34' union all
select '224777','07 20 24 25 35','07','20','24','25','35' union all
select '224783','07 20 24 26 32','07','20','24','26','32' union all
select '224793','07 20 24 27 34','07','20','24','27','34' union all
select '224795','07 20 24 28 29','07','20','24','28','29' union all
select '224800','07 20 24 28 34','07','20','24','28','34' union all
select '224801','07 20 24 28 35','07','20','24','28','35' union all
select '224802','07 20 24 29 30','07','20','24','29','30'

----表二（数据库已经建立该表）
if object_id('tempdb.dbo.#BLU') is not null drop table #BLU 
go
create table #BLU([blu1] varchar(2),[blu2] varchar(2))
insert #BLU 
select '01','07' union all 
select '04','07' union all
select '06','07' union all
select '07','09' union all
select '07','11'

---->表 #tbb1  是当期预测前区红球与后区蓝球的记录一一组合，执行代码生成 #tbb1 
if object_id('tempdb.dbo.#tbb1') is not null drop table #tbb1   --检查 select *from  #tbb1
go
select 
rtrim(a.id)AS ID,rtrim(a.Notext)AS NOTEXT,rtrim(right('0'+cast([no1] as varchar(10)),2)) AS NO1,rtrim(right('0'+cast([no2] as varchar(10)),2)) AS NO2,rtrim(right('0'+cast([no3] as varchar(10)),2)) AS NO3,rtrim(right('0'+cast([no4] as varchar(10)),2)) AS NO4,rtrim(right('0'+cast([no5] as varchar(10)),2)) AS NO5,ltrim(right('0'+cast([blu1] as varchar(10)),2))AS BLU1,ltrim(right('0'+cast([blu2] as varchar(10)),2)) AS BLU2  into #tbb1 
from  #DLT2017066 a,#BLU b  --检查 select *from  #DLT2017066  检查  select *from  #BLU

---->表 #tbb3   设定条件值进行缩水， 执行代码生成  #tbb3    ************核心表！！！ 
if object_id('tempdb.dbo.#tbb3') is not null drop table #tbb3  --检查 select *from  #tbb3
                                                                                 
Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT ,ROW_NUMBER()OVER(ORDER BY GETDATE())RN,ROW_NUMBER()OVER(ORDER BY GETDATE())+(ROW_NUMBER()OVER(ORDER BY GETDATE())-1)/5-1 AS RN1
into #tbb3
From #tbb1
WHERE Notext like '%24%' OR  Notext like '%25%' OR  Notext like '%26%'

----->表 #tbb4  添加记录值的单位 执行代码生成 #tbb4   --检查 select *from  #tbb4
if object_id('tempdb.dbo.#tbb4') is not null drop table #tbb4
select COUNT(*) AS qty, MAX(RN+(RN-1)/5-1) AS MaxNumber,
N'大乐透17067期单式投注' AS [name],LTRIM(COUNT(*)) +N'注' as num,LTRIM(2*COUNT(*))+N'元' as Rvalue,N'编号' AS [BHao] 
into #tbb4 ----->表 #tbb4  添加记录值的单位 执行代码生成 #tbb4 
from #tbb3 ---->表 #tbb3   设定条件值进行缩水的结果

----->结果5行记录分组，但在第三句代码里无法添加自动编号。
DECLARE @sql nvarchar(max)
SELECT  @sql='
SELECT ISNULL(LTRIM(T2.notext),LTRIM(T3.BHao)) AS '+QUOTENAME(T3.name+t3.num+t3.Rvalue)+CHAR(13)
 +N'FROM (select row_number()over(order by getdate()) as number from master..spt_values n1, master..spt_values n2 where n1.type=''P'' AND n2.type=n1.type and n1.number between 1 and 10000 and n2.number between 1 and 10000) as T1
   INNER JOIN #tbb4 AS T3 ON 1=1
      LEFT JOIN #tbb3 T2 ON T1.number=T2.RN1+2
WHERE T1.number<=t3.MaxNumber+2
'
FROM #tbb4 AS T3     ----->INNER JOIN表 #tbb4  添加记录值的单位       --> LEFT JOIN表 #tbb3   条件值进行缩水的结果
PRINT @sql
EXEC(@sql)
但执行结果如下，无法在编号里自动填充编号！ 

大乐透17067期单式投注50注100元
编号
07 20 24 25 26+01 07
07 20 24 25 28+01 07
07 20 24 25 34+01 07
07 20 24 25 35+01 07
07 20 24 26 32+01 07
编号
07 20 24 27 34+01 07
07 20 24 28 29+01 07
07 20 24 28 34+01 07
07 20 24 28 35+01 07
07 20 24 29 30+01 07
编号
07 20 24 25 26+04 07
07 20 24 25 28+04 07
07 20 24 25 34+04 07
07 20 24 25 35+04 07
07 20 24 26 32+04 07
编号
07 20 24 27 34+04 07
07 20 24 28 29+04 07
07 20 24 28 34+04 07
07 20 24 28 35+04 07
07 20 24 29 30+04 07
编号
07 20 24 25 26+06 07
07 20 24 25 28+06 07
07 20 24 25 34+06 07
07 20 24 25 35+06 07
07 20 24 26 32+06 07
编号
07 20 24 27 34+06 07
07 20 24 28 29+06 07
07 20 24 28 34+06 07
07 20 24 28 35+06 07
07 20 24 29 30+06 07
编号
07 20 24 25 26+07 09
07 20 24 25 28+07 09
07 20 24 25 34+07 09
07 20 24 25 35+07 09
07 20 24 26 32+07 09
编号
07 20 24 27 34+07 09
07 20 24 28 29+07 09
07 20 24 28 34+07 09
07 20 24 28 35+07 09
07 20 24 29 30+07 09
编号
07 20 24 25 26+07 11
07 20 24 25 28+07 11
07 20 24 25 34+07 11
07 20 24 25 35+07 11
07 20 24 26 32+07 11
编号
07 20 24 27 34+07 11
07 20 24 28 29+07 11
07 20 24 28 34+07 11
07 20 24 28 35+07 11
07 20 24 29 30+07 11
想要的结果是这样的 

大乐透17067期单式投注50注100元
编号 1
07 20 24 25 26+01 07
07 20 24 25 28+01 07
07 20 24 25 34+01 07
07 20 24 25 35+01 07
07 20 24 26 32+01 07
编号 2
07 20 24 27 34+01 07
07 20 24 28 29+01 07
07 20 24 28 34+01 07
07 20 24 28 35+01 07
07 20 24 29 30+01 07
编号 3
07 20 24 25 26+04 07
07 20 24 25 28+04 07
07 20 24 25 34+04 07
07 20 24 25 35+04 07
07 20 24 26 32+04 07
编号 4
07 20 24 27 34+04 07
07 20 24 28 29+04 07
07 20 24 28 34+04 07
07 20 24 28 35+04 07
07 20 24 29 30+04 07
编号 5
07 20 24 25 26+06 07
07 20 24 25 28+06 07
07 20 24 25 34+06 07
07 20 24 25 35+06 07
07 20 24 26 32+06 07
编号 6
07 20 24 27 34+06 07
07 20 24 28 29+06 07
07 20 24 28 34+06 07
07 20 24 28 35+06 07
07 20 24 29 30+06 07
编号 7
07 20 24 25 26+07 09
07 20 24 25 28+07 09
07 20 24 25 34+07 09
07 20 24 25 35+07 09
07 20 24 26 32+07 09
编号 8
07 20 24 27 34+07 09
07 20 24 28 29+07 09
07 20 24 28 34+07 09
07 20 24 28 35+07 09
07 20 24 29 30+07 09
编号 9
07 20 24 25 26+07 11
07 20 24 25 28+07 11
07 20 24 25 34+07 11
07 20 24 25 35+07 11
07 20 24 26 32+07 11
编号 10
07 20 24 27 34+07 11
07 20 24 28 29+07 11
07 20 24 28 34+07 11
07 20 24 28 35+07 11
07 20 24 29 30+07 11
同时请教：动态语句里能够完成自动编号吗？万一不行的话，就换一种方式吧，谢谢！
二月十六 2017-06-13
  • 打赏
  • 举报
 回复 
----表一（数据库已经建立该表）
if object_id('tempdb..#DLT2017066') is not null drop table #DLT2017066
go 
create table #DLT2017066([id] int,[Notext] varchar(17),[NO1] varchar(2),[NO2] varchar(2),[NO3] varchar(2),[NO4] varchar(2),[NO5] varchar(2) )
insert #DLT2017066
select '224766','07 20 23 33 35','07','20','23','33','35' union all
select '224768','07 20 24 25 26','07','20','24','25','26' union all
select '224770','07 20 24 25 28','07','20','24','25','28' union all
select '224776','07 20 24 25 34','07','20','24','25','34' union all
select '224777','07 20 24 25 35','07','20','24','25','35' union all
select '224783','07 20 24 26 32','07','20','24','26','32' union all
select '224793','07 20 24 27 34','07','20','24','27','34' union all
select '224795','07 20 24 28 29','07','20','24','28','29' union all
select '224800','07 20 24 28 34','07','20','24','28','34' union all
select '224801','07 20 24 28 35','07','20','24','28','35' union all
select '224802','07 20 24 29 30','07','20','24','29','30'
 
----表二（数据库已经建立该表）
if object_id('tempdb.dbo.#BLU') is not null drop table #BLU 
go
create table #BLU([blu1] varchar(2),[blu2] varchar(2))
insert #BLU 
select '01','07' union all 
select '04','07' union all
select '06','07' union all
select '07','09' union all
select '07','11'
 
---->表三  是表一记录与表二的记录一一组合，执行代码生成  table #tbb1
 
if object_id('tempdb.dbo.#tbb1') is not null drop table #tbb1 
go
select 
rtrim(a.id)AS ID,
rtrim(a.Notext)AS NOTEXT,
rtrim(a.no1)AS NO1,
rtrim(a.no2)AS NO2,
rtrim(a.no3)AS NO3,
rtrim(a.NO4)AS NO4,
rtrim(a.no5)AS NO5,
ltrim(b.blu1)AS BLU1,
ltrim(b.blu2)AS BLU2  into #tbb1 
from  #DLT2017066 a,#BLU b  --检查 select *from  #DLT2017066  检查  select *from  #BLU
 
---->表四   设定条件值查询结果， 执行代码生成  table #tbb2
 
if object_id('tempdb.dbo.#tbb2') is not null drop table #tbb2  
                                                                                 
Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT 
into #tbb2
From #tbb1
WHERE Notext like '%24%' OR  Notext like '%25%' OR  Notext like '%26%'   ----修改为“OR”
 
----->表五   统计出#tbb2的数量值   执行代码生成  table #tbb3
if object_id('tempdb.dbo.#tbb3') is not null drop table #tbb3
SELECT COUNT(*) AS num 
into #tbb3
FROM #tbb2
 
----->表六  添加记录值的单位 执行代码生成  table #tbb4
if object_id('tempdb.dbo.#tbb4') is not null drop table #tbb4
select (cast('' as varchar(50))+'大乐透 2017066期单式投注')as [name],
(cast(num as varchar(10))+'注')as num,(cast(num*2 as varchar(10))+'元') as Rvalue 
into #tbb4
from #tbb3
  
 ----->表七  5个记录为一组 执行代码生成  table #tbb5
if object_id('tempdb.dbo.#tbb5') is not null drop table #tbb5 
;WITH CTE AS(
    SELECT  ROW_NUMBER()OVER(ORDER BY GETDATE()) -1 RN
        ,*
    FROM  #tbb2  
)
,Nrs
AS
(
SELECT TOP ((SELECT COUNT(1)+COUNT(1)/5 FROM Cte)) ROW_NUMBER()OVER(ORDER BY GETDATE())-1 AS Nr FROM sys.columns AS a,sys.columns AS b,sys.columns AS c,sys.columns AS d
)
SELECT ISNULL(LTRIM(T2.[NOTEXT]),'第'+RTRIM((T1.Nr+7)/6)+'组')AS [DLTT],T1.Nr into  #tbb5
 
FROM Nrs T1
    LEFT JOIN CTE T2 ON T1.Nr%6=T2.RN%5 AND T1.Nr/6=T2.RN/5
ORDER BY T1.Nr
  
----->需要的结果是5行记录分为一组，再编号“第N组”
DECLARE @sql nvarchar(max)
SELECT @sql=N'select '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+' from(SELECT TOP ((SELECT MAX(nr) FROM #tbb5)) t.[DLTT] as '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+N' ,Nr FROM #tbb5 AS t union  SELECT ''第1组'',-1 )t order by t.Nr' FROM #tbb4 AS T4
EXEC(@sql)
二月十六 2017-06-13
  • 打赏
  • 举报
 回复 
----表一（数据库已经建立该表）

if object_id('tempdb..#DLT2017066') is not null drop table #DLT2017066

go 

create table #DLT2017066([id] int,[Notext] varchar(17),[NO1] varchar(2),[NO2] varchar(2),[NO3] varchar(2),[NO4] varchar(2),[NO5] varchar(2) )

insert #DLT2017066

select '224766','07 20 23 33 35','07','20','23','33','35' union all

select '224768','07 20 24 25 26','07','20','24','25','26' union all

select '224770','07 20 24 25 28','07','20','24','25','28' union all

select '224776','07 20 24 25 34','07','20','24','25','34' union all

select '224777','07 20 24 25 35','07','20','24','25','35' union all

select '224783','07 20 24 26 32','07','20','24','26','32' union all

select '224793','07 20 24 27 34','07','20','24','27','34' union all

select '224795','07 20 24 28 29','07','20','24','28','29' union all

select '224800','07 20 24 28 34','07','20','24','28','34' union all

select '224801','07 20 24 28 35','07','20','24','28','35' union all

select '224802','07 20 24 29 30','07','20','24','29','30'

 

----表二（数据库已经建立该表）

if object_id('tempdb.dbo.#BLU') is not null drop table #BLU 

go

create table #BLU([blu1] varchar(2),[blu2] varchar(2))

insert #BLU 

select '01','07' union all 

select '04','07' union all

select '06','07' union all

select '07','09' union all

select '07','11'

 

---->表三  是表一记录与表二的记录一一组合，执行代码生成  table #tbb1

 

if object_id('tempdb.dbo.#tbb1') is not null drop table #tbb1 

go

select 

rtrim(a.id)AS ID,

rtrim(a.Notext)AS NOTEXT,

rtrim(a.no1)AS NO1,

rtrim(a.no2)AS NO2,

rtrim(a.no3)AS NO3,

rtrim(a.NO4)AS NO4,

rtrim(a.no5)AS NO5,

ltrim(b.blu1)AS BLU1,

ltrim(b.blu2)AS BLU2  into #tbb1 

from  #DLT2017066 a,#BLU b  --检查 select *from  #DLT2017066  检查  select *from  #BLU

 

---->表四   设定条件值查询结果， 执行代码生成  table #tbb2

 

if object_id('tempdb.dbo.#tbb2') is not null drop table #tbb2  

                                                                                 

Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT 

into #tbb2

From #tbb1

WHERE Notext like '%24%' OR  Notext like '%25%' OR  Notext like '%26%'   ----修改为“OR”

 

----->表五   统计出#tbb2的数量值   执行代码生成  table #tbb3

if object_id('tempdb.dbo.#tbb3') is not null drop table #tbb3

SELECT COUNT(*) AS num 

into #tbb3

FROM #tbb2

 

----->表六  添加记录值的单位 执行代码生成  table #tbb4

if object_id('tempdb.dbo.#tbb4') is not null drop table #tbb4

select (cast('' as varchar(50))+'大乐透 2017066期单式投注')as [name],

(cast(num as varchar(10))+'注')as num,(cast(num*2 as varchar(10))+'元') as Rvalue 

into #tbb4

from #tbb3

  

 ----->表七  5个记录为一组 执行代码生成  table #tbb5

if object_id('tempdb.dbo.#tbb5') is not null drop table #tbb5 

;WITH CTE AS(

    SELECT  ROW_NUMBER()OVER(ORDER BY GETDATE()) -1 RN

        ,*

    FROM  #tbb2  

)

,Nrs

AS

(

SELECT TOP ((SELECT COUNT(1)+COUNT(1)/5 FROM Cte)) ROW_NUMBER()OVER(ORDER BY GETDATE())-1 AS Nr FROM sys.columns AS a,sys.columns AS b,sys.columns AS c,sys.columns AS d

)

SELECT ISNULL(LTRIM(T2.[NOTEXT]),'第'+RTRIM(T1.Nr/5)+'组')AS [DLTT],T1.Nr into  #tbb5

 

FROM Nrs T1

    LEFT JOIN CTE T2 ON T1.Nr%6=T2.RN%5 AND T1.Nr/6=T2.RN/5

ORDER BY T1.Nr

  

----->需要的结果是5行记录分为一组，再编号“第N组”

DECLARE @sql nvarchar(max)

SELECT @sql=N'select '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+' from(SELECT TOP ((SELECT MAX(nr) FROM #tbb5)-1) t.[DLTT] as '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+N' ,Nr FROM #tbb5 AS t union  SELECT ''第1组'',-1 )t order by t.Nr' FROM #tbb4 AS T4

EXEC(@sql)


RICHEER COCA 2017-06-12
  • 打赏
  • 举报
 回复
修改 #tbb2 where 语句里的 AND -修改为“OR”，可以看到结果。 


----表一（数据库已经建立该表）
if object_id('tempdb..#DLT2017066') is not null drop table #DLT2017066
go 
create table #DLT2017066([id] int,[Notext] varchar(17),[NO1] varchar(2),[NO2] varchar(2),[NO3] varchar(2),[NO4] varchar(2),[NO5] varchar(2) )
insert #DLT2017066
select '224766','07 20 23 33 35','07','20','23','33','35' union all
select '224768','07 20 24 25 26','07','20','24','25','26' union all
select '224770','07 20 24 25 28','07','20','24','25','28' union all
select '224776','07 20 24 25 34','07','20','24','25','34' union all
select '224777','07 20 24 25 35','07','20','24','25','35' union all
select '224783','07 20 24 26 32','07','20','24','26','32' union all
select '224793','07 20 24 27 34','07','20','24','27','34' union all
select '224795','07 20 24 28 29','07','20','24','28','29' union all
select '224800','07 20 24 28 34','07','20','24','28','34' union all
select '224801','07 20 24 28 35','07','20','24','28','35' union all
select '224802','07 20 24 29 30','07','20','24','29','30'

----表二（数据库已经建立该表）
if object_id('tempdb.dbo.#BLU') is not null drop table #BLU 
go
create table #BLU([blu1] varchar(2),[blu2] varchar(2))
insert #BLU 
select '01','07' union all 
select '04','07' union all
select '06','07' union all
select '07','09' union all
select '07','11'

---->表三  是表一记录与表二的记录一一组合，执行代码生成  table #tbb1

if object_id('tempdb.dbo.#tbb1') is not null drop table #tbb1 
go
select 
rtrim(a.id)AS ID,
rtrim(a.Notext)AS NOTEXT,
rtrim(a.no1)AS NO1,
rtrim(a.no2)AS NO2,
rtrim(a.no3)AS NO3,
rtrim(a.NO4)AS NO4,
rtrim(a.no5)AS NO5,
ltrim(b.blu1)AS BLU1,
ltrim(b.blu2)AS BLU2  into #tbb1 
from  #DLT2017066 a,#BLU b  --检查 select *from  #DLT2017066  检查  select *from  #BLU

---->表四   设定条件值查询结果， 执行代码生成  table #tbb2

if object_id('tempdb.dbo.#tbb2') is not null drop table #tbb2  
                                                                                
Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT 
into #tbb2
From #tbb1
WHERE Notext like '%24%' OR  Notext like '%25%' OR  Notext like '%26%'   ----修改为“OR”

----->表五   统计出#tbb2的数量值   执行代码生成  table #tbb3
if object_id('tempdb.dbo.#tbb3') is not null drop table #tbb3
SELECT COUNT(*) AS num 
into #tbb3
FROM #tbb2

----->表六  添加记录值的单位 执行代码生成  table #tbb4
if object_id('tempdb.dbo.#tbb4') is not null drop table #tbb4
select (cast('' as varchar(50))+'大乐透 2017066期单式投注')as [name],
(cast(num as varchar(10))+'注')as num,(cast(num*2 as varchar(10))+'元') as Rvalue 
into #tbb4
from #tbb3
 
 ----->表七  5个记录为一组 执行代码生成  table #tbb5
if object_id('tempdb.dbo.#tbb5') is not null drop table #tbb5 
;WITH CTE AS(
    SELECT  ROW_NUMBER()OVER(ORDER BY GETDATE()) -1 RN
        ,*
    FROM  #tbb2  
)
,Nrs
AS
(
SELECT TOP ((SELECT COUNT(1)+COUNT(1)/5 FROM Cte)) ROW_NUMBER()OVER(ORDER BY GETDATE())-1 AS Nr FROM sys.columns AS a,sys.columns AS b,sys.columns AS c,sys.columns AS d
)
SELECT ISNULL(LTRIM(T2.[NOTEXT]),'第'+RTRIM(T1.Nr%5+2)+'组')AS [DLTT],T1.Nr into  #tbb5

FROM Nrs T1
    LEFT JOIN CTE T2 ON T1.Nr%6=T2.RN%5 AND T1.Nr/6=T2.RN/5
ORDER BY T1.Nr
 
----->需要的结果是5行记录分为一组，再编号“第N组”
DECLARE @sql nvarchar(max)
SELECT @sql=N'select '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+' from(SELECT t.[DLTT] as '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+N' ,Nr FROM #tbb5 AS t union  SELECT ''第1组'',-1 )t order by t.Nr' FROM #tbb4 AS T4
EXEC(@sql)
这时有10组的结果，应该是第1组——第10组，但实际结果如同楼主#3 描述。要的不是第1 /2 /3 /4 /5 /6 /2 /3 /4 /5 /6组，求解中.......
RICHEER COCA 2017-06-12
  • 打赏
  • 举报
 回复
请教：有办法让最后这个“第N组”不要出现吗？期盼着...... 原因：当有300组的时候，应该是 第1组 ...... 第2组 ...... ....... ...... 第300组 ...... 但执行代码后，出现的是 第1组 ...... 第2组 ...... 第3组 ....... 第4组 ...... 第5组 ...... 第6组 ...... 第2组 --------？？？ ...... 第3组 ....... 第4组 ...... 第5组 ...... 第6组 ...... 第2组 --------？？？
RICHEER COCA 2017-06-12
  • 打赏
  • 举报
 回复
非常感谢1 楼 sinat_28984567 的回复
引用 1 楼 sinat_28984567 的回复:
上边那么多没仔细看，直接从t5开始处理的，第一个组有点不好加，用的union 

----->需要的结果是5行记录分为一组，再编号“第N组”
DECLARE @sql nvarchar(max)
SELECT @sql=N'select '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+' from(SELECT t.[DLTT] as '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+N' ,Nr FROM #tbb5 AS t union  SELECT ''第1组'',-1 )t order by t.Nr' FROM #tbb4 AS T4
EXEC(@sql)
结果里出现“第6组” 

大乐透 2017066期单式投注25注50元
............
............
07 20 24 25 35+07 09
07 20 24 26 32+07 09
第5组
07 20 24 25 26+07 11
07 20 24 25 28+07 11
07 20 24 25 34+07 11
07 20 24 25 35+07 11
07 20 24 26 32+07 11
第6组
请教：有办法让这个“第6组”不要出现吗？期盼着......
二月十六 2017-06-12
  • 打赏
  • 举报
 回复
上边那么多没仔细看，直接从t5开始处理的，第一个组有点不好加，用的union


----表一（数据库已经建立该表）

if object_id('tempdb..#DLT2017066') is not null drop table #DLT2017066

go 

create table #DLT2017066([id] int,[Notext] varchar(17),[NO1] varchar(2),[NO2] varchar(2),[NO3] varchar(2),[NO4] varchar(2),[NO5] varchar(2) )

insert #DLT2017066

select '224766','07 20 23 33 35','07','20','23','33','35' union all

select '224768','07 20 24 25 26','07','20','24','25','26' union all

select '224770','07 20 24 25 28','07','20','24','25','28' union all

select '224776','07 20 24 25 34','07','20','24','25','34' union all

select '224777','07 20 24 25 35','07','20','24','25','35' union all

select '224783','07 20 24 26 32','07','20','24','26','32' union all

select '224793','07 20 24 27 34','07','20','24','27','34' union all

select '224795','07 20 24 28 29','07','20','24','28','29' union all

select '224800','07 20 24 28 34','07','20','24','28','34' union all

select '224801','07 20 24 28 35','07','20','24','28','35' union all

select '224802','07 20 24 29 30','07','20','24','29','30'



----表二（数据库已经建立该表）

if object_id('tempdb.dbo.#BLU') is not null drop table #BLU 

go

create table #BLU([blu1] varchar(2),[blu2] varchar(2))

insert #BLU 

select '01','07' union all 

select '04','07' union all

select '06','07' union all

select '07','09' union all

select '07','11'



---->表三  是表一记录与表二的记录一一组合，执行代码生成  table #tbb1



if object_id('tempdb.dbo.#tbb1') is not null drop table #tbb1 

go

select 

rtrim(a.id)AS ID,

rtrim(a.Notext)AS NOTEXT,

rtrim(a.no1)AS NO1,

rtrim(a.no2)AS NO2,

rtrim(a.no3)AS NO3,

rtrim(a.NO4)AS NO4,

rtrim(a.no5)AS NO5,

ltrim(b.blu1)AS BLU1,

ltrim(b.blu2)AS BLU2  into #tbb1 

from  #DLT2017066 a,#BLU b  --检查 select *from  #DLT2017066  检查  select *from  #BLU



---->表四   设定条件值查询结果， 执行代码生成  table #tbb2



if object_id('tempdb.dbo.#tbb2') is not null drop table #tbb2  

                                                                                

Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT 

into #tbb2

From #tbb1

WHERE Notext like '%24%'and  Notext like '%25%' or  Notext like '%26%'



----->表五   统计出#tbb2的数量值   执行代码生成  table #tbb3

if object_id('tempdb.dbo.#tbb3') is not null drop table #tbb3

SELECT COUNT(*) AS num 

into #tbb3

FROM #tbb2



----->表六  添加记录值的单位 执行代码生成  table #tbb4

if object_id('tempdb.dbo.#tbb4') is not null drop table #tbb4

select (cast('' as varchar(50))+'大乐透 2017066期单式投注')as [name],

(cast(num as varchar(10))+'注')as num,(cast(num*2 as varchar(10))+'元') as Rvalue 

into #tbb4

from #tbb3

 

 ----->表七  5个记录为一组 执行代码生成  table #tbb5

if object_id('tempdb.dbo.#tbb5') is not null drop table #tbb5 

;WITH CTE AS(

    SELECT  ROW_NUMBER()OVER(ORDER BY GETDATE()) -1 RN

        ,*

    FROM  #tbb2  

)

,Nrs

AS

(

SELECT TOP ((SELECT COUNT(1)+COUNT(1)/5 FROM Cte)) ROW_NUMBER()OVER(ORDER BY GETDATE())-1 AS Nr FROM sys.columns AS a,sys.columns AS b,sys.columns AS c,sys.columns AS d

)

SELECT ISNULL(LTRIM(T2.[NOTEXT]),'第'+RTRIM(T1.Nr%5+2)+'组')AS [DLTT],T1.Nr into  #tbb5



FROM Nrs T1

    LEFT JOIN CTE T2 ON T1.Nr%6=T2.RN%5 AND T1.Nr/6=T2.RN/5

ORDER BY T1.Nr

 

----->需要的结果是5行记录分为一组，再编号“第N组”

DECLARE @sql nvarchar(max)

SELECT @sql=N'select '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+' from(SELECT t.[DLTT] as '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+N' ,Nr FROM #tbb5 AS t union  SELECT ''第1组'',-1 )t order by t.Nr' FROM #tbb4 AS T4

EXEC(@sql)


SQL 给一个数据查询结果添加一列从0到n的序号
 现在我要将这个排序的结果添加一列序号，从0到100，这样可以看到前100名每一名玩家所对应的等级。  我原有的查询语句如下：    查询结果如下： 为了添加一列序号，我只需这样来写：select 名次=Row_...
组合问题，n个元素分成k组-5
题目要求: P(n,k)代表将n个元素分成k个非空分组集合的总集。例如，当L=[1,2,3], P（3，2）=12,3; 13,2; 1,23.我们就假设此n个元素是从1...思想：结果列表R是三重列表，一个集合放在一个二重列表中，所有的集合放在...
求1~n的全排列组合
2.思路：通过深度优先搜索来做，用一个数组来标记当前位置，一个数组来记录当前位置所代表的的值，只要未标记就加入排列数组中，当数组中元素达到n时，输出这个排列，并取消对应位置的标记 3.代码如下所示： ...
算法-1到n中所有和为m的组合
题目： 输入两个整数 n 和 m，从数列1，2，3…….n 中随意取几个数,使其和等于 m ,要求将其中所有的可能组合列出来。
python创建矩阵_python创建n行m列数组示例
python创建n行m列数组示例我就废话不多说了，直接上代码吧！>>> matrix=[None]*2>>> print(matrix)[None, None]>>> for i in range(len(matrix)):matrix[i ]=[0]*3>>> print(matrix)[[0, 0, 0], [0, 0, 0]]>>>例n = ...
疑难问题

22,207

社区成员

121,730

社区内容

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

加载中

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

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

+ 用AI写文章