日期:2014-05-18 浏览次数:20566 次
--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中模糊匹配
- 如何删掉自动增长
- 分组求和有关问题
- 若是设置唯一索引或普通索引,怎么令表按索引的排序方式显示
- 为什么用表查询比用从函数返回的结果集快得多呢?解决思路
- 如何插入两个日期之间的所有日期
- 看了几篇关于sp_executesql的稿件,有句话不太明白
- 请问一个SQL2005验证模式有关问题
- 外部sql数据库的连接有关问题
- 请问大侠帮忙,以下的sql如何写
- 数据库中一条删除语句的有关问题
- 三张表判断A表里的ID是另外两张表哪位高手的ID
- 请教通过ResultSet得到PreparedStatement对像,然后关闭PreparedStatement可行吗
- 从一张表中update数据到另一张表中!诸位大大帮忙琢磨琢磨
- 类似ERP 多阶BOM成本从下往上滚算的语句
- Alter修改表结构对数据存储的影响(二),该怎么解决
- 急关于自定义函数的有关问题
- 怎么查询一段序列数字中缺少的数字的语句