两表内联查询后,如何查重复记录里最新的

weasea 2010-04-09 02:22:51
tba
ID SN tba_date
1 1 2009-1-1
2 1 2009-1-2
3 1 2009-1-3
4 1 2009-1-4

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

select * from tba inner join tbb on (tba.ID=tbb.ID and process=p1)
这样有两条记录
但是我想查的是2000-1-2 这一条而已
谢谢
...全文
210 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
weasea 2010-04-12
  • 打赏
  • 举报
回复
我这个ID还是guid,楼上的兄弟方法不知道可行吗?
ycj80 2010-04-12
  • 打赏
  • 举报
回复

-- 借各位數據
--> 测试数据:[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)
*/
张自强153 2010-04-11
  • 打赏
  • 举报
回复
drop table #ls1
go
drop table #ls2
go

select 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 process

select a.*
from #ls1 a,#ls2 b
where a.process=b.process and a.tbb_date=b.tbb_date

weasea 2010-04-09
  • 打赏
  • 举报
回复
还是错的。。。
zhangjiang264 2010-04-09
  • 打赏
  • 举报
回复
select max(tba.id) id,max([sn])[sn],'p1' Process,max(tbb.[tbb_date])tbb_date
from tba inner join tbb on
tba.id=tbb.id
where tbb.process='p1'
group by tba.[SN]
?
weasea 2010-04-09
  • 打赏
  • 举报
回复
ID还是得不到。。。。。。。
zhangjiang264 2010-04-09
  • 打赏
  • 举报
回复

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]
weasea 2010-04-09
  • 打赏
  • 举报
回复
如果我需要得到那个ID呢?
feixianxxx 2010-04-09
  • 打赏
  • 举报
回复
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*/
weasea 2010-04-09
  • 打赏
  • 举报
回复
谢谢。。。下次注意
老黎 2010-04-09
  • 打赏
  • 举报
回复
建议楼主把问题描述清楚
你的create语句、insert语句,全面的测试数据,在测试数据基础上得出的正确结果、简单的计算规则描述
这样大家都很省事
weasea 2010-04-09
  • 打赏
  • 举报
回复
tba
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会有很多
谢谢啊

--小F-- 2010-04-09
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 weasea 的回复:]
b.tbb_date=(select max(tbb_date) from tbb where Process=b.Process)
这个应该不对吧。
[/Quote]

----------------------------------------------------------------
-- 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 行受影响)

*/
weasea 2010-04-09
  • 打赏
  • 举报
回复
up......
weasea 2010-04-09
  • 打赏
  • 举报
回复
但是我想求最新的 相同的SN,process=p1 的数据啊。
qq234121906 2010-04-09
  • 打赏
  • 举报
回复
你这条语句的意思是符合条件:tba.id=tbb.id 和 process=p1的
现在process=p1的有两条,肯定就会有两条啊。你可以把你要表达的意思拿出来
weasea 2010-04-09
  • 打赏
  • 举报
回复
b.tbb_date=(select max(tbb_date) from tbb where Process=b.Process)
这个应该不对吧。
--小F-- 2010-04-09
  • 打赏
  • 举报
回复
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)
--小F-- 2010-04-09
  • 打赏
  • 举报
回复
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
永生天地 2010-04-09
  • 打赏
  • 举报
回复
符合条件的不唯一,不行
加载更多回复(1)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