试试这个:
create table t1( FID int, TID int, NAME varchar(20))insert into t1
select 1 ,1 ,'name11' union all
select 1 ,2 ,'name12' union all
select 2 ,1 ,'name21' union all
select 2 ,2 ,'name22' union all
select 3 ,1 ,'name31' union all
select 3 ,2 ,'name32' create table t2(FID int, TID int, TM datetime, DATA int)insert into t2
select 1 ,1 ,'2014-01-01 12:00', 30 union all
select 1 ,2 ,'2014-01-01 12:00', 30 union all
select 2 ,1 ,'2014-01-01 12:01', 30 union all
select 2 ,2 ,'2014-01-01 12:02', 30 union all
select 3 ,1 ,'2014-01-01 12:03', 30 union all
select 3 ,2 ,'2014-01-01 12:04', 30 union all
select 1 ,1 ,'2014-01-02 12:00', 30 union all
select 1 ,2 ,'2014-01-02 12:00', 30 union all
select 2 ,1 ,'2014-01-02 12:01', 30 union all
select 2 ,2 ,'2014-01-02 12:02', 30 union all
select 3 ,1 ,'2014-01-02 12:03', 30 union all
select 3 ,2 ,'2014-01-02 12:04', 30 union all
select 1 ,1 ,'2014-01-02 13:00', 30 union all
select 1 ,2 ,'2014-01-02 13:00', 30 union all
select 2 ,1 ,'2014-01-02 13:01', 30 union all
select 2 ,2 ,'2014-01-02 13:02', 30 union all
select 3 ,2 ,'2014-01-02 13:04', 30
go
select t1.FID,t1.TID,t1.NAME,t2.TM ,t2.DATA
from t1
inner join
(
select *,
ROW_NUMBER() over(partition by fid,tid order by TM) rownum
from t2
)t2
on t2.FID = t1.FID and t2.TID = t1.TID and t2.rownum = 1
/*
FID TID NAME TM DATA
1 1 name11 2014-01-01 12:00:00.000 30
1 2 name12 2014-01-01 12:00:00.000 30
2 1 name21 2014-01-01 12:01:00.000 30
2 2 name22 2014-01-01 12:02:00.000 30
3 1 name31 2014-01-01 12:03:00.000 30
3 2 name32 2014-01-01 12:04:00.000 30
*/
create table t1( FID int, TID int, NAME varchar(20))insert into t1
select 1 ,1 ,'name11' union all
select 1 ,2 ,'name12' union all
select 2 ,1 ,'name21' union all
select 2 ,2 ,'name22' union all
select 3 ,1 ,'name31' union all
select 3 ,2 ,'name32' create table t2(FID int, TID int, TM datetime, DATA int)insert into t2
select 1 ,1 ,'2014-01-01 12:00', 30 union all
select 1 ,2 ,'2014-01-01 12:00', 30 union all
select 2 ,1 ,'2014-01-01 12:01', 30 union all
select 2 ,2 ,'2014-01-01 12:02', 30 union all
select 3 ,1 ,'2014-01-01 12:03', 30 union all
select 3 ,2 ,'2014-01-01 12:04', 30 union all
select 1 ,1 ,'2014-01-02 12:00', 30 union all
select 1 ,2 ,'2014-01-02 12:00', 30 union all
select 2 ,1 ,'2014-01-02 12:01', 30 union all
select 2 ,2 ,'2014-01-02 12:02', 30 union all
select 3 ,1 ,'2014-01-02 12:03', 30 union all
select 3 ,2 ,'2014-01-02 12:04', 30 union all
select 1 ,1 ,'2014-01-02 13:00', 30 union all
select 1 ,2 ,'2014-01-02 13:00', 30 union all
select 2 ,1 ,'2014-01-02 13:01', 30 union all
select 2 ,2 ,'2014-01-02 13:02', 30 union all
select 3 ,2 ,'2014-01-02 13:04', 30
go
select t1.FID,t1.TID,t1.NAME,t2.TM ,t2.DATA
from t1
inner join
(
select *,
ROW_NUMBER() over(partition by fid,tid order by TM) rownum
from t2
)t2
on t2.FID = t1.FID and t2.TID = t1.TID and t2.rownum = 1
/*
FID TID NAME TM DATA
1 1 name11 2014-01-01 12:00:00.000 30
1 2 name12 2014-01-01 12:00:00.000 30
2 1 name21 2014-01-01 12:01:00.000 30
2 2 name22 2014-01-01 12:02:00.000 30
3 1 name31 2014-01-01 12:03:00.000 30
3 2 name32 2014-01-01 12:04:00.000 30
*/
解决方案 »
- SQL 错误
- 关于组合查询。如下情况要如何组合查询来获得我要的数据内容?
- 求一触发器,关于自动给表生成符合规则的随机数问题。
- 证求一个有关改进存储过程的意见?
- 跨库触发器
- SQL中不同数据类型求和
- 如何获取进程正在查询的SQL语句?
- 请问怎样在存储过程中实现一个表中满足一定条件的几条记录转入同一数据库中另一个结构相同的表中?谢谢!
- 如何建库?
- 有关ms sql server 2000大小写转换的问题?请大虾帮忙!!!!!!!!
- 关于SQL日期转换成英文并赋值给某个字段
- 数据表中tbCustomer存在cId,cName,cParentId三个字段,表示客户的层级关系,其中tParentId和tId关联。请编写SQL获取指定索引
create table t1( FID int, TID int, NAME varchar(20))insert into t1
select 1 ,1 ,'name11' union all
select 1 ,2 ,'name12' union all
select 2 ,1 ,'name21' union all
select 2 ,2 ,'name22' union all
select 3 ,1 ,'name31' union all
select 3 ,2 ,'name32' create table t2(FID int, TID int, TM datetime, DATA int)insert into t2
select 1 ,1 ,'2014-01-01 12:00', 30 union all
select 1 ,2 ,'2014-01-01 12:00', 30 union all
select 2 ,1 ,'2014-01-01 12:01', 30 union all
select 2 ,2 ,'2014-01-01 12:02', 30 union all
select 3 ,1 ,'2014-01-01 12:03', 30 union all
select 3 ,2 ,'2014-01-01 12:04', 30 union all
select 1 ,1 ,'2014-01-02 12:00', 30 union all
select 1 ,2 ,'2014-01-02 12:00', 30 union all
select 2 ,1 ,'2014-01-02 12:01', 30 union all
select 2 ,2 ,'2014-01-02 12:02', 30 union all
select 3 ,1 ,'2014-01-02 12:03', 30 union all
select 3 ,2 ,'2014-01-02 12:04', 30 union all
select 1 ,1 ,'2014-01-02 13:00', 30 union all
select 1 ,2 ,'2014-01-02 13:00', 30 union all
select 2 ,1 ,'2014-01-02 13:01', 30 union all
select 2 ,2 ,'2014-01-02 13:02', 30 union all
select 3 ,2 ,'2014-01-02 13:04', 30
go
SELECT t1.FID, t1.TID, t1.NAME, F.TM, F.DATA
FROM t1
OUTER APPLY (SELECT TOP 1 TM, DATA FROM t2 WHERE FID=t1.FID AND TID=t1.TID ORDER BY TM DESC ) F
*
FROM
t1 a
LEFT JOIN t2 b ON a.fid=b.fid AND a.tid=b.tid
WHERE
b.tm=(SELECT
MAX(tm)
FROM
t2
WHERE
fid=b.fid AND tid=b.tid)