表一: | 表二:
student_ID student_name | course_ID student_ID select_Date
1 张三 | 1 1 2009-12-11
2 李四 | 2 1 2009-12-14
| 3 2 2009-12-11
| 4 2 2009-12-15
————————————————|————————————————————————————student_ID是表一得主键,是表二的外键
要求结果集中包含student_name和course_ID字段
表二中一个student_ID只选出select_Date最近的那个course_ID结果集应该是
student_name course_ID
张三 2
李四 4
student_ID student_name | course_ID student_ID select_Date
1 张三 | 1 1 2009-12-11
2 李四 | 2 1 2009-12-14
| 3 2 2009-12-11
| 4 2 2009-12-15
————————————————|————————————————————————————student_ID是表一得主键,是表二的外键
要求结果集中包含student_name和course_ID字段
表二中一个student_ID只选出select_Date最近的那个course_ID结果集应该是
student_name course_ID
张三 2
李四 4
join tb2 as b
on a.student_id=b.student_id
where not exists(select * from tb2
where b.student_id=student_id
and select_Date>b.select_Date)
from 表一 a inner join 表二 b on a.student_ID=b.student_ID
where not exists (select 1 from 表二 where student_ID=b.student_ID and select_Date>b.select_Date)
a.student_name,b.course_id
from
tb1 a
join tb2 b on
a.student_id=b.student_id
where
b.select_Date=(select max(select_Date) from tb2 where b.student_id=student_id)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-24 20:22:27
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([student_ID] int,[student_name] varchar(4))
insert [tb1]
select 1,'张三' union all
select 2,'李四'
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([course_ID] int,[student_ID] int,[select_Date] datetime)
insert [tb2]
select 1,1,'2009-12-11' union all
select 2,1,'2009-12-14' union all
select 3,2,'2009-12-11' union all
select 4,2,'2009-12-15'
--------------开始查询--------------------------
select
a.student_name,b.course_id
from
tb1 a
join tb2 b on
a.student_id=b.student_id
where
b.select_Date=(select max(select_Date) from tb2 where b.student_id=student_id)
----------------结果----------------------------
/* student_name course_id
------------ -----------
张三 2
李四 4(2 行受影响)
*/
2> go
student_ID |student_name
-----------|------------
1|张三
2|李四(2 rows affected)
1> select * from 表二
2> go
course_ID |student_ID |select_Date
-----------|-----------|--------------
1| 1|2009-12-11
2| 1|2009-12-14
3| 2|2009-12-11
4| 2|2009-12-15 (4 rows affected)
1> select a.student_name,b.course_ID
2> from 表一 a inner join 表二 b on a.student_ID=b.student_ID
3> where not exists (select 1 from 表二 where student_ID=b.student_ID and select_Date>b.select_Date)
4> go
student_name|course_ID
------------|-----------
张三 | 2
李四 | 4(2 rows affected)
1>
insert [tb1]
select 1,'张三' union all
select 2,'李四'
create table [tb2]([course_ID] int,[student_ID] int,[select_Date] datetime)
insert [tb2]
select 1,1,'2009-12-11' union all
select 2,1,'2009-12-14' union all
select 3,2,'2009-12-11' union all
select 4,2,'2009-12-15'--方法一
select m.student_name , n.course_ID
from tb1 m , tb2 n
where m.student_ID = n.student_ID and
n.select_Date = (select max(select_Date) from tb2 where student_ID = n.student_ID)
/*
student_name course_ID
------------ -----------
张三 2
李四 4(所影响的行数为 2 行)
*/--方法二
select m.student_name , n.course_ID
from tb1 m , tb2 n
where m.student_ID = n.student_ID and
not exists (select 1 from tb2 where student_ID = n.student_ID and select_Date > n.select_Date)
/*
student_name course_ID
------------ -----------
张三 2
李四 4(所影响的行数为 2 行)
*/drop table tb1 , tb2
insert @tb1
select 1,'张三' union all
select 2,'李四'
declare @tb2 table([course_ID] int,[student_ID] int,[select_Date] datetime)
insert @tb2
select 1,1,'2009-12-11' union all
select 2,1,'2009-12-14' union all
select 3,2,'2009-12-11' union all
select 4,2,'2009-12-15'select student_name , course_ID
from @tb1 t1 , @tb2 t2
where t1.student_ID = t2.student_ID and
t2.select_Date = (select max(select_Date) from @tb2 where student_ID = t2.student_ID)