select a.time,a.name max(a.value_1+b.value_2) from tab_1 a,tab_2 b where a.time=b.time and a.name=b.name and a.time>='2003-01-28 00:00' and a.time<='2003-01-28 23:00' group by a.time,a.name
如果仅仅需要最大值: select a.name, max(a.value_1+b.value_2) from tab_1 a,tab_2 b where a.time=b.time and a.name=b.name and a.time>="20030128 00:00" and a.time<="20030128 23:00" group by a.name
如需相应的时间点: select t1.[name], t1.[time], (t1.value_1+t2.value_2) as total from tab_1 t1, tab_2 t2 where t1.[time]=t2.[time] and t1.[name]=t2.[name] and t1.[name]+'|'+cast((t1.value_1+t2.value_2) as varchar) in (select a.[name]+'|' + cast(max(a.value_1+b.value_2) as varchar) from tab_1 a,tab_2 b where a.[time]=b.[time] and a.[name]=b.[name] and a.[time]>='20030128 00:00' and a.[time]<='20030128 23:00' group by a.[name])
select a.name, a.time, c.m_val from tab_1 a left join tab_2 b on a.time=b.time and a.name=b.name left join ( select a.name, max(a.value_1+b.value_2) as m_val from tab_1 a left join tab_2 b on a.time=b.time and a.name=b.name where a.time>='2003-01-28 00:00' and a.time<='2003-01-28 23:00' group by a.name) as c on a.name = c.name where a.time>='2003-01-28 00:00' and a.time<='2003-01-28 23:00' and a.value_1+b.value_2 = c.m_val
and time>="20030128 00:00" and time<="20030128 23:00"中的格式错
如需相应的时间点: select t1.[name], t1.[time], (t1.value_1+t2.value_2) as total from tab_1 t1, tab_2 t2 where t1.[time]=t2.[time] and t1.[name]=t2.[name] and t1.[name]+'|'+cast((t1.value_1+t2.value_2) as varchar) in (select a.[name]+'|' + cast(max(a.value_1+b.value_2) as varchar) from tab_1 a,tab_2 b where a.[time]=b.[time] and a.[name]=b.[name] and a.[time]>='20030128 00:00' and a.[time]<='20030128 23:00' group by a.[name])修改一下 select t1.[name], t1.[time], (t1.value_1+t2.value_2) as total from tab_1 t1, tab_2 t2 where t1.[time]=t2.[time] and t1.[name]=t2.[name] and t1.[time]>='20030128 00:00' and t1.[time]<='20030128 23:00' and t1.[name]+'|'+cast((t1.value_1+t2.value_2) as varchar) in (select a.[name]+'|' + cast(max(a.value_1+b.value_2) as varchar) from tab_1 a,tab_2 b where a.[time]=b.[time] and a.[name]=b.[name] and a.[time]>='20030128 00:00' and a.[time]<='20030128 23:00' group by a.[name])
to huxin1012 你的查询我在access 下执行不成功 ??????
access中时间要表示为#20030128 00:00#
select max(value) 最大值 from (select * from tab_1 union all select * from tab_2) AA group by a.time,a.name
from tab_1 a,tab_2 b
where a.time=b.time and a.name=b.name
and a.time>='2003-01-28 00:00'
and a.time<='2003-01-28 23:00'
group by a.time,a.name
select a.name, max(a.value_1+b.value_2)
from tab_1 a,tab_2 b
where a.time=b.time and a.name=b.name
and a.time>="20030128 00:00"
and a.time<="20030128 23:00"
group by a.name
select t1.[name], t1.[time], (t1.value_1+t2.value_2) as total
from tab_1 t1, tab_2 t2
where t1.[time]=t2.[time] and t1.[name]=t2.[name]
and t1.[name]+'|'+cast((t1.value_1+t2.value_2) as varchar) in
(select a.[name]+'|' + cast(max(a.value_1+b.value_2) as varchar)
from tab_1 a,tab_2 b
where a.[time]=b.[time] and a.[name]=b.[name]
and a.[time]>='20030128 00:00'
and a.[time]<='20030128 23:00'
group by a.[name])
from tab_1 a left join tab_2 b on
a.time=b.time and a.name=b.name
left join (
select a.name, max(a.value_1+b.value_2) as m_val
from tab_1 a left join tab_2 b on
a.time=b.time and a.name=b.name
where a.time>='2003-01-28 00:00'
and a.time<='2003-01-28 23:00'
group by a.name) as c
on a.name = c.name
where a.time>='2003-01-28 00:00'
and a.time<='2003-01-28 23:00'
and a.value_1+b.value_2 = c.m_val
and time<="20030128 23:00"中的格式错
select t1.[name], t1.[time], (t1.value_1+t2.value_2) as total
from tab_1 t1, tab_2 t2
where t1.[time]=t2.[time] and t1.[name]=t2.[name]
and t1.[name]+'|'+cast((t1.value_1+t2.value_2) as varchar) in
(select a.[name]+'|' + cast(max(a.value_1+b.value_2) as varchar)
from tab_1 a,tab_2 b
where a.[time]=b.[time] and a.[name]=b.[name]
and a.[time]>='20030128 00:00'
and a.[time]<='20030128 23:00'
group by a.[name])修改一下
select t1.[name], t1.[time], (t1.value_1+t2.value_2) as total
from tab_1 t1, tab_2 t2
where t1.[time]=t2.[time] and t1.[name]=t2.[name]
and t1.[time]>='20030128 00:00'
and t1.[time]<='20030128 23:00'
and t1.[name]+'|'+cast((t1.value_1+t2.value_2) as varchar) in
(select a.[name]+'|' + cast(max(a.value_1+b.value_2) as varchar)
from tab_1 a,tab_2 b
where a.[time]=b.[time] and a.[name]=b.[name]
and a.[time]>='20030128 00:00'
and a.[time]<='20030128 23:00'
group by a.[name])
你的查询我在access 下执行不成功
??????