34,575
社区成员
发帖
与我相关
我的任务
分享
-- 借各位數據
--> 测试数据:[tba]
if object_id('[tba]') is not null drop table [tba]
go
create table [tba]([ID] int,[SN] int,[tba_date] datetime)
insert [tba]
select 1,1,'2009-1-1' union all
select 2,1,'2009-1-2' union all
select 3,1,'2009-1-3' union all
select 4,1,'2009-1-4'
--> 测试数据:[tbb]
if object_id('[tbb]') is not null drop table [tbb]
go
create table [tbb]([ID] int,[Process] varchar(2),[tbb_date] datetime)
insert [tbb]
select 1,'p1','2009-1-1' union all
select 2,'p1','2009-1-2' union all
select 3,'p2','2009-1-3' union all
select 4,'p3','2009-1-4'
--select * from tba
--select * from tbb
select a.id,a.sn,b.process,a.tba_date
from tba a join (select max(id) as id,process from tbb group by process ) b on a.id=b.id
-- where b.process= '??' --這裡加上條件
/*
id,sn,process,tba_date
-- -- -- --
2,1,p1,2009-01-02 00:00:00.000
3,1,p2,2009-01-03 00:00:00.000
4,1,p3,2009-01-04 00:00:00.000
(3 row(s) affected)
*/
if object_id('[tba]') is not null drop table [tba]
go
create table [tba]([ID] int,[SN] int,[tba_date] datetime)
insert [tba]
select 1,1,'2009-1-1' union all
select 2,1,'2009-1-2' union all
select 3,1,'2009-1-3' union all
select 4,1,'2009-1-4' union all
select 5,2,'2009-1-5'
--> 测试数据:[tbb]
if object_id('[tbb]') is not null drop table [tbb]
go
create table [tbb]([ID] int,[Process] varchar(2),[tbb_date] datetime)
insert [tbb]
select 1,'p1','2009-1-1' union all
select 2,'p1','2009-1-2' union all
select 3,'p2','2009-1-3' union all
select 4,'p3','2009-1-4' union all
select 5,'p1','2009-1-5'
go
select max([sn]),'p1' Process,max(tbb.[tbb_date])
from tba inner join tbb on
tba.id=tbb.id
where tbb.process='p1'
group by tba.[SN]
if object_id('[tba]') is not null drop table [tba]
go
create table [tba]([ID] int,[SN] int,[tba_date] datetime)
insert [tba]
select 1,1,'2009-1-1' union all
select 2,1,'2009-1-2' union all
select 3,1,'2009-1-3' union all
select 4,1,'2009-1-4' union all
select 5,2,'2009-1-5'
--> 测试数据:[tbb]
if object_id('[tbb]') is not null drop table [tbb]
go
create table [tbb]([ID] int,[Process] varchar(2),[tbb_date] datetime)
insert [tbb]
select 1,'p1','2009-1-1' union all
select 2,'p1','2009-1-2' union all
select 3,'p2','2009-1-3' union all
select 4,'p3','2009-1-4' union all
select 5,'p1','2009-1-5'
go
select a.SN,Process,MAX(tbb_date) as tbb_date
from tba a join tbb b on a.tba_date=b.tbb_date
where Process='p1'
group by a.SN,Process
/*
SN Process tbb_date
----------- ------- -----------------------
1 p1 2009-01-02 00:00:00.000
2 p1 2009-01-05 00:00:00.000*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-09 15:00:12
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tba]
if object_id('[tba]') is not null drop table [tba]
go
create table [tba]([ID] int,[SN] int,[tba_date] datetime)
insert [tba]
select 1,1,'2009-1-1' union all
select 2,1,'2009-1-2' union all
select 3,1,'2009-1-3' union all
select 4,1,'2009-1-4'
--> 测试数据:[tbb]
if object_id('[tbb]') is not null drop table [tbb]
go
create table [tbb]([ID] int,[Process] varchar(2),[tbb_date] datetime)
insert [tbb]
select 1,'p1','2009-1-1' union all
select 2,'p1','2009-1-2' union all
select 3,'p2','2009-1-3' union all
select 4,'p3','2009-1-4'
--------------开始查询--------------------------
select
*
from
tba a
inner join
tbb b
on
a.id=b.id
and
b.process='p1'
and
b.tbb_date=(select max(tbb_date) from tbb where Process=b.Process)
----------------结果----------------------------
/* ID SN tba_date ID Process tbb_date
----------- ----------- ----------------------- ----------- ------- -----------------------
2 1 2009-01-02 00:00:00.000 2 p1 2009-01-02 00:00:00.000
(1 行受影响)
*/
select
*
from
tba a
inner join
tbb b
on
a.id=b.id
and
b.process=p1
and
b.tbb_date=(select max(tbb_date) from tbb where Process=b.Process)
select
*
from
tba a
inner join
tbb b
on
a.id=b.id
and
b.process=p1
and
b.tbb_date=(select max(tbb_date) from tbb where Process=b.Process)a