利用sql2005的新特性实现根据子表条件得到的主表键且按其排序取出对应主子表记录的方法...

 

假如有两个关联表，是一对多关系的主子表。如下：

主表

CREATE   TABLE   [ dbo ] . [ CourseT ] (
     [ CourseID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL ,
     [ CourseName ]   [ nchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS_WS  NULL
)  ON   [ PRIMARY ]

字表

CREATE   TABLE   [ dbo ] . [ Broad ] (
     [ BroadID ]   [ int ]   IDENTITY ( 1 , 1 )  NOT   NULL ,
     [ CourseID ]   [ int ]   NULL ,
     [ BroadName ]   [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS_WS  NULL
)  ON   [ PRIMARY ]

如果数据取自CourseT表，我们想查询Broad表中记录对应的CourseT表中的记录，且按CourseID的降序只取一次，在SQL2005中SQL如下

with   temp   as  
(
select   distinct  courseid  from  Broad 
),
 temp2  as
(
     select  courseid , ROW_NUMBER()  OVER ( ORDER   BY  courseid  desc )  AS  row_num
      from   temp  
)
select  CourseT. *   from   CourseT, temp2  where  courset.courseid = temp2.courseid  order   by  row_num

如果数据取自CourseT和Broad表，我们想查询Broad表中记录对应的CourseT表中的记录，且按CourseID的降序只取一次，我们可以如下写：

with   temp   as
(
   select  courseid, broadid, row_number()  over ( order   by  courseid  desc , broadid  desc   )  as  rownum  from  broad
),
temp2  as
(
select   temp .courseid,  temp .broadid  from   temp   where  rownum = 1
union
select   temp .courseid,  temp .broadid  from   temp ,  temp   as  temp0  where   temp .rownum  =  temp0.rownum + 1   and   temp .courseid  <>  temp0.courseid
)

select  CourseT. * , broad.broadid, broad.broadname  from   CourseT, broad, temp2
  where  courset.courseid = broad.courseid  and  temp2.courseid  =  courset.courseid  and  temp2.broadid  =  broad.broadid  order   by  
broad.courseid  desc  ,broad.broadid  desc

经牛人宪哥帮忙，又想出一个更好的方案如下：

select  CourseT.CourseID,CourseName,b.BroadID,b.BroadName
  from  CourseT,broad b,( select   max (broadid) bid,CourseID  from  broad  group   by  courseid ) t
   where
   CourseT.CourseID = b.CourseId  and  b.CourseID = t.courseid  and  b.broadid = t.bid

转载于:https://www.cnblogs.com/laiwen/archive/2006/12/30/607690.html