日期:2014-05-18 浏览次数:20532 次
--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 语句含有计算式时怎么引入变量
- SQL2000无法启动,该如何处理
- 写个触发器实现以下功能:企业内部人员调动时实现部门人数的增减;当删除某部门时,将该部门对应的员工全部删除,该怎么处理
- 除了分库,还有什么招能面对这种有关问题
- sql 2000升级到sql 2005后.asp.net 存储总是间歇性出错解决方法
- 三年经验DBA,收到BI岗位的面试通知,去不去解决办法
- 分享:查询优化案例一解决办法
- 查询了5万累次,突然提示“连接失败”,但把SQL语句放在数据库中运行又是对的,为何
- 请问关于服务器登录用户和数据库用户的区别和联系
- 主子表Group by求和的有关问题
- sql有好多,许多字段有关问题
- 关于一个数据表设计的有关问题
- 亲~你上班了吗?该如何处理
- 求高手解决,查询筛选排除指定值。该怎么解决
- 关于一部分Groupby
- 如何批量删除整个库中表内相同的内容
- ASP+SQL中,UPDATE语法出错解决思路
- 求一条查询语句的写法。该如何解决
- -散分,工作还算顺利,顺便澄清一上,小弟我的工资一年10k,不是一个月-