CREATE TABLE [dbo].[UserPC](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[RegisterTime] [datetime] NULL,
[RegisterIP] [nvarchar](50) NULL,
[SeriaNumber] [nvarchar](100) NULL,
[PcName] [nvarchar](100) NULL,
[Estate] [int] NULL,
[Re] [nvarchar](500) NULL,
CONSTRAINT [PK_UserPC] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GOGO
CREATE TABLE [dbo].[OnlinePc](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[PcID] [int] NULL,
[IP] [nvarchar](20) NULL,
[OnlineTime] [datetime] NULL,
CONSTRAINT [PK_ONLINEPC] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
用户电脑半小时向OnlinePc,插入一条记录;
求一SQL语句,查出如下结果:
UserPC.[UserID],[RegisterTime],[RegisterIP],[SeriaNumber],[PcName],[Estate],[Re],OnlinePc.OnlineTime as 最近一次在线时间,OnlinePc.IP as 最近一次在线IP
b.OnlineTime as 最近一次在线时间,b.IP as 最近一次在线IP
from UserPC a inner join OnlinePc b on a.UserID=b.UserID
where not exists(select 1 from OnlinePc where UserID=b.UserID and OnlineTime>a.OnlineTime)
select UserPC.[UserID],[RegisterTime],[RegisterIP],[SeriaNumber],[PcName],[Estate],[Re],
OnlinePc.OnlineTime as 最近一次在线时间,OnlinePc.IP as 最近一次在线IP
from UserPC
left outer join
(
select t.*,t1.IP
from
(
select max([ID]) as [ID],UserID,max(OnlineTime) as OnlineTime
from OnlinePc group by UserID
) as t
left outer join t1 on t1.id=t.id
)as OnlinePc on UserPC.ID=OnlinePc.PcID
那就:
select a.UserID,a.RegisterTime,a.RegisterIP,a.SeriaNumber,a.PcName,a.Estate,a.Re,
b.OnlineTime as 最近一次在线时间,b.IP as 最近一次在线IP
from UserPC a inner join OnlinePc b on a.ID=b.PcID
where not exists(select 1 from OnlinePc where PcID=b.PcID and OnlineTime>a.OnlineTime)
如果两表UserID 不匹配,那你的表设计得有问题,UserPC 表中不应该有UserID这个列.
select a.UserID,a.RegisterTime,a.RegisterIP,a.SeriaNumber,a.PcName,a.Estate,a.Re,
b.OnlineTime as 最近一次在线时间,b.IP as 最近一次在线IP
from UserPC a Left join OnlinePc b on a.UserID=b.UserID
where not exists(select 1 from OnlinePc where UserID=b.UserID and OnlineTime>b.OnlineTime)