日期:2014-05-18 浏览次数:20552 次
--SQL2005環境: ;with NewYear as (select 1 as ID union all select ID+1 as ID from NewYear where ID<888--以888為例 ),NewYear168 as (select *,(ID-1)/88 as ID2 from NewYear where (ID/8)=ID*1.0/8 ),NewYear2010 as ( select top 88 t2.ID as [Floor], rtrim((t2.ID-1)%188+1) as Qty --188可為吉祥數字 from (select distinct ID2 from NewYear168)t cross apply (select top 10 * from NewYear168 where ID2=t.ID2 order by NewID())t2 order by newID()) select [Floor]as 樓層, cast(stuff(replace([Qty],'4','8'),len([Qty]),1,'8') as int) as 中獎紅包 from NewYear2010 order by 1 option(MAXRECURSION 0) --把結尾數改為8,把中間有其它數字有4的改為8。
樓層 中獎紅包 8 8 24 28 32 38 40 88 64 68 88 88 96 98 104 108 112 118 120 128 128 128 136 138 152 158 160 168 168 168 176 178 184 188 192 8 208 28 216 28 232 88 240 58 248 68 256 68 264 78 272 88 288 108 296 108 312 128 336 188 344 158 368 188 376 188 384 8 392 18 400 28 408 38 416 88 424 88 432 58 440 68 448 78 456 88 464 88 480 108 488 118 496 128 504 128 512 138 520 188 528 158 536 168 544 168 552 178 560 188 568 8 576 18 600 38 608 88 616 58 624 68 632 68 648 88 656 98 664 108 672 108 680 118 688 128 696 138 704 188 720 158 728 168 736 178 744 188 752 188 760 8 776 28 784 38 792 88 808 58 816 68 824 78 832 88 848 98 856 108 864 118 880 128 888 138
--SQL2005環境: if object_id('Tempdb..#NewYear2208') is not null drop table #NewYear2208 ;with NewYear as (select 889 as ID union all select ID+1 as ID from NewYear where ID<2208--以2208樓 ),NewYear168 as (select *,(ID-889)/88 as ID2 from NewYear where (ID-888)/8=(ID-888)*1.0/8 ) select t2.ID as [Floor] ,Qty=case row_Number()over(partition by t.ID2 order by newID()) when 1 then 188 when 2 then 88 when 3 then 68 else 0 end into #NewYear2208 from (select distinct ID2 from NewYear168)t cross apply (select top 8 * from NewYear168 where ID2=t.ID2 order by NewID() )t2 option(MAXRECURSION 0) ;with HappyNewYear as ( select [Floor],NewRow=row_Number()over(order by newID()) from #NewYear2208 where Qty=0 ) ,NewYear2010 as ( select * from #NewYear2208 where Qty>0 union all select [Floor], Qty=case when NewRow<=3 then 168 when NewRow<=6 then 118 when NewRow<=7 then 108 when NewRow<=8 then 38 when NewRow<=9 then 28 else ((abs(checksum(newID()))-1)-1)%18+1 end from HappyNewYear ) select [Floor]as 樓層,cast(stuff([Qty],len([Qty]),1,'8') as int) as 中獎紅包 from NewYear2010 order by 1
![]()
推荐阅读更多>
- 一个SQL有关问题!看能否实现
- 行列拆分转换,该怎么处理
- row_number()是什么意思呀?该如何处理
- 依据列值生成多行记录的新表,通过语句实现
- 求教高手一条高难度SQL语句解决办法
- 关于mysql存储过程有关问题
- mysql,30G二进制文件的数据恢复,该怎么处理
- 关于SQL Server 2008插入记录的疑问(五一过后赶着交老师作业呐,)解决方法
- 用SQL2008语句怎么将EXCEL导入SQL中~
- 怎么根据标志位选择数据
- sql中怎么按类别取数据
- 合并记录:每条记录中除时间不同外,其他部分基本相同,将其合并成同一条记录解决方法
- vc中应用ado的_ConnectionPtr连接sql2005数据库求解!解决办法
- 主从表查询统计,
- 请教SQLSERVER2008有没有好的开发工具,2005的时候用TOAD,2008有吗
- SQL SERVER 导出到txt有关问题
- 公司每天要用excel做很多表,为什么不用数据库来做呢,小弟我想把它升级到数据库
- 存储过程的困惑!解决方案
- 关于SQL SERVER2005通过存储图片的有关问题
- 无法运行sql服务器 ,提示拒绝访问,发生异常5