create table Company(ID int, CompanyName varchar(32)) create table Job(ID int identity(1,1), CompanyID int, JobDescription varchar(32))insert Company select 1, 'CSDN' union all select 2, 'Baidu' union all select 3, 'Yahoo' union all select 4, 'IBM' union all select 5, 'Google'insert Job select 1, 'Guset' union all select 1, 'VIP' union all select 2, 'Member' union all select 2, 'Employee' union all select 3, 'Programmer' union all select 3, 'Manager' union all select 4, 'Top Coder' union all select 5, 'Programmer' union all select 5, 'Manager' union all select 5, 'Leader'--取前3个公司,每个公司最多2个职位 --SQL2005 select C.ID, CompanyName, J.ID, JobDescription from (select top 3 * from Company order by ID desc) C cross apply (select top 2 * from Job where CompanyID=C.ID) AS J /* ID CompanyName ID JobDescription ----------- -------------------------------- ----------- -------------------------------- 5 Google 8 Programmer 5 Google 9 Manager 4 IBM 7 Top Coder 3 Yahoo 5 Programmer 3 Yahoo 6 Manager(5 行受影响) */drop table Company, Job
好像不对
'apply' 附近有语法错误。
在关键字 'as' 附近有语法错误。
create table Job(ID int identity(1,1), CompanyID int, JobDescription varchar(32))insert Company select 1, 'CSDN'
union all select 2, 'Baidu'
union all select 3, 'Yahoo'
union all select 4, 'IBM'
union all select 5, 'Google'insert Job select 1, 'Guset'
union all select 1, 'VIP'
union all select 2, 'Member'
union all select 2, 'Employee'
union all select 3, 'Programmer'
union all select 3, 'Manager'
union all select 4, 'Top Coder'
union all select 5, 'Programmer'
union all select 5, 'Manager'
union all select 5, 'Leader'--取前3个公司,每个公司最多2个职位
--SQL2005
select C.ID, CompanyName, J.ID, JobDescription
from (select top 3 * from Company order by ID desc) C
cross apply (select top 2 * from Job where CompanyID=C.ID) AS J
/*
ID CompanyName ID JobDescription
----------- -------------------------------- ----------- --------------------------------
5 Google 8 Programmer
5 Google 9 Manager
4 IBM 7 Top Coder
3 Yahoo 5 Programmer
3 Yahoo 6 Manager(5 行受影响)
*/drop table Company, Job