有一表A
CODE
11
22
33
44
.....
表B
CODE1 CODE2 TRAN_QTY, DATE, NAME....
22 33 10 2005/6/1 CC
22 44 25 2005/6/1 BB
11 44 37 2005/6/2 MM
11 44 25 2005/6/2 MM
11 44 79 2006/6/2 MM
11 44 80 2006/5/2 MM
22 44 25 2005/6/1 BB
22 11 25 2005/6/1 BB
22 33 25 2005/6/1 BB
22 33 25 2005/6/1 BB
.................我要的結果是﹕找出A表中的CODE,(與B表中code1或者CODE2相同值的其中五條記錄,不足五條則全部顯示﹐否則只顯示五條)
注﹕因為B表有很多數據﹐我只想取出A表中每個值的五條記錄就好了。。謝謝﹐在線等﹗
CODE
11
22
33
44
.....
表B
CODE1 CODE2 TRAN_QTY, DATE, NAME....
22 33 10 2005/6/1 CC
22 44 25 2005/6/1 BB
11 44 37 2005/6/2 MM
11 44 25 2005/6/2 MM
11 44 79 2006/6/2 MM
11 44 80 2006/5/2 MM
22 44 25 2005/6/1 BB
22 11 25 2005/6/1 BB
22 33 25 2005/6/1 BB
22 33 25 2005/6/1 BB
.................我要的結果是﹕找出A表中的CODE,(與B表中code1或者CODE2相同值的其中五條記錄,不足五條則全部顯示﹐否則只顯示五條)
注﹕因為B表有很多數據﹐我只想取出A表中每個值的五條記錄就好了。。謝謝﹐在線等﹗
create table test(stuid int,subjectid varchar(10), int)
insert test select 1,'001',98
union all select 1,'002',20
union all select 1,'003',65
union all select 2,'001',79
union all select 2,'002',85
union all select 2,'003',95
union all select 3,'001',69
union all select 3,'002',26
union all select 3,'003',49
union all select 4,'001',91
union all select 4,'002',68
union all select 4,'003',61
select * from test
select * from test t where(select count(1) from test where subjectid=t.subjectid and >=t.)<=2 order by subjectid, desc
select top 5 * from a inner join b on a.code=b.code1
union
select top 5 * from a inner join b on a.code=b.code2
)
表A中的每個值對應表B的五條記錄﹐其實就是抽一些樣版數據﹐
表A中有很多個數據啊﹐
A.CODE,
B.*
From A
Inner Join B
On A.CODE=B.CODE1
Or A.CODE=B.CODE2
Where (Select Count(*) From B C Where (CODE1=A.CODE Or CODE2=A.CODE) And ID<B.ID)<5
(CODE Int)
Insert A Select 11
Union All Select 22
Union All Select 33
Union All Select 44
Create Table B
(ID Int,
CODE1 Int,
CODE2 Int,
TRAN_QTY Int,
[DATE] Varchar(10),
NAME Varchar(10))
Insert B Select 1,22, 33, 10, '2005/6/1', 'CC'
Union All Select 2,22, 44, 25, '2005/6/1', 'BB'
Union All Select 3,11, 44, 37, '2005/6/2', 'MM'
Union All Select 4,11, 44, 25, '2005/6/2', 'MM'
Union All Select 5,11, 44, 79, '2006/6/2', 'MM'
Union All Select 6,11, 44, 80, '2006/5/2', 'MM'
Union All Select 7,22, 44, 25, '2005/6/1', 'BB'
Union All Select 8,22, 11, 25, '2005/6/1', 'BB'
Union All Select 9,22, 33, 25, '2005/6/1', 'BB'
Union All Select 10,22, 33, 25, '2005/6/1', 'BB'
GO
Select
A.CODE,
B.*
From A
Inner Join B
On A.CODE=B.CODE1
Or A.CODE=B.CODE2
Where (Select Count(*) From B C Where (CODE1=A.CODE Or CODE2=A.CODE) And ID<B.ID)<5
GO
Drop Table A,B
--Result
/*
CODE ID CODE1 CODE2 TRAN_QTY DATE NAME
11 3 11 44 37 2005/6/2 MM
11 4 11 44 25 2005/6/2 MM
11 5 11 44 79 2006/6/2 MM
11 6 11 44 80 2006/5/2 MM
11 8 22 11 25 2005/6/1 BB
22 1 22 33 10 2005/6/1 CC
22 2 22 44 25 2005/6/1 BB
22 7 22 44 25 2005/6/1 BB
22 8 22 11 25 2005/6/1 BB
22 9 22 33 25 2005/6/1 BB
33 1 22 33 10 2005/6/1 CC
33 9 22 33 25 2005/6/1 BB
33 10 22 33 25 2005/6/1 BB
44 2 22 44 25 2005/6/1 BB
44 3 11 44 37 2005/6/2 MM
44 4 11 44 25 2005/6/2 MM
44 5 11 44 79 2006/6/2 MM
44 6 11 44 80 2006/5/2 MM
*/
表A是主表,
表B是子表,
不过偶表B子表是有单主键列
那么快
汗,偶的主键是uniqueidentifier类型,不能 ID<B.ID
你的SQL沒問題﹐但是我B表的數據真是大啊﹐一個小時都沒有運行出來﹐不知道怎么才能快點
Select
A.CODE,
B.*
From A
Inner Join B
On A.CODE=B.CODE1
Or A.CODE=B.CODE2
Where (Select Count(ID) From B C Where (CODE1=A.CODE Or CODE2=A.CODE) And ID<B.ID)<5
A.CODE,
B.*
From A
Inner Join B
On A.CODE=B.CODE1
Or A.CODE=B.CODE2
Where (Select Count(*) From B C Where (CODE1=A.CODE Or CODE2=A.CODE) And ID<B.ID)<5