表一
ID1 Name UpdateDate
1 a 2009-10-1 14:11:39.153
2 b 2009-10-2 14:11:39.153
3 c 2009-10-3 14:11:39.153
表二
ID2 ID1 UpdateDate
1 a 2009-10-25 14:11:39.153
2 a 2009-10-27 14:11:39.153
3 b 2009-10-30 14:11:39.153
4 c 2009-10-28 14:11:39.153
5 b 2009-10-24 14:11:39.153
6 a 2009-10-29 14:11:39.153要得到下面这样的结果,查询怎么写
ID1 Name UpdateDate
2 b 2009-10-30 14:11:39.153
1 a 2009-10-29 14:11:39.153
3 c 2009-10-28 14:11:39.153
ID1 Name UpdateDate
1 a 2009-10-1 14:11:39.153
2 b 2009-10-2 14:11:39.153
3 c 2009-10-3 14:11:39.153
表二
ID2 ID1 UpdateDate
1 a 2009-10-25 14:11:39.153
2 a 2009-10-27 14:11:39.153
3 b 2009-10-30 14:11:39.153
4 c 2009-10-28 14:11:39.153
5 b 2009-10-24 14:11:39.153
6 a 2009-10-29 14:11:39.153要得到下面这样的结果,查询怎么写
ID1 Name UpdateDate
2 b 2009-10-30 14:11:39.153
1 a 2009-10-29 14:11:39.153
3 c 2009-10-28 14:11:39.153
FROM A ,B T
WHERE A.NAME=B.ID1
AND
NOT EXISTS(SELECT 1 FROM B WHERE ID1=T.ID1 AND UpdateDate >T.UpdateDate )
(select a.id1,b.id1 as name,b.UpdateDate from 表一 a join 表二 b on a.Name=b.id1)t
order by UpdateDate desc
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-30 13:11:48
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[表一]
if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([ID1] int,[Name] varchar(1),[UpdateDate] datetime)
insert [表一]
select 1,'a','2009-10-1 14:11:39.153' union all
select 2,'b','2009-10-2 14:11:39.153' union all
select 3,'c','2009-10-3 14:11:39.153'
--> 测试数据:[表二]
if object_id('[表二]') is not null drop table [表二]
go
create table [表二]([ID2] int,[ID1] varchar(1),[UpdateDate] datetime)
insert [表二]
select 1,'a','2009-10-25 14:11:39.153' union all
select 2,'a','2009-10-27 14:11:39.153' union all
select 3,'b','2009-10-30 14:11:39.153' union all
select 4,'c','2009-10-28 14:11:39.153' union all
select 5,'b','2009-10-24 14:11:39.153' union all
select 6,'a','2009-10-29 14:11:39.153'
--------------开始查询--------------------------
select top 3 * from
(select a.id1,b.id1 as name,b.UpdateDate from 表一 a join 表二 b on a.Name=b.id1)t
order by UpdateDate desc
----------------结果----------------------------
/* id1 name UpdateDate
----------- ---- -----------------------
2 b 2009-10-30 14:11:39.153
1 a 2009-10-29 14:11:39.153
3 c 2009-10-28 14:11:39.153(3 行受影响)
*/
from a left
(select * from b as t where t.ID2=(select max(UpdateDate) from b where ID1=t.ID1)) b
ID1 UpdateDate
on a.ID1=b.ID1
FROM A ,B T
WHERE A.NAME=B.ID1
AND
NOT EXISTS(SELECT 1 FROM B WHERE ID1=T.ID1 AND UpdateDate >T.UpdateDate )
ORDER BY T.UPDATEDATE DESC
if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([ID1] int,[Name] varchar(1),[UpdateDate] datetime)
insert [表一]
select 1,'a','2009-10-1 14:11:39.153' union all
select 2,'b','2009-10-2 14:11:39.153' union all
select 3,'c','2009-10-3 14:11:39.153'
--> 测试数据:[表二]
if object_id('[表二]') is not null drop table [表二]
go
create table [表二]([ID2] int,[ID1] varchar(1),[UpdateDate] datetime)
insert [表二]
select 1,'a','2009-10-25 14:11:39.153' union all
select 2,'a','2009-10-27 14:11:39.153' union all
select 3,'b','2009-10-30 14:11:39.153' union all
select 4,'c','2009-10-28 14:11:39.153' union all
select 5,'b','2009-10-24 14:11:39.153' union all
select 6,'a','2009-10-29 14:11:39.153'
SELECT A.ID1,A.NAME,T.UPDATEDATE
FROM 表一 A ,表二 T
WHERE A.NAME=T.ID1
AND
NOT EXISTS(SELECT 1 FROM 表二 WHERE ID1=T.ID1 AND UpdateDate >T.UpdateDate )
ORDER BY T.UPDATEDATE DESC
(所影响的行数为 3 行)
(所影响的行数为 6 行)ID1 NAME UPDATEDATE
----------- ---- ------------------------------------------------------
2 b 2009-10-30 14:11:39.153
1 a 2009-10-29 14:11:39.153
3 c 2009-10-28 14:11:39.153(所影响的行数为 3 行)
借小F数据
-----有很多方法可以得到这样的结果,但最好说明你的取数规则
-----因为当你的表数据量很多时,很多方法得到的结果就有可能不一样了!
-----楼主是表二用ID2、ID1分组最时间最大值再与表一联接呢?还是其他的取数规则?
-----我觉得:先说清楚你的取数规则,比其不同方法的取数,在小量数据下一致更重要!!!!
declare @表一 table ([ID1] int,[Name] varchar(1),[UpdateDate] datetime)
insert into @表一
select 1,'a','2009-10-1 14:11:39.153' union all
select 2,'b','2009-10-2 14:11:39.153' union all
select 3,'c','2009-10-3 14:11:39.153'
--> Test data : @表二
declare @表二 table ([ID2] int,[ID1] varchar(1),[UpdateDate] datetime)
insert into @表二
select 1,'a','2009-10-25 14:11:39.153' union all
select 2,'a','2009-10-27 14:11:39.153' union all
select 3,'b','2009-10-30 14:11:39.153' union all
select 4,'c','2009-10-28 14:11:39.153' union all
select 5,'b','2009-10-24 14:11:39.153' union all
select 6,'a','2009-10-29 14:11:39.153'select
t1.id1
,t1.name
,(select top 1 [UpdateDate]
from @表二 b2
where b2.id1 = t1.name
order by [UpdateDate] desc
) [UpdateDate]
from @表一 t1
order by [UpdateDate] desc--或者select
t1.id1
,t1.name
,t2.UpdateDate
from @表一 t1
,(select id1,max(UpdateDate) UpdateDate from @表二 group by id1) t2
where t1.name = t2.id1
order by t2.UpdateDate desc
id1 name UpdateDate
----------- ---- -----------------------
2 b 2009-10-30 14:11:39.153
1 a 2009-10-29 14:11:39.153
3 c 2009-10-28 14:11:39.153
表一
Companyid CompanyName UpdateDate
1 公司一 2009-10-1 14:11:39.153
2 公司二 2009-10-2 14:11:39.153
3 公司三 2009-10-3 14:11:39.153
表二
ID Companyid UpdateDate
1 1 2009-10-25 14:11:39.153
2 1 2009-10-27 14:11:39.153
3 2 2009-10-30 14:11:39.153
4 3 2009-10-28 14:11:39.153
5 2 2009-10-24 14:11:39.153
6 1 2009-10-29 14:11:39.153要得到下面这样的结果
Comid CompanyName UpdateDate
2 公司二 2009-10-30 14:11:39.153
1 公司一 2009-10-29 14:11:39.153
3 公司三 2009-10-28 14:11:39.153表一记录公司信息,Comid公司编号,CompanyName公司名称,UpdateDate公司信息更新时间
表二记录公司业务操作,ID业务操作编号,Companyid公司编号,UpdateDate业务操作时间
表一表二的UpdateDate无联系查询结果是要查看各公司最新的业务操作时间
if object_id('[表一]') is not null drop table [表一]
go
create table [表一](CompanyId int,CompanyName varchar(10),UpdateDate datetime)
insert [表一]
select 1,'公司一','2009-10-1 14:11:39.153' union all
select 2,'公司二','2009-10-2 14:11:39.153' union all
select 3,'公司三','2009-10-3 14:11:39.153'
--> 测试数据:[表二]
if object_id('[表二]') is not null drop table [表二]
go
create table [表二](ID int,CompanyId int,UpdateDate datetime)
insert [表二]
select 1,1,'2009-10-25 14:11:39.153' union all
select 2,1,'2009-10-27 14:11:39.153' union all
select 3,2,'2009-10-30 14:11:39.153' union all
select 4,3,'2009-10-28 14:11:39.153' union all
select 5,2,'2009-10-24 14:11:39.153' union all
select 6,1,'2009-10-29 14:11:39.153'
--------------开始查询--------------------------
--select a.id1,b.id1 as name,b.UpdateDate from 表一 a join 表二 b on a.Name=b.id1select t1.Companyid Comid, t1.CompanyName, t2.UpdateDate
from 表一 t1 left join (select Companyid, max(UpdateDate) UpdateDate from 表二 group by Companyid ) t2
on t1.CompanyId=t2.CompanyId
order by t2.UpdateDate desc;