求SQL語句

[Tosp2012]

現有表TA
BillNo BH_ID SortCode Order1
3 C2 1001
3 C2 1003
3 C2 1004
3 C2 1006
10 C5 1002
10 C5 1003
10 C5 1004
35 C2 1006
35 C2 1007
35 C2 1009
35 C2 1013
35 C2 1016

如何能生成這樣的結果
BillNo BH_ID SortCode Order1
3 C2 1001 1
3 C2 1003 2
3 C2 1004 3
3 C2 1006 4
10 C5 1002 1
10 C5 1003 2
10 C5 1004 3
35 C2 1006 1
35 C2 1007 2
35 C2 1009 3
35 C2 1013 4
35 C2 1016 5

即 Billno、BH_ID相等時，按Sortcode的順序加序號 每組的序號從1開始

[chuanzhang5687]

2005以上 用row_number()

[Tosp2012]

謝謝，我用的是SQL2000

求教。。。

[chuanzhang5687]

select BillNo ,BH_ID ,SortCode ,row_number() over (partiton by billNo,BH_ID order by BillNo asc) order1
from tb

[Tosp2012]

提示：
'row_number' is not a recognized function name.

SQL2000沒有這個函數吧。。。

[--小F--]

select  *, Order1=(select count(distinct BillNo)+1 from tb where BillNo=t.BillNo and SortCode<=t.SortCode) from tb t

[清海扬波]

select BillNo ,BH_ID ,SortCode ,(select count(1) from tb where BillNo=a.BillNo and SortCode<=a.SortCode)order1
from tb a

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-09-23 10:32:42
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([BillNo] int,[BH_ID] varchar(2),[SortCode] int,[Order1] sql_variant)
insert [tb]
select 3,'C2',1001,null union all
select 3,'C2',1003,null union all
select 3,'C2',1004,null union all
select 3,'C2',1006,null union all
select 10,'C5',1002,null union all
select 10,'C5',1003,null union all
select 10,'C5',1004,null union all
select 35,'C2',1006,null union all
select 35,'C2',1007,null union all
select 35,'C2',1009,null union all
select 35,'C2',1013,null union all
select 35,'C2',1016,null
--------------开始查询--------------------------
select  *, Order1=(select count(BillNo) from tb where BillNo=t.BillNo and BH_ID=t.BH_ID and SortCode<=t.SortCode) from tb t
----------------结果----------------------------
/* 
(12 行受影响)
BillNo      BH_ID SortCode    Order1                                                                                                                                                                                                                                                           Order1
----------- ----- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
3           C2    1001        NULL                                                                                                                                                                                                                                                             1
3           C2    1003        NULL                                                                                                                                                                                                                                                             2
3           C2    1004        NULL                                                                                                                                                                                                                                                             3
3           C2    1006        NULL                                                                                                                                                                                                                                                             4
10          C5    1002        NULL                                                                                                                                                                                                                                                             1
10          C5    1003        NULL                                                                                                                                                                                                                                                             2
10          C5    1004        NULL                                                                                                                                                                                                                                                             3
35          C2    1006        NULL                                                                                                                                                                                                                                                             1
35          C2    1007        NULL                                                                                                                                                                                                                                                             2
35          C2    1009        NULL                                                                                                                                                                                                                                                             3
35          C2    1013        NULL                                                                                                                                                                                                                                                             4
35          C2    1016        NULL                                                                                                                                                                                                                                                             5

(12 行受影响)


*/

[Tosp2012]

哇，先恭喜一下小F升磚了。。

[忠臣]

select BillNo ,BH_ID ,SortCode ,row_number() over (partiton by billNo,BH_ID order by BillNo asc) order1
from tb

[Tosp2012]

[Quote=引用 5 楼 fredrickhu 的回复:]
SQL code
select *, Order1=(select count(distinct BillNo)+1 from tb where BillNo=t.BillNo and SortCode<=t.SortCode) from tb t
[/Quote]
結果為：
BillNo BH_ID SortCode Order1
3 C2 1001 2
3 C2 1003 2
3 C2 1004 2
3 C2 1006 2
10 C5 1002 2
10 C5 1003 2
10 C5 1004 2
35 C2 1006 2
35 C2 1007 2
35 C2 1009 2
35 C2 1013 2
35 C2 1016 2

