使用时间戳组合3个相同的表

Question

我有一个Access数据库，由3个独立的人在场外使用;此离线位置没有网络链接(也不能)。

我有三个相同的数据库，因此有三个相同的表。每个用户使用相同的主键填写信息。对于这个"食品评估"示例:

Item       |      Color       | Timestamp
PERSON 1 (first database)
Carrot     |     Orange       | 2012-12-21 13:00:00
Watermelon |     Red          | 2012-12-21 19:00:00 <--
Blueberry  |     Blue         | 2012-12-21 17:00:00 <--
PERSON 2 (second database)
Carrot     |     Yellow       | 2012-12-21 15:00:00 <--
Apple      |     Green        | 2012-12-21 15:00:00 <--
PERSON 3 (third database)
Watermelon |     Green        | 2012-12-21 11:00:00 
Apple      |     Red          | 2012-12-21 14:00:00
Orange     |     Orange       | 2012-12-21 15:00:00 <--

我需要输出一个表:

Blueberry  |     Blue         | 2012-12-21 17:00:00
Watermelon |     Red          | 2012-12-21 19:00:00
Carrot     |     Yellow       | 2012-12-21 15:00:00
Apple      |     Green        | 2012-12-21 15:00:00
Orange     |     Orange       | 2012-12-21 15:00:00

所以，基于时间戳，我需要选择只有一个重复，但也有所有的非重复(项目是一个主，唯一键)。我就是不能用我的生命得到这个SQL ..使用

SELECT Item, Color, MAX(timestamp) 
FROM (SELECT ... FROM first 
UNION SELECT ... FROM second
UNION SELECT ... FROM third)
GROUP BY Item, Color

但是由于它必须对Color进行分组以获得MAX函数，因此仍然会造成索引冲突。

. .我怎么得到这个输出?

Answer 1

您最初忽略了Color，只是试图找到每个Item的最新时间戳。因此，您必须编写一个只执行此操作的查询，然后才能将其与完整表连接。

select a.Item, a.Color, a.Timestamp
from
(SELECT ... FROM first 
UNION SELECT ... FROM second
UNION SELECT ... FROM third) a
inner join
(SELECT Item, MAX(timestamp) as MaxTime
FROM (SELECT ... FROM first 
UNION SELECT ... FROM second
UNION SELECT ... FROM third) z
GROUP BY Item) b
on a.Item = b.Item and a.Timestamp = b.MaxTime

Answer 2

这会给你估计的结果:

create table t1 ( food nvarchar(20), color nvarchar(10), timestamp datetime );
create table t2 ( food nvarchar(20), color nvarchar(10), timestamp datetime );
create table t3 ( food nvarchar(20), color nvarchar(10), timestamp datetime );
insert into t1 values 
( 'Carrot', 'Orange', '2012-12-21T13:00:00' ), 
( 'Watermelon', 'Red', '2012-12-21T19:00:00' ), 
( 'Blueberry', 'Blue', '2012-12-21T17:00:00' );
insert into t2 values 
( 'Carrot', 'Yellow', '2012-12-21T15:00:00' ), 
( 'Apple', 'Green', '2012-12-21T15:00:00' );
insert into t3 values 
( 'Watermelon', 'Green', '2012-12-21T11:00:00' ), 
( 'Apple', 'Red', '2012-12-21T14:00:00' ), 
( 'Orange', 'Orange', '2012-12-21T15:00:00' );
with "data"
as 
(
    select * from t1 
    union all select * from t2 
    union all select * from t3
)
, "maxdata"
as
(
    select
        *,
        latest = MAX( "timestamp" ) over ( partition by "food" )
    from
        data
)
select 
    "food", "color", "timestamp"
from 
    maxdata
where
    "timestamp" = "latest"

@edit: overread "ms access" -这将是tsql。很抱歉。尽管如此，也许您可以使用查询将其转换为有效的语法以进行访问。

Answer 3

EDITED

MS Access的一个伟大之处在于查询上的查询。您可以将联合保存到查询中。把它当作你的主表。另一个按项目和最大日期分组的查询。

或者进行第二次查询，只返回最大日期并使用IN操作符的不同项;-)

---

<
• SQLFIDDLE演示/gh>

答案是ANSI SQL，所以你可以合并到MS ACCESS。您所需要的只是一个联合，然后将其分组:)

查询:

select x.*, max(x.TimeStamp) 
from (
select * from person1
union all
select * from person2
union all
select * from person3) as x
group by x.Item
order by x.TimeStamp asc
;

结果:

ITEM        COLOR   MAX(X.TIMESTAMP)
Carrot      Orange  December, 21 2012 15:00:00+0000
Apple       Green   December, 21 2012 15:00:00+0000
Orange      Orange  December, 21 2012 15:00:00+0000
Blueberry   Blue    December, 21 2012 17:00:00+0000
Watermelon  Red     December, 21 2012 19:00:00+0000