调整当前表记录的序号

Question

基础数据日期      描述    排序
2010-1-1  周五    null
2010-1-4   周一    null
---------中间省略------
2010-1-29   周一    null
2010-2-1   周一    null
2010-2-2   周二    null
---------中间省略------
2010-2-26   周五    null
想要的结果日期      描述    排序
2010-1-1  周五    1
2010-1-4   周一    2
---------中间省略------
2010-1-29   周一    21
2010-2-1   周一    1
2010-2-2   周二    2
---------中间省略------
2010-2-26   周五    20也就是对基础数据按照每个月顺序从1累加并更新排序字段

Answer 1

[code=SQL]----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-25 19:59:00
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([日期] datetime,[描述] varchar(4),[排序] sql_variant)
insert #TB
select '2010-1-1','周五',null union all
select '2010-1-4','周一',null union all
select '2010-1-29','周一',null union all
select '2010-2-1','周一',null union all
select '2010-2-2','周二',null union all
select '2010-2-26','周五',null
--------------开始查询--------------------------select *,
(SELECT COUNT(*) FROM #TB 
WHERE CONVERT(VARCHAR(7),日期,120)=CONVERT(VARCHAR(7),T.日期,120) 
AND DAY(日期)<=DAY(T.日期))NUM
 from #TB T

Answer 2

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-25 19:59:00
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([日期] datetime,[描述] varchar(4),[排序] sql_variant)
insert #TB
select '2010-1-1','周五',null union all
select '2010-1-4','周一',null union all
select '2010-1-29','周一',null union all
select '2010-2-1','周一',null union all
select '2010-2-2','周二',null union all
select '2010-2-26','周五',null
--------------开始查询--------------------------select *,
(SELECT COUNT(*) FROM #TB 
WHERE CONVERT(VARCHAR(7),日期,120)=CONVERT(VARCHAR(7),T.日期,120) 
AND DAY(日期)<=DAY(T.日期))NUM
 from #TB T
----------------结果----------------------------
/* （所影响的行数为 6 行）日期                                                     描述   排序                                                                                                                                                                                                                                                               NUM         
------------------------------------------------------ ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 
2010-01-01 00:00:00.000                                周五   NULL                                                                                                                                                                                                                                                             1
2010-01-04 00:00:00.000                                周一   NULL                                                                                                                                                                                                                                                             2
2010-01-29 00:00:00.000                                周一   NULL                                                                                                                                                                                                                                                             3
2010-02-01 00:00:00.000                                周一   NULL                                                                                                                                                                                                                                                             1
2010-02-02 00:00:00.000                                周二   NULL                                                                                                                                                                                                                                                             2
2010-02-26 00:00:00.000                                周五   NULL                                                                                                                                                                                                                                                             3（所影响的行数为 6 行）
*/

Answer 3

你的表中没有周日吗 
--tryselect 日期,描述,序号=(
select count(*)+1 from tb where t.日期>日期 and datediffy(month,t.日期,日期)=0)
from tb t

Answer 4

