select distinct loginlog.companyID,loginlog.LoginIP,loginlog.LoginTime,Company.CompanyName,Company.CompanyNameCN,Member.LoginCount from loginlog join Company on loginlog.CompanyID=Company.CompanyID join Member on loginlog.MemberID=Member.MemberID where loginlog.CompanyID>0 order by loginlog.LoginTime desc
loginlog 会员登录记录表 怎么筛选不同的companyID, 并读取LoginIP,LoginTime
Member 会员表 读取 LoginCount
Company公司表 这个表读取 CompanyName 并按 LoginTime排序.哪个高手解答下. 忙了一天了 还没有成. 谢谢了.
loginlog 会员登录记录表 怎么筛选不同的companyID, 并读取LoginIP,LoginTime
Member 会员表 读取 LoginCount
Company公司表 这个表读取 CompanyName 并按 LoginTime排序.哪个高手解答下. 忙了一天了 还没有成. 谢谢了.
这个筛选规则你必须告诉大家,
companyID相同的纪录,凭什么取某一条,而其他的都不要!!
不然没法做!
left companyname b on a.companyid=b.companyid
left member c on a.memberid=c.memberid
where companyid='要筛选的id'
oerder by a.logintime desc
left outer join companyname b on a.companyid=b.companyid
left outer join member c on a.memberid=c.memberid
where companyid='要筛选的id'
order by a.logintime desc
哪里不对了
SELECT a.LoginIP, b.CompanyName, b.CompanyNameCN, c.LoginCount
FROM LoginLog a LEFT OUTER JOIN
Company b ON a.CompanyID = b.CompanyID LEFT OUTER JOIN
Member c ON a.MemberID = c.MemberID
WHERE (a.CompanyID IN
(SELECT DISTINCT CompanyID
FROM loginLog))
ORDER BY a.LoginTime DESC我在说问题详细点
loginlog 会员登录记录表 怎么筛选不同的companyID, 并读取LoginIP,LoginTime
Member 会员表 读取 LoginCount
Company公司表 这个表读取 CompanyName
loginlog 表有字段 里面的companyID有很多是重复的
companyID LoginIP LoginTime MemberID
117 127.0.0.1 ... 11
117
110
117
Member表 有字段 LoginCount为统计某个会员登录总数
Company表 有字段CompanyName CompanyNameCN MemberID
我现在想读取三个表显示为
companyID LoginIP LoginTime CompanyName CompanyNameCN LoginCount
要求公司名称是不能重复的不知道我说清楚了 没有? 哪个在帮帮忙 谢谢
companyID相同的纪录,凭什么取某一条,而其他的都不要!!
不然没法做!只看一个公司的最新登录记录就可以了,知道他登录总数就行了.
select distinct
l.companyID,
l.LoginIP,
l.LoginTime,
c.CompanyName,
c.CompanyNameCN,
m.LoginCount
from loginlog l,
(select companyID,max(LoginTime) as LoginTime
from loginlog
where companyID > 0
group by companyID) lo Company c,
Member m
where l.companyID = lo.companyID
and l.LoginTime = lo.LoginTime
and l.CompanyID = c.CompanyID
and l.MemberID = m.MemberID
order by l.LoginTime desc