110,532
社区成员
发帖
与我相关
我的任务
分享
SELECT A.comname ,B.loginip,B.logincount,B.logindt FROM company A,
(SELECT loginip,comid,MAX(logindt) AS logindt,COUNT(*) AS logincount
FROM LoginIP GROUP BY loginip,comid ) B
WHERE A.comid = B.comid
create table LoginIP
(loginid int,comid int,loginip nvarchar(30),logindt datetime)
create table company
(comid int,comname nvarchar(50))
insert into LoginIP
select 1,1,'60.192.168.1','2009-03-31 10:11:19'
union all
select 2,1,'60.192.168.1','2009-03-30 10:11:19'
union all
select 3,2,'60.192.168.1','2009-01-1 10:11:19'
union all
select 4,2,'60.192.168.1','2009-01-2 10:11:19'
insert into company
select 1,'测试公司 A '
union all
select 2,'测试公司 B'
select comname,b.loginip,b.次数,b.最后时间 from company a
inner join
(
select loginip,comid,count(1)as 次数,max(logindt) as 最后时间
from LoginIP
group by comid,loginip
) b on a.comid = b.comid
comname loginip 次数 最后时间
-------------------------------------------------- ------------------------------ ----------- -----------------------
测试公司 A 60.192.168.1 2 2009-03-31 10:11:19.000
测试公司 B 60.192.168.1 2 2009-01-02 10:11:19.000
(2 row(s) affected)
select C.comname,L.IP,count(loginip),(select L.logindt from LoginIP L where logindt in (select max(logindt) from loginip=L.loginip)) from LoginIP L join company C
on L.comid=C.comid