--借用77的表
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([日期] datetime,[描述] varchar(4),[排序] sql_variant)
insert #TB
select '2010-1-1','周五',null union all
select '2010-1-4','周一',null union all
select '2010-1-29','周一',null union all
select '2010-2-1','周一',null union all
select '2010-2-2','周二',null union all
select '2010-2-26','周五',null
select 日期,描述,序号=(
select count(*)+1 from #TB where t.日期>日期 and datediff(month,t.日期,日期)=0)
from #TB t/*
日期                                                     描述   序号          
------------------------------------------------------ ---- ----------- 
2010-01-01 00:00:00.000                                周五   1
2010-01-04 00:00:00.000                                周一   2
2010-01-29 00:00:00.000                                周一   3
2010-02-01 00:00:00.000                                周一   1
2010-02-02 00:00:00.000                                周二   2
2010-02-26 00:00:00.000                                周五   3（所影响的行数为 6 行）
*/--如果想要updateupdate t set 排序=(
select count(*)+1 from #TB where t.日期>日期 and datediff(month,t.日期,日期)=0)
from #TB tselect * from #tb
日期                                                     描述   排序                                                                                                                                                                                                                                                               
------------------------------------------------------ ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
2010-01-01 00:00:00.000                                周五   1
2010-01-04 00:00:00.000                                周一   2
2010-01-29 00:00:00.000                                周一   3
2010-02-01 00:00:00.000                                周一   1
2010-02-02 00:00:00.000                                周二   2
2010-02-26 00:00:00.000                                周五   3（所影响的行数为 6 行）

Answer 5

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-01-25 20:04:16
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([日期] datetime,[描述] varchar(4),[排序] sql_variant)
insert [tb]
select '2010-1-1','周五',null union all
select '2010-1-4','周一',null union all
select '2010-1-29','周一',null union all
select '2010-2-1','周一',null union all
select '2010-2-2','周二',null union all
select '2010-2-26','周五',null
--------------开始查询--------------------------
update  
  a
set
  排序=b.排序1
from
  tb a,
  (select 排序1=row_number()over(partition by convert(varchar(7),日期,120) order by getdate()),* from tb )b
where 
  a.日期=b.日期
  
select * from tb
----------------结果----------------------------
/* 日期                      描述   排序
----------------------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2010-01-01 00:00:00.000 周五   1
2010-01-04 00:00:00.000 周一   2
2010-01-29 00:00:00.000 周一   3
2010-02-01 00:00:00.000 周一   1
2010-02-02 00:00:00.000 周二   2
2010-02-26 00:00:00.000 周五   3(6 行受影响)*/

Answer 6

1810 2010-01-29 00:00:00.000 1 friday 37
1809 2010-01-28 00:00:00.000 1 thursday 36
1808 2010-01-27 00:00:00.000 1 wednesday 35
1807 2010-01-26 00:00:00.000 1 tuesday 34
1806 2010-01-25 00:00:00.000 1 monday 33
1801 2010-01-22 00:00:00.000 1 friday 28
1800 2010-01-21 00:00:00.000 1 thursday 27
1799 2010-01-20 00:00:00.000 1 wednesday 26
1798 2010-01-19 00:00:00.000 1 tuesday 25
1797 2010-01-18 00:00:00.000 1 monday 24
1792 2010-01-15 00:00:00.000 1 friday 19
1791 2010-01-14 00:00:00.000 1 thursday 18
1790 2010-01-13 00:00:00.000 1 wednesday 17
1789 2010-01-12 00:00:00.000 1 tuesday 16
1788 2010-01-11 00:00:00.000 1 monday 15
1783 2010-01-08 00:00:00.000 1 friday 10
1782 2010-01-07 00:00:00.000 1 thursday 9
1781 2010-01-06 00:00:00.000 1 wednesday 8
1780 2010-01-05 00:00:00.000 1 tuesday 7
1779 2010-01-04 00:00:00.000 1 monday 6
1774 2010-01-01 00:00:00.000 1 friday 1
楼上的兄弟 更新后的结果怎么是这样啊?

Answer 7

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-25 20:25:48
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([C1] int,[C2] datetime,[C3] int,[C4] varchar(9),[C5] int)
insert #TB
select 1810,'2010-01-29 00:00:00.000',1,'friday',37 union all
select 1809,'2010-01-28 00:00:00.000',1,'thursday',36 union all
select 1808,'2010-01-27 00:00:00.000',1,'wednesday',35 union all
select 1807,'2010-01-26 00:00:00.000',1,'tuesday',34 union all
select 1806,'2010-01-25 00:00:00.000',1,'monday',33 union all
select 1801,'2010-01-22 00:00:00.000',1,'friday',28 union all
select 1800,'2010-01-21 00:00:00.000',1,'thursday',27 union all
select 1799,'2010-01-20 00:00:00.000',1,'wednesday',26 union all
select 1798,'2010-01-19 00:00:00.000',1,'tuesday',25 union all
select 1797,'2010-01-18 00:00:00.000',1,'monday',24 union all
select 1792,'2010-01-15 00:00:00.000',1,'friday',19 union all
select 1791,'2010-01-14 00:00:00.000',1,'thursday',18 union all
select 1790,'2010-01-13 00:00:00.000',1,'wednesday',17 union all
select 1789,'2010-01-12 00:00:00.000',1,'tuesday',16 union all
select 1788,'2010-01-11 00:00:00.000',1,'monday',15 union all
select 1783,'2010-01-08 00:00:00.000',1,'friday',10 union all
select 1782,'2010-01-07 00:00:00.000',1,'thursday',9 union all
select 1781,'2010-01-06 00:00:00.000',1,'wednesday',8 union all
select 1780,'2010-01-05 00:00:00.000',1,'tuesday',7 union all
select 1779,'2010-01-04 00:00:00.000',1,'monday',6 union all
select 1774,'2010-01-01 00:00:00.000',1,'friday',1
--------------开始查询--------------------------select *,
(SELECT COUNT(*) FROM #TB 
WHERE CONVERT(VARCHAR(7),C2,120)=CONVERT(VARCHAR(7),T.C2,120) 
AND DAY(C2)<=DAY(T.C2))NUM
 from #TB T ----------------结果----------------------------
/* （所影响的行数为 21 行）C1          C2                                                     C3          C4        C5          NUM         
----------- ------------------------------------------------------ ----------- --------- ----------- ----------- 
1810        2010-01-29 00:00:00.000                                1           friday    37          21
1809        2010-01-28 00:00:00.000                                1           thursday  36          20
1808        2010-01-27 00:00:00.000                                1           wednesday 35          19
1807        2010-01-26 00:00:00.000                                1           tuesday   34          18
1806        2010-01-25 00:00:00.000                                1           monday    33          17
1801        2010-01-22 00:00:00.000                                1           friday    28          16
1800        2010-01-21 00:00:00.000                                1           thursday  27          15
1799        2010-01-20 00:00:00.000                                1           wednesday 26          14
1798        2010-01-19 00:00:00.000                                1           tuesday   25          13
1797        2010-01-18 00:00:00.000                                1           monday    24          12
1792        2010-01-15 00:00:00.000                                1           friday    19          11
1791        2010-01-14 00:00:00.000                                1           thursday  18          10
1790        2010-01-13 00:00:00.000                                1           wednesday 17          9
1789        2010-01-12 00:00:00.000                                1           tuesday   16          8
1788        2010-01-11 00:00:00.000                                1           monday    15          7
1783        2010-01-08 00:00:00.000                                1           friday    10          6
1782        2010-01-07 00:00:00.000                                1           thursday  9           5
1781        2010-01-06 00:00:00.000                                1           wednesday 8           4
1780        2010-01-05 00:00:00.000                                1           tuesday   7           3
1779        2010-01-04 00:00:00.000                                1           monday    6           2
1774        2010-01-01 00:00:00.000                                1           friday    1           1（所影响的行数为 21 行）
*/

Answer 8

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-25 20:25:48
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([C1] int,[C2] datetime,[C3] int,[C4] varchar(9),[C5] int)
insert #TB
select 1810,'2010-01-29 00:00:00.000',1,'friday',37 union all
select 1809,'2010-01-28 00:00:00.000',1,'thursday',36 union all
select 1808,'2010-01-27 00:00:00.000',1,'wednesday',35 union all
select 1807,'2010-01-26 00:00:00.000',1,'tuesday',34 union all
select 1806,'2010-01-25 00:00:00.000',1,'monday',33 union all
select 1801,'2010-01-22 00:00:00.000',1,'friday',28 union all
select 1800,'2010-01-21 00:00:00.000',1,'thursday',27 union all
select 1799,'2010-01-20 00:00:00.000',1,'wednesday',26 union all
select 1798,'2010-01-19 00:00:00.000',1,'tuesday',25 union all
select 1797,'2010-01-18 00:00:00.000',1,'monday',24 union all
select 1792,'2010-01-15 00:00:00.000',1,'friday',19 union all
select 1791,'2010-01-14 00:00:00.000',1,'thursday',18 union all
select 1790,'2010-01-13 00:00:00.000',1,'wednesday',17 union all
select 1789,'2010-01-12 00:00:00.000',1,'tuesday',16 union all
select 1788,'2010-01-11 00:00:00.000',1,'monday',15 union all
select 1783,'2010-01-08 00:00:00.000',1,'friday',10 union all
select 1782,'2010-01-07 00:00:00.000',1,'thursday',9 union all
select 1781,'2010-01-06 00:00:00.000',1,'wednesday',8 union all
select 1780,'2010-01-05 00:00:00.000',1,'tuesday',7 union all
select 1779,'2010-01-04 00:00:00.000',1,'monday',6 union all
select 1774,'2010-01-01 00:00:00.000',1,'friday',1
--------------开始查询--------------------------UPDATE #TB SET C5=
(SELECT COUNT(*) FROM #TB 
WHERE CONVERT(VARCHAR(7),C2,120)=CONVERT(VARCHAR(7),T.C2,120) 
AND DAY(C2)<=DAY(T.C2))
 from #TB T SELECT * FROM #TB
----------------结果----------------------------
/* （所影响的行数为 21 行）
（所影响的行数为 21 行）C1          C2                                                     C3          C4        C5          
----------- ------------------------------------------------------ ----------- --------- ----------- 
1810        2010-01-29 00:00:00.000                                1           friday    21
1809        2010-01-28 00:00:00.000                                1           thursday  20
1808        2010-01-27 00:00:00.000                                1           wednesday 19
1807        2010-01-26 00:00:00.000                                1           tuesday   18
1806        2010-01-25 00:00:00.000                                1           monday    17
1801        2010-01-22 00:00:00.000                                1           friday    16
1800        2010-01-21 00:00:00.000                                1           thursday  15
1799        2010-01-20 00:00:00.000                                1           wednesday 14
1798        2010-01-19 00:00:00.000                                1           tuesday   13
1797        2010-01-18 00:00:00.000                                1           monday    12
1792        2010-01-15 00:00:00.000                                1           friday    11
1791        2010-01-14 00:00:00.000                                1           thursday  10
1790        2010-01-13 00:00:00.000                                1           wednesday 9
1789        2010-01-12 00:00:00.000                                1           tuesday   8
1788        2010-01-11 00:00:00.000                                1           monday    7
1783        2010-01-08 00:00:00.000                                1           friday    6
1782        2010-01-07 00:00:00.000                                1           thursday  5
1781        2010-01-06 00:00:00.000                                1           wednesday 4
1780        2010-01-05 00:00:00.000                                1           tuesday   3
1779        2010-01-04 00:00:00.000                                1           monday    2
1774        2010-01-01 00:00:00.000                                1           friday    1（所影响的行数为 21 行）*/