ID stuname
1 mike
2 john
3 cake
tab2
id stuid visitdate disc
1 1 2009-2-3 演示
2 2 2009-12-12 传真
3 2 2009-4-2 上门
4 3 2009-4-2 电话
5 1 2009-8-30 来访
6 2 2009-7-19 吃饭
7 3 2009-4-2 演示
我想要一SQL语名,查询出上两个表的数据是这个人的最后一次的visitdate,如果visitdate相同,则取最大ID的,如下所示:
ID stuname visitdate disc
1 mike 2009-8-30 来访
2 john 2009-12-12 传真
3 cake 2009-4-2 演示
请问这个SQL怎么写呀？
7 个解决方案
#1
--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(ID int, stuname varchar(8))
insert into #1
select 1, 'mike' union all
select 2, 'john' union all
select 3, 'cake'
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id int, stuid int, visitdate datetime, disc varchar(8))
insert into #2
select 1, 1, '2009-2-3', '演示' union all
select 2, 2, '2009-12-12', '传真' union all
select 3, 2, '2009-4-2', '上门' union all
select 4, 3, '2009-4-2', '电话' union all
select 5, 1, '2009-8-30', '来访' union all
select 6, 2, '2009-7-19', '吃饭' union all
select 7, 3, '2009-4-2', '演示'
select a.*, b.visitdate, b.disc from #1 a inner join #2 b on a.id = stuid
where b.id = (select max(id) from #2 c where stuid=b.stuid and not exists (select 1 from #2 where stuid=c.stuid and visitdate>c.visitdate))
/*
ID stuname visitdate disc
----------- -------- ----------------------- --------
1 mike 2009-08-30 00:00:00.000 来访
2 john 2009-12-12 00:00:00.000 传真
3 cake 2009-04-02 00:00:00.000 演示
*/
#2
这个太复杂了吧?有没有简单些的呀?
#3
用到not exists ,执行的效率会比较低呀.
#4
测测看吧
#5
你要找最大时间的并且可能不是唯一值，要么用 max(visitdate ) group by 得出一个子查询结果，再与原表联接；
要么就用not exists内联接，没第三种思路。
group by 的效率可能更低，自己测试一下，哪种方法快用哪种。
要么就用not exists内联接，没第三种思路。
group by 的效率可能更低，自己测试一下，哪种方法快用哪种。
#6
那全部改为连接看是否快一点
#7
try
SELECT A.*,B.*
FROM #1 A
LEFT JOIN (
SELECT aa.* FROM #2 aa INNER JOIN(
SELECT (SELECT MAX(ID) FROM #2 WHERE t.stuid=stuid AND MAX(t.visitdate)=visitdate) id FROM #2 t GROUP BY stuid
) bb ON(aa.ID=bb.ID)
) B ON(A.ID=B.stuid)
#1
--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(ID int, stuname varchar(8))
insert into #1
select 1, 'mike' union all
select 2, 'john' union all
select 3, 'cake'
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id int, stuid int, visitdate datetime, disc varchar(8))
insert into #2
select 1, 1, '2009-2-3', '演示' union all
select 2, 2, '2009-12-12', '传真' union all
select 3, 2, '2009-4-2', '上门' union all
select 4, 3, '2009-4-2', '电话' union all
select 5, 1, '2009-8-30', '来访' union all
select 6, 2, '2009-7-19', '吃饭' union all
select 7, 3, '2009-4-2', '演示'
select a.*, b.visitdate, b.disc from #1 a inner join #2 b on a.id = stuid
where b.id = (select max(id) from #2 c where stuid=b.stuid and not exists (select 1 from #2 where stuid=c.stuid and visitdate>c.visitdate))
/*
ID stuname visitdate disc
----------- -------- ----------------------- --------
1 mike 2009-08-30 00:00:00.000 来访
2 john 2009-12-12 00:00:00.000 传真
3 cake 2009-04-02 00:00:00.000 演示
*/
#2
这个太复杂了吧?有没有简单些的呀?
#3
用到not exists ,执行的效率会比较低呀.
#4
测测看吧
#5
你要找最大时间的并且可能不是唯一值，要么用 max(visitdate ) group by 得出一个子查询结果，再与原表联接；
要么就用not exists内联接，没第三种思路。
group by 的效率可能更低，自己测试一下，哪种方法快用哪种。
要么就用not exists内联接，没第三种思路。
group by 的效率可能更低，自己测试一下，哪种方法快用哪种。
#6
那全部改为连接看是否快一点
#7
try
SELECT A.*,B.*
FROM #1 A
LEFT JOIN (
SELECT aa.* FROM #2 aa INNER JOIN(
SELECT (SELECT MAX(ID) FROM #2 WHERE t.stuid=stuid AND MAX(t.visitdate)=visitdate) id FROM #2 t GROUP BY stuid
) bb ON(aa.ID=bb.ID)
) B ON(A.ID=B.stuid)