select distint b.*
from tb1 a
left join (
select tel,dotime=max(dotime)
from tb2
group by tel
)b1 on a.tel=b1.tel
left join tb2 b on a.tel=b.tel and b.dotime=b1.dotime
不知行不行
from tb1 a
left join (
select tel,dotime=max(dotime)
from tb2
group by tel
)b1 on a.tel=b1.tel
left join tb2 b on a.tel=b.tel and b.dotime=b1.dotime
不知行不行
from tb1 a
left join (
select a.resultid,a.tel
from tab2 a,(
select tel,dotime=max(dotime)
from tb2
group by tel
)b where a.tel=b.tel and a.dotime=b.dotime
group by b.tel,b.dotime
)b1 on a.tel=b1.tel
left join tb2 b on a.tel=b.tel and b.resultid=b1.resultid
from tb1 a
left join (
select tel,dotime=max(dotime),resultid =max(resultid )
from tb2
group by tel
)b1 on a.tel=b1.tel
left join tb2 b on a.tel=b.tel and b.dotime=b1.dotime and b.resultid =b1.resultid
from tb1 a
left join (
select tel,dotime=max(dotime)
from tb2
group by tel
)b1 on a.tel=b1.tel
left join tb2 b on a.tel=b.tel and b.dotime=b1.dotime
测试不行
create table tb1 (id int, [user] varchar(10), tel int)
insert into tb1 select
1 ,'aaa', '111' union all select
2 ,'bbb', '222' union all select
3 ,'ccc', '333'
create table tb2 (resultid int, [user] varchar(10), tel int, doman int , dotime datetime)
insert into tb2 select
1 ,'aaa', 111 ,10 ,'2004-08-02 20:00:00' union all select
2 ,'aaa', 111 ,20 ,'2004-08-21 13:00:00' union all select
3 ,'aaa', 111 ,20 ,'2004-08-11 12:00:00' union all select
4 ,'bbb', 222 ,30 ,'2004-08-15 15:00:00' union all select
5 ,'ccc', 333 ,20 ,'2004-08-18 05:00:00' union all select
6 ,'ccc', 333 ,10 ,'2004-08-20 05:00:00'--测试
select b.*
from tb1 a
left join (
select a.resultid,a.tel
from tb2 a,(
select tel,dotime=max(dotime)
from tb2
group by tel
)b where a.tel=b.tel and a.dotime=b.dotime
group by b.tel,b.dotime
)b1 on a.tel=b1.tel
left join tb2 b on a.tel=b.tel and b.resultid=b1.resultid
出错:
列 'a.resultid' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
服务器: 消息 8120,级别 16,状态 1,行 28
列 'a.tel' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
from tb1 a
left join (
select a.resultid,a.tel
from tb2 a,(
select tel,dotime=max(dotime)
from tb2
group by tel
)b where a.tel=b.tel and a.dotime=b.dotime
group by a.resultid,a.tel,a.dotime
)b1 on a.tel=b1.tel
left join tb2 b on a.tel=b.tel and b.resultid=b1.resultid