22,209
社区成员
发帖
与我相关
我的任务
分享
----建立表一
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'
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
----当期的统计 希望是代码完成统计并显示结果:大乐透 2017066期单式投注25注50元
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%'
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN
,*
FROM #tbb2
)
SELECT ISNULL(LTRIM(T2.notext),'')AS [大乐透 2017066期单式投注25注50元] -----这个字段是手工添写完成的,希望用代码完成。
---问题:一注2元,如何自动统计出结果里有225注,并显示字段名为“大乐透 2017066期单式投注25注50元”
FROM master..spt_values T1
LEFT JOIN CTE T2 ON T1.number=T2.RN+(T2.RN-1)/5-1
WHERE T1.type='P' AND T1.number<=(SELECT MAX(RN+(RN-1)/5-1)FROM CTE)
大乐透 2017066期单式投注25注50元 ----这个字段是手工添写完成的,希望用代码完成。
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 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 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 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 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
LEFT JOIN #tbb3 T2 ON T1.number=T2.RN1+1
WHERE T1.number<=t3.MaxNumber+1
Select NOTEXT+'+'+BLU1+' '+BLU2 AS NOTEXT ,ROW_NUMBER()OVER(ORDER BY GETDATE())+(ROW_NUMBER()OVER(ORDER BY GETDATE())-1)/5-1 AS RN1
into #tbb2
From #tbb1
WHERE Notext like '%24%'and Notext like '%25%' or Notext like '%26%'
---最后的动态语句
DECLARE @sql nvarchar(max)
SELECT @sql='
SELECT ISNULL(LTRIM(T2.notext),'''') 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 1000 and n2.number between 1 and 1000
) as T1
INNER JOIN #tbb3 AS T3 ON 1=1
LEFT JOIN #tbb2 T2 ON T1.number=T2.RN1
WHERE T1.number<=t3.MaxNumber
'
FROM #tbb3 AS T3
PRINT @sql
EXEC(@sql)
按照t1.number排序,就能看到分组
大乐透 2017066期单式投注25注25元
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 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 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 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 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
select * from master..spt_values where type='p'
--->消息
(2048 行受影响)
master..spt_values--记录数不够时引起错误的结果的,因为有时查询结果会有5万多条
请教出现数量不够50999行时的绝招
----表一(数据库已经建立该表)
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 ,ROW_NUMBER()OVER(ORDER BY GETDATE())RN
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.#tbb3') is not null drop table #tbb3
select COUNT(*) AS qty, MAX(RN+(RN-1)/5-1) AS MaxNumber,
N'大乐透 2017066期单式投注' AS [name],LTRIM(COUNT(*)) +N'注' as num,LTRIM(COUNT(*))+N'元' as Rvalue
into #tbb3
from #tbb2
----->表七 5个记录为一组 执行代码生成 table #tbb5
--if object_id('tempdb.dbo.#tbb5') is not null drop table #tbb5
DECLARE @sql nvarchar(max)
SELECT @sql='
SELECT ISNULL(LTRIM(T2.notext),'''') AS '+QUOTENAME(T3.name+t3.num+t3.Rvalue)+CHAR(13)
+N'FROM master..spt_values T1
INNER JOIN #tbb3 AS T3 ON 1=1
LEFT JOIN #tbb2 T2 ON T1.number=T2.RN+(T2.RN-1)/5-1
WHERE T1.type=''P'' AND T1.number<=t3.MaxNumber
'
FROM #tbb3 AS T3
PRINT @sql
EXEC(@sql)
----表一(数据库已经建立该表)
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
----->需要的结果
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)
SELECT ISNULL(LTRIM(T2.notext),'') AS [测试数据40577注81154元]
FROM master..spt_values T1
INNER JOIN #tbb5 AS T3 ON 1=1
LEFT JOIN #tbb3 T2 ON T1.number=T2.RN+(T2.RN-1)/5-1
WHERE T1.type='P' AND T1.number<=t3.MaxNumber
(2048 行受影响)
优化如下,是否为最佳方案?
IF OBJECT_ID('master..spt_values_max','U') IS NOT NULL
DROP TABLE master..spt_values_max
GO
CREATE TABLE master..spt_values_max(
id BIGINT
)
GO
DECLARE @i bigINT,@iMax bigINT
SET @i=1
SET @iMax=90000 --不够自己加
WHILE @i<=@iMax
BEGIN
INSERT INTO master..spt_values_max (id)
SELECT ISNULL((SELECT MAX(id) FROM master..spt_values_max),0)+1
SET @i=@i+1
END
----表一(数据库已经建立该表)
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())RN
,*
FROM #tbb2
)
SELECT ISNULL(LTRIM(T2.notext),'') AS [DLTT] into #tbb5
FROM master..spt_values T1
LEFT JOIN CTE T2 ON T1.number=T2.RN+(T2.RN-1)/5-1
WHERE T1.type='P' AND T1.number<=(SELECT MAX(RN+(RN-1)/5-1)FROM CTE)
---->问题: 如何用代码 把#tbb5 的字段名DLTT转换成为表 #tbb3的值='大乐透 2017066期单式投注25注50元'
这个代码请大神指点,谢谢!
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)
另外我觉得你的代码tbb3和tbb4可以合并为一个,在创建#tbb2直接插入需要这样也不需要CTE,不修改不影响结果
create table #t2 (tableName varchar(100))
insert into #t2
SELECT name FROM sysobjects where xtype='U'
SELECT 'select * from t_FABalance a, (select * from DT_2DHORIZON where PROJECTCODE<>null ) c, DT_PROJECT b where a.PROJECTCODE=b.PROJECTCODE and b.PROJECTCODE=c.PROJECTCODE'
AS SqlStr into #t1
SELECT tableName FROM #t2 A inner join #t1 B on A.tableName like '%DLT2017066%'
DROP TABLE #T1 DROP TABLE #T2 --检查 SELECT * FROM #T1 检查 SELECT * FROM #T2
尝试二:#tbb2 的记录数执行代码也可以得到了
select count(*) from #tbb2
接下来的我就没辙了
新的字段名 = 表一的名称“#DLT2017066 ” + 表#tbb2的记录数 + 表#tbb2的记录数 * 2 + 元(单位)。