CREATE TABLE [pro_rank] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[m_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_cid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_order] [int] NOT NULL ,
[p_num] [int] NULL ,
[p_time] [datetime] NULL ,
[p_isexit] [char] (1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_pro_rank_p_isexit] DEFAULT (0),
CONSTRAINT [PK_pro_rank] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO表记录如下:
会员编号 订单号 子订单号 排序
2 MT20060518002 P200605270001 P200605270001_001 1 0 3 MT20060518002 P200605270001 P200605270001_002 2 04 MT20060518002 P200605270001 P200605270001_003 3 05 MT20060518002 P200605270001 P200605270001_004 4 06 MT20060518002 P200605270001 P200605270001_005 5 07 MT20060518002 P200605270001 P200605270001_006 6 08 MT20060518002 P200605270001 P200605270001_007 7 09 MT20060518002 P200605270001 P200605270001_008 8 010 MT20060518002 P200605270001 P200605270001_009 9 011 MT20060518002 P200605270001 P200605270001_010 10 012 MT20060518002 P200605270001 P200605270001_011 11 013 MT20060518002 P200605270001 P200605270001_012 12 014 MT20060518001 P200605270002 P200605270002_001 13 015 MT20060518001 P200605270002 P200605270002_002 14 016 MT20060518001 P200605270002 P200605270002_003 15 017 MT20060518002 P200605280001 P200605280001_001 16 018 MT20060518002 P200605280001 P200605280001_002 17 019 MT20060518002 P200605280001 P200605280001_003 18 020 MT20060518002 P200605280002 P200605280002_001 19 0网站销售出400份产品,则提供200份价值800元的产品赠送给排前200位的消费者,然后前200位排到当天队伍的后面,第201位上升至第一位,形成循环态势。
谢谢了
[id] [int] IDENTITY (1, 1) NOT NULL ,
[m_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_cid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_order] [int] NOT NULL ,
[p_num] [int] NULL ,
[p_time] [datetime] NULL ,
[p_isexit] [char] (1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_pro_rank_p_isexit] DEFAULT (0),
CONSTRAINT [PK_pro_rank] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO表记录如下:
会员编号 订单号 子订单号 排序
2 MT20060518002 P200605270001 P200605270001_001 1 0 3 MT20060518002 P200605270001 P200605270001_002 2 04 MT20060518002 P200605270001 P200605270001_003 3 05 MT20060518002 P200605270001 P200605270001_004 4 06 MT20060518002 P200605270001 P200605270001_005 5 07 MT20060518002 P200605270001 P200605270001_006 6 08 MT20060518002 P200605270001 P200605270001_007 7 09 MT20060518002 P200605270001 P200605270001_008 8 010 MT20060518002 P200605270001 P200605270001_009 9 011 MT20060518002 P200605270001 P200605270001_010 10 012 MT20060518002 P200605270001 P200605270001_011 11 013 MT20060518002 P200605270001 P200605270001_012 12 014 MT20060518001 P200605270002 P200605270002_001 13 015 MT20060518001 P200605270002 P200605270002_002 14 016 MT20060518001 P200605270002 P200605270002_003 15 017 MT20060518002 P200605280001 P200605280001_001 16 018 MT20060518002 P200605280001 P200605280001_002 17 019 MT20060518002 P200605280001 P200605280001_003 18 020 MT20060518002 P200605280002 P200605280002_001 19 0网站销售出400份产品,则提供200份价值800元的产品赠送给排前200位的消费者,然后前200位排到当天队伍的后面,第201位上升至第一位,形成循环态势。
谢谢了
[id] [int] IDENTITY (1, 1) NOT NULL ,
[m_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_cid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_order] [int] NOT NULL ,
[p_num] [int] NULL ,
[p_time] [datetime] NULL ,
[p_isexit] [char] (1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_pro_rank_p_isexit] DEFAULT (0),
CONSTRAINT [PK_pro_rank] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
表记录如下:
会员编号 订单号 子订单号 排序
2 MT20060518002 P200605270001 P200605270001_001 1 0
3 MT20060518002 P200605270001 P200605270001_002 2 0
4 MT20060518002 P200605270001 P200605270001_003 3 0
5 MT20060518002 P200605270001 P200605270001_004 4 0
6 MT20060518002 P200605270001 P200605270001_005 5 0
7 MT20060518002 P200605270001 P200605270001_006 6 0
8 MT20060518002 P200605270001 P200605270001_007 7 0
9 MT20060518002 P200605270001 P200605270001_008 8 0
10 MT20060518002 P200605270001 P200605270001_009 9 0
11 MT20060518002 P200605270001 P200605270001_010 10 0
12 MT20060518002 P200605270001 P200605270001_011 11 0
13 MT20060518002 P200605270001 P200605270001_012 12 0
14 MT20060518001 P200605270002 P200605270002_001 13 0
15 MT20060518001 P200605270002 P200605270002_002 14 0
16 MT20060518001 P200605270002 P200605270002_003 15 0
17 MT20060518002 P200605280001 P200605280001_001 16 0
18 MT20060518002 P200605280001 P200605280001_002 17 0
19 MT20060518002 P200605280001 P200605280001_003 18 0
20 MT20060518002 P200605280002 P200605280002_001 19 0
网站销售出400份产品,则提供200份价值800元的产品赠送给排前200位的消费者,然后前200位排到当天队伍的后面,第201位上升至第一位,形成循环态势。
谢谢了
[id] [int] IDENTITY (1, 1) NOT NULL ,
[m_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_cid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_order] [int] NOT NULL ,
[p_num] [int] NULL ,
[p_time] [datetime] NULL ,
[p_isexit] [char] (1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_pro_rank_p_isexit] DEFAULT (0),
CONSTRAINT [PK_pro_rank] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO表记录如下:
会员编号 订单号 子订单号 排序
2 MT20060518002 P200605270001 P200605270001_001 1 0 3 MT20060518002 P200605270001 P200605270001_002 2 04 MT20060518002 P200605270001 P200605270001_003 3 05 MT20060518002 P200605270001 P200605270001_004 4 06 MT20060518002 P200605270001 P200605270001_005 5 07 MT20060518002 P200605270001 P200605270001_006 6 08 MT20060518002 P200605270001 P200605270001_007 7 09 MT20060518002 P200605270001 P200605270001_008 8 010 MT20060518002 P200605270001 P200605270001_009 9 011 MT20060518002 P200605270001 P200605270001_010 10 012 MT20060518002 P200605270001 P200605270001_011 11 013 MT20060518002 P200605270001 P200605270001_012 12 014 MT20060518001 P200605270002 P200605270002_001 13 015 MT20060518001 P200605270002 P200605270002_002 14 016 MT20060518001 P200605270002 P200605270002_003 15 017 MT20060518002 P200605280001 P200605280001_001 16 018 MT20060518002 P200605280001 P200605280001_002 17 019 MT20060518002 P200605280001 P200605280001_003 18 020 MT20060518002 P200605280002 P200605280002_001 19 0网站销售出400份产品,则提供200份价值800元的产品赠送给排前200位的消费者,然后前200位排到当天队伍的后面,第201位上升至第一位,形成循环态势。
谢谢了
Set 排序=(Case When 排序<=200 Then 排序+200 When 排序>200 Then 排序-200 End)
Create Table TEST(ID Int,Name Varchar(10))
Insert TEST Select 1,'aa'
Union All Select 2,'bb'
Union All Select 3,'cc'
Union All Select 4,'dd'
Union All Select 5,'ee'
Union All Select 6,'ff'
Union All Select 7,'gg'
Union All Select 8,'hh'
Union All Select 9,'ii'
Union All Select 10,'jj'
GO
Update TEST Set ID=(Case When ID<=5 Then ID+5 When ID>5 Then ID-5 End)Select * From TEST Order By ID
GO
Drop Table TEST
GO
--Result
/*
ID Name
1 ff
2 gg
3 hh
4 ii
5 jj
6 aa
7 bb
8 cc
9 dd
10 ee
*/
请问:
例如:
会员编号 订单号 子订单号 排序 p_time
1 1111111 111122 1 2006-05-29
2 1111112 222 2 2006-05-29
...
200 1111200 11122 200 2006-05-29
201 1111201 111122 201 2006-05-29
202 1111202 111122 202 2006-05-29
...
400 1111400 111122 400 2006-05-29
401 1111401 111122 401 2006-05-28
...
你的意思是不是这样:经过执行一次存储过程结果变成这样
会员编号 订单号 子订单号 排序 p_time
201 1111201 111122 1 2006-05-29
202 1111202 111122 2 2006-05-29
...
400 1111400 111122 200 2006-05-29
1 1111111 111122 201 2006-05-29
2 1111112 222 202 2006-05-29
...
200 1111200 11122 400 2006-05-29
401 1111401 111122 401 2006-05-28
...
CREATE PROCEDURE tt
@p_time varchar(10)--'2006--05-29'
AS
Update pro_rank Set p_order=(Case When p_order<=200 Then p_order+200 When p_order>200 Then p_order-200 End)
WHERE convert(varchar(10),p_time,120) = @p_timeGO
你不应该变更数据的位置。这是相当耗费资源的。
只要建立交易日期和更新日期字段就可以了。
通过select top 200 * from table order by 更新日期,交易日期
来获得你要的数据,更新相关内容及更新日期
这是我后来写出来的,贴出来,请大家点评,谢谢。---2006-05-28 17:39pm
---排序算法
select * from pro_rank
---1.查看今天有几份3800的定单
declare @rnum int
select @rnum=count(id) from pro_money where p_isbalance='1' and p_time between '2006-05-27 0:00:00' and '2006-05-27 23:59:59'
set @rnum=@rnum/2--获得赠品的份数
/*--对赠品份数进行奇偶处理begin 不去做处理
if(@rnum%2=1)--奇
set @rnum=@rnum-1
else if(@rnum%2=0)--偶
set @rnum=@rnum*/
--对赠品份数进行奇偶处理end
--统计现在的排名总人数begin
declare @pnum int---总人数
declare @cnum int---总组数
select @pnum=count(id) from pro_rank
--对组数进行处理 3.几,算4组begin
if(@pnum%400=1)
set @cnum=@pnum/400+1
else if(@pnum%400=0)
set @cnum=@pnum
--对组数进行处理 3.几,算4组end
--统计现在的排名总人数end
--如果碰到赠品只有6份,但现在有4组(每组400),那么赠品怎么分 取int,只取整数
declare @evnum int ---每组所分的赠品数
set @evnum=@rnum/@cnuminsert into test select
'2006-09-29' , @cnumselect * from testselect * from pro_money where p_isbalance='1' and p_time between '2006-05-28 0:00:00' and '2006-05-28 23:59:59'
select * from pro_money where p_isbalance='1' and p_time between '2006-05-27 0:00:00' and '2006-05-27 23:59:59'select * from pro_rank
select m_id,p_id,p_cid,
case
when p_order<=8 then p_order-2
when p_order>8 and p_order<=8*2 then p_order-2*2
when p_order>8*2 and p_order<=8*3 then p_order-2*3
end p_order from pro_rank where p_order>2 ------找新排序规律2006-05-28 20:15:16pm
--第一组
select m_id,p_id,p_cid,
case
when p_order>8*0 and p_order<=8*0+2 then 19-6+(p_order-6*0)
when p_order>8*0+2 then p_order-2*1
end p_order from pro_rank where p_order<=8*1 and p_order>8*0
--第二组
select m_id,p_id,p_cid,
case
when p_order>8*1 and p_order<=8*1+2 then 19-6+(p_order-6*1)
when p_order>8*1+2 then p_order-2*2
end p_order from pro_rank where p_order<=8*2 and p_order>8*1
--第三组
select m_id,p_id,p_cid,
case
when p_order>8*2 and p_order<=8*2+2 then 19-6+(p_order-6*2)
when p_order>8*2+2 then p_order-2*3
end p_order from pro_rank where p_order<=8*3 and p_order>8*2--用while循环
declare @i int
set @i=1
while @i<=3
begin
insert into pro_rank2
select m_id,p_id,p_cid,
case
when p_order>8*(@i-1) and p_order<=8*(@i-1)+2 then 19-6+(p_order-6*(@i-1))
when p_order>8*(@i-1)+2 then p_order-2*@i
end p_order,
p_num,
p_time,
p_isexit from pro_rank where p_order<=8*@i and p_order>8*(@i-1) order by p_order
Set @i=@i+1
end
----以下结果测试正常ok
select * from pro_rank2
--delete from pro_rank2------------------------begin
---排序算法成熟 2006-05-28 22:35:19pm
/*
19:表总排队数@pnum
6:表总赠品数@rnum
8:表每组总数@gnum
2:表每组赠品 @evnum
3:表有几组总组数 @cnum
*/
---1.取以上参数
---1.1总排队数(总人数)
declare @pnum int
select @pnum=count(id) from pro_rank ---1.2总赠品数
declare @rnum int
select @rnum=count(id) from pro_money where p_isbalance='1' and p_time between '2006-05-27 0:00:00' and '2006-05-27 23:59:59'
set @rnum=@rnum/2--获得赠品的份数---1.3每组总数 400
declare @gnum int
set @gnum=400---1.4总组数
---1.4条件一:总人数@pnum
declare @cnum int---总组数
--对组数进行处理 3.几,算4组begin
if(@pnum%@gnum=1)
set @cnum=@pnum/@gnum+1
else if(@pnum%@gnum=0)
set @cnum=@pnum
--对组数进行处理 3.几,算4组end---1.5每组赠品数
---1.5条件一:总人数@pnum
---1.5条件二:总组数@cnum
--如果碰到赠品只有6份,但现在有4组(每组400),那么赠品怎么分 取int,只取整数
declare @evnum int ---每组所分的赠品数
set @evnum=@rnum/@cnum---2.执行循环插入新排序到临时表
--用while循环
declare @i int
set @i=1
while @i<=@cnum
begin
insert into pro_rank2
select m_id,p_id,p_cid,
case
when p_order>@gnum*(@i-1) and p_order<=@gnum*(@i-1)+@evnum then @pnum-@rnum+(p_order-@rnum*(@i-1))
when p_order>@gnum*(@i-1)+@evnum then p_order-@evnum*@i
end p_order,
p_num,
p_time,
p_isexit from pro_rank where p_order<=@gnum*@i and p_order>@gnum*(@i-1)
Set @i=@i+1
end
------------------------end
select * from pro_rank2
delete from pro_rank2
---排序算法成熟 2006-05-28 22:35:19pm
/*
19:表总排队数@pnum
6:表总赠品数@rnum
8:表每组总数@gnum
2:表每组赠品 @evnum
3:表有几组总组数 @cnum
*/
----2006-05-28 23:21:19pm 语句放在一起
---convert(varchar(10),getdate(),120) 取系统时间后,取当前日期
declare @pnum int --总人数
declare @rnum int --总赠品
declare @gnum int --每组人数
declare @cnum int---总组数
declare @evnum int ---每组所分的赠品数select @pnum=count(id) from pro_rank
select @rnum=count(id) from pro_money where p_isbalance='1' and p_time between convert(varchar(10),getdate(),120)+' 0:00:00' and convert(varchar(10),getdate(),120)+' 23:59:59'
set @rnum=@rnum/2--获得赠品的份数
set @gnum=400
--对组数进行处理 3.几,算4组begin %是相除取的余数
if(@pnum%@gnum!=0)
set @cnum=@pnum/@gnum+1
if(@pnum%@gnum=0)
set @cnum=@pnum/@gnum
--对组数进行处理 3.几,算4组endset @evnum=@rnum/@cnum--用while循环
declare @i int
set @i=1
while @i<=@cnum
begin
-------------------------------------送赠品算法begin
---取次数
declare @xnum int
select @xnum=p_num from pro_rank where p_order>@gnum*(@i-1) and p_order<=@gnum*(@i-1)+@evnum
set @xnum=@xnum+1
declare @xone varchar(10)
set @xone=@xnum
---往奖金表插记录begin
insert into pro_moneytotal(m_id,p_cause,p_money,p_time)
select m_id,'订单'+p_cid+'第'+@xone+'次循环奖励',800,getdate()
from pro_rank where p_order>@gnum*(@i-1) and p_order<=@gnum*(@i-1)+@evnum
---往奖金表插记录end
---往赠品表插记录begin
insert into pro_present(m_id,p_money,p_content,p_time)
select m_id,800,'您的子订单订单'+p_cid+'完成第'+@xone+'次循环',getdate()
from pro_rank where p_order>@gnum*(@i-1) and p_order<=@gnum*(@i-1)+@evnum
---往赠品表插记录end
-------------------------------------送赠品算法end
-------------------------------------排名算法begin
insert into pro_rank2
select m_id,p_id,p_cid,
case
when p_order>@gnum*(@i-1) and p_order<=@gnum*(@i-1)+@evnum then @pnum-@rnum+(p_order-@rnum*(@i-1))
when p_order>@gnum*(@i-1)+@evnum then p_order-@evnum*@i
end p_order,
case
when p_order>@gnum*(@i-1) and p_order<=@gnum*(@i-1)+@evnum then p_num+1
else p_num
end p_num,
p_time,
p_isexit from pro_rank where p_order<=@gnum*@i and p_order>@gnum*(@i-1)
-------------------------------------排名算法end
Set @i=@i+1
end
---while循环end
delete from pro_rank
insert into pro_rank
select m_id,p_id,p_cid,p_order,
p_num,
p_time,
p_isexit from pro_rank2
delete from pro_rank2
---------------------------------------------------