【！】求一SQL语句

Question

数据库是SQL 2000，
不是2005，也不是2008哈。A表，这3个字段都是主键。Job-No Mark-No Son-No
JP69 AW-1 A-1
JP69 AW-1 A-2
JP69 AW-1 A-3
JP69 AW-2 A-4
JP70 AW-1 A-3
JP71 AW-1 A-3B表，Job-N，Mark-No，Floor-No是主键。Job-No Mark-No Floor-No Count
JP69 AW-1 1F 1
JP69 AW-1 2F 3
JP69 AW-1 3F 2
JP69 AW-2 1F 1
JP70 AW-1 1F 3想得到，
Job-No Mark-No Son-No Floor-No Number
JP69 AW-1 A-1 1F 1
JP69 AW-1 A-1 2F 2
JP69 AW-1 A-1 2F 3
JP69 AW-1 A-1 2F 4
JP69 AW-1 A-1 3F 5
JP69 AW-1 A-1 3F 6
JP69 AW-1 A-2 1F 1
JP69 AW-1 A-2 2F 2
JP69 AW-1 A-2 2F 3
JP69 AW-1 A-2 2F 4
JP69 AW-1 A-2 3F 5
JP69 AW-1 A-2 3F 6
JP69 AW-1 A-3 1F 1
JP69 AW-1 A-3 2F 2
JP69 AW-1 A-3 2F 3
JP69 AW-1 A-3 2F 4
JP69 AW-1 A-3 3F 5
JP69 AW-1 A-3 3F 6
JP69 AW-2 A-4 1F 1
JP70 AW-1 A-3 1F 1
JP70 AW-1 A-3 1F 2
JP70 AW-1 A-3 1F 3

重点在Number上，Number的累加，是通过Job-No,Mark-No,Son-No为单位累加的。
请高手指点1

Answer 1

补充说明下哈，
Job-No Mark-No Son-No Floor-No Number
JP69 AW-1 A-1 1F 1
JP69 AW-1 A-1 2F 2
JP69 AW-1 A-1 2F 3
JP69 AW-1 A-1 2F 4
JP69 AW-1 A-1 3F 5
JP69 AW-1 A-1 3F 6之所以，2F有3个，这个3，是通过B表的COUNT字段来决定的。

Answer 2

create table ta(JobNo varchar(10),MarkNo varchar(10),SonNo varchar(10))
insert into ta
select 'JP69', 'AW-1', 'A-1' union all
select 'JP69', 'AW-1', 'A-2' union all
select 'JP69', 'AW-1', 'A-3' union all
select 'JP69', 'AW-2', 'A-4' union all
select 'JP70', 'AW-1', 'A-3' union all
select 'JP71', 'AW-1', 'A-3'
gocreate table tb(JobNo varchar(10),MarkNo varchar(10),FloorNo varchar(10),Cnt int)
insert into tb
select 'JP69', 'AW-1', '1F', 1 union all
select 'JP69', 'AW-1', '2F', 3 union all
select 'JP69', 'AW-1', '3F', 2 union all
select 'JP69', 'AW-2', '1F', 1 union all
select 'JP70', 'AW-1', '1F', 3
gowith t as
(
select distinct a.jobno,a.no,b.number
from (select jobno,no,sum(cnt)cnt from tb group by jobno,no)a,spt_values b
where 1 = 1 and b.number between 1 and a.cnt
)select t.jobno,t.no,c.sonno,t.number
from t left join ta c on t.jobno = c.jobno and t.no = c.no
group by t.jobno,t.no,c.sonno,t.numberdrop table ta,tbjobno      no     sonno      number
---------- ---------- ---------- -----------
JP69       AW-1       A-1        1
JP69       AW-1       A-1        2
JP69       AW-1       A-1        3
JP69       AW-1       A-1        4
JP69       AW-1       A-1        5
JP69       AW-1       A-1        6
JP69       AW-1       A-2        1
JP69       AW-1       A-2        2
JP69       AW-1       A-2        3
JP69       AW-1       A-2        4
JP69       AW-1       A-2        5
JP69       AW-1       A-2        6
JP69       AW-1       A-3        1
JP69       AW-1       A-3        2
JP69       AW-1       A-3        3
JP69       AW-1       A-3        4
JP69       AW-1       A-3        5
JP69       AW-1       A-3        6
JP69       AW-2       A-4        1
JP70       AW-1       A-3        1
JP70       AW-1       A-3        2
JP70       AW-1       A-3        3(22 行受影响)

