tba
ID SN tba_date
1 1 2009-1-1
2 1 2009-1-2
3 1 2009-1-3
4 1 2009-1-4tbb
ID Process tbb_date
1 p1 2009-1-1
2 p1 2009-1-2
3 p2 2009-1-3
4 p3 2009-1-4select * from tba inner join tbb on (tba.ID=tbb.ID and process=p1)
这样有两条记录
但是我想查的是2000-1-2 这一条而已
谢谢
ID SN tba_date
1 1 2009-1-1
2 1 2009-1-2
3 1 2009-1-3
4 1 2009-1-4tbb
ID Process tbb_date
1 p1 2009-1-1
2 p1 2009-1-2
3 p2 2009-1-3
4 p3 2009-1-4select * from tba inner join tbb on (tba.ID=tbb.ID and process=p1)
这样有两条记录
但是我想查的是2000-1-2 这一条而已
谢谢
*
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
*
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)
这个应该不对吧。
现在process=p1的有两条,肯定就会有两条啊。你可以把你要表达的意思拿出来
----------------------------------------------------------------
-- 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 行受影响)*/
ID SN tba_date
1 1 2009-1-1
2 1 2009-1-2
3 1 2009-1-3
4 1 2009-1-4
5 2 2009-1-5
tbb
ID Process tbb_date
1 p1 2009-1-1
2 p1 2009-1-2
3 p2 2009-1-3
4 p3 2009-1-4
5 p1 2009-1-5根据process 是p1 最新的记录
SN process tbb_date
1 p1 2009-1-2
2 p1 2009-1-5
ls的老大,你的查询语句对一个SN有效。但是SN会有很多
谢谢啊
你的create语句、insert语句,全面的测试数据,在测试数据基础上得出的正确结果、简单的计算规则描述
这样大家都很省事
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*/
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'
goselect 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]
from tba inner join tbb on
tba.id=tbb.id
where tbb.process='p1'
group by tba.[SN]
?
go
drop table #ls2
goselect tba.id,tba.sn,tba.tba_date,tbb.process,tbb.tbb_date
into #ls1
from tba inner join tbb
on (tba.ID=tbb.ID and process=p1)slect process,max(tbb_date) tbb_date
into #ls2
from #ls1 group by processselect a.*
from #ls1 a,#ls2 b
where a.process=b.process and a.tbb_date=b.tbb_date
-- 借各位數據
--> 测试数据:[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)
*/