日期:2014-05-18 浏览次数:20546 次
--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 启动不了解决思路
- 诡异的有关问题,大神帮忙
- 比较有挑战性的SQL语句,小弟我用游标完成,但是效果不理想,看看谁能用一个SQL语句或者更简单的方法来完成
- 查询事务有优先级设置吗?该怎么处理
- 一个sql的有关问题,大家进来看看吧,标题文字说不清楚
- 求一個通用的SQL,该如何解决
- 索引服务的有关问题 (全文检索) 为什么不准
- SQLServer中表名想用输入的参数该如何搞
- 带@的表有什么好处,该如何处理
- 怎么遍历表数据
- -冒个泡-解决思路
- 百分请问sql 句子select top的执行效率有关问题
- SQL作业对于执行vbs脚本有何要求?该如何解决
- 求答案:数据分组显示,增加递增编号解决方法
- 一个最大值的有关问题
- 关系倒底要不要建解决思路
- 求解一个简单格式转换的有关问题
- 这样的数据库怎么设计或者怎么设计查询语句