有2个表A、B,结构相同;内容不同,记录数据较多
例子数据
A表
id point name maxV minV realtime
1 A1 点1 3 1 2008-8-27 0:05
2 A2 点2 4 2 2008-8-27 0:05
3 A3 点3 10 6 2008-8-27 0:05
4 A1 点1 11 7 2008-8-27 0:15
5 A2 点2 10 9 2008-8-27 0:15
6 A3 点3 10 8 2008-8-27 0:15
id是自增列 B表
id point name maxV minV realtime
1 A1 点1 3 1 2008-8-27 0:05
2 A2 点2 4 2 2008-8-27 0:05
3 A4 点4 10 6 2008-8-27 0:05
4 A4 点4 11 7 2008-8-27 0:15
5 A2 点2 10 9 2008-8-27 0:35
6 A3 点3 10 8 2008-8-27 0:55
id是自增列
所要结果:
point name maxV minV realtime
A1 点1 11 7 2008-8-27 0:15
A2 点2 10 9 2008-8-27 0:35
A3 点3 10 8 2008-8-27 0:55
A4 点4 11 7 2008-8-27 0:15 联合2表,取得各点最大时间对应的那条数据
除了考虑执行外,还需要考虑一下执行效率,因为2表中的数据记录很多
例子数据
A表
id point name maxV minV realtime
1 A1 点1 3 1 2008-8-27 0:05
2 A2 点2 4 2 2008-8-27 0:05
3 A3 点3 10 6 2008-8-27 0:05
4 A1 点1 11 7 2008-8-27 0:15
5 A2 点2 10 9 2008-8-27 0:15
6 A3 点3 10 8 2008-8-27 0:15
id是自增列 B表
id point name maxV minV realtime
1 A1 点1 3 1 2008-8-27 0:05
2 A2 点2 4 2 2008-8-27 0:05
3 A4 点4 10 6 2008-8-27 0:05
4 A4 点4 11 7 2008-8-27 0:15
5 A2 点2 10 9 2008-8-27 0:35
6 A3 点3 10 8 2008-8-27 0:55
id是自增列
所要结果:
point name maxV minV realtime
A1 点1 11 7 2008-8-27 0:15
A2 点2 10 9 2008-8-27 0:35
A3 点3 10 8 2008-8-27 0:55
A4 点4 11 7 2008-8-27 0:15 联合2表,取得各点最大时间对应的那条数据
除了考虑执行外,还需要考虑一下执行效率,因为2表中的数据记录很多
(
select * from a
union all
select * from b
)
select * from t a
where not exists(
select 1 from t
where point =t.point
and realtime >t.realtime)
from tba a,tbb b
where a.point=b.point and a.name=b.name
group by a.point,a.name
不知是不是这样的!试试吧!
from (select * from 表A union all select * from 表B)t group by point,name
(
select * from a
union all
select * from b
) m
where realtime = (select max(realtime) from
(
select * from a
union all
select * from b
) n
where point = m.point
)
order by m.point
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-01 15:37:52
-- Verstion:
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[point] varchar(2),[name] varchar(3),[maxV] int,[minV] int,[realtime] datetime)
insert [A]
select 1,'A1','点1',3,1,'2008-8-27 0:05' union all
select 2,'A2','点2',4,2,'2008-8-27 0:05' union all
select 3,'A3','点3',10,6,'2008-8-27 0:05' union all
select 4,'A1','点1',11,7,'2008-8-27 0:15' union all
select 5,'A2','点2',10,9,'2008-8-27 0:15' union all
select 6,'A3','点3',10,8,'2008-8-27 0:15'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[point] varchar(2),[name] varchar(3),[maxV] int,[minV] int,[realtime] datetime)
insert [B]
select 1,'A1','点1',3,1,'2008-8-27 0:05' union all
select 2,'A2','点2',4,2,'2008-8-27 0:05' union all
select 3,'A4','点4',10,6,'2008-8-27 0:05' union all
select 4,'A4','点4',11,7,'2008-8-27 0:15' union all
select 5,'A2','点2',10,9,'2008-8-27 0:35' union all
select 6,'A3','点3',10,8,'2008-8-27 0:55'
--------------开始查询--------------------------
select m.* from
(
select * from a
union all
select * from b
) m
where realtime = (select max(realtime) from
(
select * from a
union all
select * from b
) n
where point = m.point
)
order by m.point
----------------结果----------------------------
/* (6 行受影响)
id point name maxV minV realtime
----------- ----- ---- ----------- ----------- -----------------------
4 A1 点1 11 7 2008-08-27 00:15:00.000
5 A2 点2 10 9 2008-08-27 00:35:00.000
6 A3 点3 10 8 2008-08-27 00:55:00.000
4 A4 点4 11 7 2008-08-27 00:15:00.000(4 行受影响)
*/