日期:2014-05-18 浏览次数:20531 次
--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
![]()
推荐阅读更多>
- 获取当前SQLServer连接数解决思路
- 求查询为最大值的记录,该怎么解决
- 交叉求和,请好心人来给小弟弟帮帮忙,就先在此谢过,该怎么处理
- 大家看看这个代码怎么写
- 请问sql怎么写。帮帮忙,多谢
- 快速更新数据的有关问题
- 关于页面中显示多个列表对数据库的压力有关问题
- mssql 按字段重复行取第一行的有关问题
- MS SQL的BUG?该怎么解决
- 怎么用SQL语句检索单位转换的数量计算啊
- 这个查询如何做
- 求 数据库阻塞 完整解决方案解决方案
- sql2012支持WINXP32位系统吗?解决办法
- 记数不重复记录的SQL话语
- 将数据类型 varchar 转换为 numeric 时出错。请问!
- 为什么微软建议用 TOP N 来代替 SET ROWCOUNT ?解决方案
- contains的应用
- sqlserver 2000 telnet 1433端口不通解决办法
- sql server 2005 中没有发现solution explorer面板怎么处理
- 截取两个字符串的不同字符解决办法