[Tosp2012]

[Quote=引用 9 楼 aijianzhishen 的回复:]
select BillNo ,BH_ID ,SortCode ,row_number() over (partiton by billNo,BH_ID order by BillNo asc) order1
from tb
[/Quote]
我用的是SQL2000 沒Row_Number 這個函數。

[chuanzhang5687]

select BillNo ,BH_ID ,SortCode,(select count(*)+1 from tb where [BillNo]=t1.[BillNo] and [SortCode]<t1.[SortCode]) as order1
from tb t1
/*
BillNo,BH_ID,SortCode,order1
3,C2,1001,1
3,C2,1003,2
3,C2,1004,3
3,C2,1006,4
10,C5,1002,1
10,C5,1003,2
10,C5,1004,3
35,C2,1006,1
35,C2,1007,2
35,C2,1009,3
35,C2,1013,4
35,C2,1016,5

(12 行受影响)

[Tosp2012]

[Quote=引用 6 楼 jackycontact 的回复:]
select BillNo ,BH_ID ,SortCode ,(select count(1) from tb where BillNo=a.BillNo and SortCode<=a.SortCode)order1
from tb a
[/Quote]
這個結果都變成了偶數了，如
BillNo BH_ID SortCode Order1
3 C2 1001 4
3 C2 1003 8
3 C2 1004 12
3 C2 1006 16
10 C5 1002 2
10 C5 1003 4
10 C5 1004 6
35 C2 1006 4
35 C2 1007 8
35 C2 1009 12
35 C2 1013 16
35 C2 1016 20

[chuanzhang5687]

--sql2005
select BillNo ,BH_ID ,SortCode ,row_number() over (partiton by billNo,BH_ID order by BillNo asc) order1
from tb
partiton

--sql2000

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([BillNo] int,[BH_ID] varchar(2),[SortCode] int,[Order1] sql_variant)
insert [tb]
select 3,'C2',1001,null union all
select 3,'C2',1003,null union all
select 3,'C2',1004,null union all
select 3,'C2',1006,null union all
select 10,'C5',1002,null union all
select 10,'C5',1003,null union all
select 10,'C5',1004,null union all
select 35,'C2',1006,null union all
select 35,'C2',1007,null union all
select 35,'C2',1009,null union all
select 35,'C2',1013,null union all
select 35,'C2',1016,null

select BillNo ,BH_ID ,SortCode,
(select count(*)+1 from tb where [BillNo]=t1.[BillNo] and [BH_ID] =t1.[BH_ID] and [SortCode]<t1.[SortCode]) as order1
from tb t1
/*
BillNo,BH_ID,SortCode,order1
3,C2,1001,1
3,C2,1003,2
3,C2,1004,3
3,C2,1006,4
10,C5,1002,1
10,C5,1003,2
10,C5,1004,3
35,C2,1006,1
35,C2,1007,2
35,C2,1009,3
35,C2,1013,4
35,C2,1016,5

(12 行受影响)

[chuanzhang5687]

-sql2005 修改一下
select BillNo ,BH_ID ,SortCode ,row_number() over (partition by billNo,BH_ID order by BillNo asc) order1
from tb

[Tosp2012]

[Quote=引用 12 楼 chuanzhang5687 的回复:]
SQL code
select BillNo ,BH_ID ,SortCode,(select count(*)+1 from tb where [BillNo]=t1.[BillNo] and [SortCode]<t1.[SortCode]) as order1
from tb t1
/*
BillNo,BH_ID,SortCode,order1
3,C2,1001,1
3,C2,……
[/Quote]
good Code
多謝了。

[Tosp2012]

多謝各位兄弟們。散分啦

[Tosp2012]

Sorry ,是結貼了。