Answer 3

--SQL2000需要用臨時輔助處理use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([Job-No] nvarchar(4),[Mark-No] nvarchar(4),[Son-No] nvarchar(3))
Insert #A
select N'JP69',N'AW-1',N'A-1' union all
select N'JP69',N'AW-1',N'A-2' union all
select N'JP69',N'AW-1',N'A-3' union all
select N'JP69',N'AW-2',N'A-4' union all
select N'JP70',N'AW-1',N'A-3' union all
select N'JP71',N'AW-1',N'A-3'
Go
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([Job-No] nvarchar(4),[Mark-No] nvarchar(4),[Floor-No] nvarchar(2),[Count] int)
Insert #B
select N'JP69',N'AW-1',N'1F',1 union all
select N'JP69',N'AW-1',N'2F',3 union all
select N'JP69',N'AW-1',N'3F',2 union all
select N'JP69',N'AW-2',N'1F',1 union all
select N'JP70',N'AW-1',N'1F',3
Go
IF OBJECT_ID('tempdb..#1') IS NOT NULL
DROP TABLE #1
SELECT TOP 100 row=IDENTITY(INT,1,1) INTO #1 FROM syscolumns AS a,syscolumns  AS bIF object_id('Tempdb..#2') IS NOT NULL
DROP TABLE #2
Select 
a.[Job-No],
a.[Mark-No],
a.[Son-No],
b.[Floor-No],
[Number]=c.row
INTO #2
from #A AS a
INNER JOIN #B AS b ON a.[Job-No]=b.[Job-No] AND a.[Mark-No]=b.[Mark-No]
INNER JOIN #1 AS c ON c.row<=b.[Count]
ORDER BY a.[Job-No],a.[Mark-No],a.[Son-No]SELECT 
a.[Job-No],
a.[Mark-No],
a.[Son-No],
a.[Floor-No],
[Number]=(SELECT COUNT(*) FROM #2 WHERE [Job-No]=a.[Job-No] AND [Mark-No]=a.[Mark-No] AND [Son-No]=a.[Son-No] AND([Floor-No]<a.[Floor-No] OR ([Floor-No]=a.[Floor-No] AND [Number]<=a.[Number])))
FROM #2 AS a
/*
Job-No Mark-No Son-No Floor-No Number
JP69 AW-1 A-1 1F 1
JP69 AW-1 A-1 2F 2
JP69 AW-1 A-1 2F 3
JP69 AW-1 A-1 2F 4
JP69 AW-1 A-1 3F 5
JP69 AW-1 A-1 3F 6
JP69 AW-1 A-2 1F 1
JP69 AW-1 A-2 2F 2
JP69 AW-1 A-2 2F 3
JP69 AW-1 A-2 2F 4
JP69 AW-1 A-2 3F 5
JP69 AW-1 A-2 3F 6
JP69 AW-1 A-3 1F 1
JP69 AW-1 A-3 2F 2
JP69 AW-1 A-3 2F 3
JP69 AW-1 A-3 2F 4
JP69 AW-1 A-3 3F 5
JP69 AW-1 A-3 3F 6
JP69 AW-2 A-4 1F 1
JP70 AW-1 A-3 1F 1
JP70 AW-1 A-3 1F 2
JP70 AW-1 A-3 1F 3
*/

Answer 4

--> 测试数据: #ta
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta ([Job-No] varchar(4),[Mark-No] varchar(4),[Son-No] varchar(3))
insert into #ta
select 'JP69','AW-1','A-1' union all
select 'JP69','AW-1','A-2' union all
select 'JP69','AW-1','A-3' union all
select 'JP69','AW-2','A-4' union all
select 'JP70','AW-1','A-3' union all
select 'JP71','AW-1','A-3'
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb ([Job-No] varchar(4),[Mark-No] varchar(4),[Floor-No] varchar(2),Count int)
insert into #tb
select 'JP69','AW-1','1F',1 union all
select 'JP69','AW-1','2F',3 union all
select 'JP69','AW-1','3F',2 union all
select 'JP69','AW-2','1F',1 union all
select 'JP70','AW-1','1F',3--sql 2005   
;with cte as 
(
select b.*,N=number from #tb b,master..spt_values s
where type='p' and number between 1 and b.Count  
)
select T.[Job-No],T.[Mark-No],A.[Son-No],[Floor-No],m=N+ISNULL((select SUM(distinct COUNT) FROM cte WHERE [Job-No]=T.[Job-No] AND  [Mark-No]=T.[Mark-No] AND [Floor-No]<T.[Floor-No]),0)
FROM cte T,#ta A
WHERE  A.[Job-No]=T.[Job-No] AND  A.[Mark-No]=T.[Mark-No]
ORDER BY  T.[Job-No],T.[Mark-No],A.[Son-No],[Floor-No],m 
--sql 2000
select b.*,N=number 
into #
from #tb b,master..spt_values s
where type='p' and number between 1 and b.Count  select T.[Job-No],T.[Mark-No],A.[Son-No],[Floor-No],m=N+ISNULL((select SUM(distinct COUNT) FROM # WHERE [Job-No]=T.[Job-No] AND  [Mark-No]=T.[Mark-No] AND [Floor-No]<T.[Floor-No]),0)
FROM # T,#ta A
WHERE  A.[Job-No]=T.[Job-No] AND  A.[Mark-No]=T.[Mark-No]
ORDER BY  T.[Job-No],T.[Mark-No],A.[Son-No],[Floor-No],m drop table #
--结果--Job-No Mark-No Son-No Floor-No m
-------- ------- ------ -------- -----------
--JP69   AW-1    A-1    1F       1
--JP69   AW-1    A-1    2F       2
--JP69   AW-1    A-1    2F       3
--JP69   AW-1    A-1    2F       4
--JP69   AW-1    A-1    3F       5
--JP69   AW-1    A-1    3F       6
--JP69   AW-1    A-2    1F       1
--JP69   AW-1    A-2    2F       2
--JP69   AW-1    A-2    2F       3
--JP69   AW-1    A-2    2F       4
--JP69   AW-1    A-2    3F       5
--JP69   AW-1    A-2    3F       6
--JP69   AW-1    A-3    1F       1
--JP69   AW-1    A-3    2F       2
--JP69   AW-1    A-3    2F       3
--JP69   AW-1    A-3    2F       4
--JP69   AW-1    A-3    3F       5
--JP69   AW-1    A-3    3F       6
--JP69   AW-2    A-4    1F       1
--JP70   AW-1    A-3    1F       1
--JP70   AW-1    A-3    1F       2
--JP70   AW-1    A-3    1F       3--(22 row(s) affected)

Answer 5

create table ta(JobNo varchar(10),MarkNo varchar(10),SonNo varchar(10))
insert into ta
select 'JP69', 'AW-1', 'A-1' union all
select 'JP69', 'AW-1', 'A-2' union all
select 'JP69', 'AW-1', 'A-3' union all
select 'JP69', 'AW-2', 'A-4' union all
select 'JP70', 'AW-1', 'A-3' union all
select 'JP71', 'AW-1', 'A-3'
gocreate table tb(JobNo varchar(10),MarkNo varchar(10),FloorNo varchar(10),Cnt int)
insert into tb
select 'JP69', 'AW-1', '1F', 1 union all
select 'JP69', 'AW-1', '2F', 3 union all
select 'JP69', 'AW-1', '3F', 2 union all
select 'JP69', 'AW-2', '1F', 1 union all
select 'JP70', 'AW-1', '1F', 3
gowith t as
(
    select distinct a.jobno,a.no,b.number
    from (select jobno,no,sum(cnt)cnt from tb group by jobno,no)a,spt_values b
    where 1 = 1 and b.number between 1 and a.cnt
)select t.jobno,t.no,c.sonno,t.number
from t left join ta c on t.jobno = c.jobno and t.no = c.no
group by t.jobno,t.no,c.sonno,t.numberdrop table ta,tbjobno      no     sonno      number
---------- ---------- ---------- -----------
JP69       AW-1       A-1        1
JP69       AW-1       A-1        2
JP69       AW-1       A-1        3
JP69       AW-1       A-1        4
JP69       AW-1       A-1        5
JP69       AW-1       A-1        6
JP69       AW-1       A-2        1
JP69       AW-1       A-2        2
JP69       AW-1       A-2        3
JP69       AW-1       A-2        4
JP69       AW-1       A-2        5
JP69       AW-1       A-2        6
JP69       AW-1       A-3        1
JP69       AW-1       A-3        2
JP69       AW-1       A-3        3
JP69       AW-1       A-3        4
JP69       AW-1       A-3        5
JP69       AW-1       A-3        6
JP69       AW-2       A-4        1
JP70       AW-1       A-3        1
JP70       AW-1       A-3        2
JP70       AW-1       A-3        3(22 行受影响)