目前提供User表和AssetsRecord表数据,用MSSQL 脚本查出Assets表一样的结果。要点:
AssetsRecord表是记录所有曾经使用过笔记本用户,目前只要把笔记本现在使用者显示出来。表:User
UID UName Address Phone
1 张三 北京市A路 123000000
2 李四 北京市B路 134111111
3 王五 广东深圳A路 123332232
4 赵六 广东广州A路 232342422 表:AssetsRecord
PID PName UID DataTime
1 笔记本A 1 2009-11-7
2 笔记本A 3 2009-11-10
3 笔记本A 4 2009-11-11
4 笔记本B 3 2009-1-1
5 笔记本C 2 2009-1-2
6 笔记本C 3 2009-1-6
7 笔记本D 1 2009-1-23
8 笔记本D 3 2009-1-23
9 笔记本E 1 2009-1-23
表:Assets
ID PName UName Address Phone
1 笔记本A 赵六 广东广州A路 232342422
2 笔记本B 李四 北京市B路
3 笔记本C 王五 广东深圳A路
4 笔记本D 王五 广东深圳A路
5 笔记本E 张三 北京市A路
FROM [USER] U JOIN (
select uid,pname
from AssetsRecord a
where not exists (SELECT * FROM AssetsRecord WHERE A.PNAME=PNAME AND PID>A.PID)) K
ON U.UID=K.UID
Select Pname,Uname,Address,Phone from User U left join
(SELECT max(DataTime),Pname,UID from AssertRecord group by Pname,UID) A
on U.UID=A.UID
FROM [USER] U JOIN (
select uid,pname
from AssetsRecord a
where not exists (SELECT * FROM AssetsRecord WHERE A.PNAME=PNAME AND DataTime>A.DataTime)) K
ON U.UID=K.UID
FROM [USER] U JOIN (
select uid,pname
from AssetsRecord a
where [DATETIME]=(SELECT MAX([DATETIME]) FROM AssetsRecord WHERE A.PNAME=PNAME ))K
ON U.UID=K.UID 这样效率应该要高
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-07 20:15:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[User]
if object_id('[User]') is not null drop table [User]
go
create table [User]([UID] int,[UName] varchar(4),[Address] varchar(11),[Phone] int)
insert [User]
select 1,'张三','北京市A路',123000000 union all
select 2,'李四','北京市B路',134111111 union all
select 3,'王五','广东深圳A路',123332232 union all
select 4,'赵六','广东广州A路',232342422
--> 测试数据:[AssetsRecord]
if object_id('[AssetsRecord]') is not null drop table [AssetsRecord]
go
create table [AssetsRecord]([PID] int,[PName] varchar(7),[UID] int,[DataTime] datetime)
insert [AssetsRecord]
select 1,'笔记本A',1,'2009-11-7' union all
select 2,'笔记本A',3,'2009-11-10' union all
select 3,'笔记本A',4,'2009-11-11' union all
select 4,'笔记本B',3,'2009-1-1' union all
select 5,'笔记本C',2,'2009-1-2' union all
select 6,'笔记本C',3,'2009-1-6' union all
select 7,'笔记本D',1,'2009-1-23' union all
select 8,'笔记本D',3,'2009-1-23' union all
select 9,'笔记本E',1,'2009-1-23'
--------------开始查询--------------------------
select
b.PName,a.UName,a.address,a.phone
from
[User] a
join
(select
[uid],pname
from
AssetsRecord t
where
not exists (select * from AssetsRecord where pname=t.pname and DataTime>t.DataTime))b
on
a.uid=b.uid
order by 1
----------------结果----------------------------
/* PName UName address phone
------- ----- ----------- -----------
笔记本A 赵六 广东广州A路 232342422
笔记本B 王五 广东深圳A路 123332232
笔记本C 王五 广东深圳A路 123332232
笔记本D 张三 北京市A路 123000000
笔记本D 王五 广东深圳A路 123332232
笔记本E 张三 北京市A路 123000000(6 行受影响)
*/
(
UID int identity(1,1) primary key,
UName nvarchar(50),
Address nvarchar(50),
Phone nvarchar(50)
)
insert into #User select '张三','北京市A路','123000000'
union all select '李四','北京市B路','134111111'
union all select '王五','广东广州A路','123332232'
union all select '赵六','广东广州A路','232342422'create table #AssetsRecord1
(
PID int identity(1,1) primary key,
PName nvarchar(50),
UID int,
DataTime datetime
)
insert into #AssetsRecord1 select '笔记本A',1,'2009-11-7'
union all select '笔记本A',3,'2009-11-10'
union all select '笔记本A',4,'2009-11-11'
union all select '笔记本B',3,'2009-1-1'
union all select '笔记本C',2,'2009-1-2'
union all select '笔记本C',3,'2009-1-6'
union all select '笔记本D',1,'2009-1-23'
union all select '笔记本D',3,'2009-1-23'
union all select '笔记本E',1,'2009-1-23'select P.PName,A.UName,A.Address,A.Phone from #User A
join
(
select * from #AssetsRecord1 A where not exists
(select * from #AssetsRecord1 where A.PName=PName and A.DataTime<DataTime)
)
P on A.UID=P.UID
PName UName Address Phone
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
笔记本A 赵六 广东广州A路 232342422
笔记本B 王五 广东广州A路 123332232
笔记本C 王五 广东广州A路 123332232
笔记本D 张三 北京市A路 123000000
笔记本D 王五 广东广州A路 123332232
笔记本E 张三 北京市A路 123000000(6 行受影响)
相比之下这个效率要高些,(我参看了下statistics io 和statistics time)
select
t.PName,a.UName,a.address,a.phone
from
[User] a,AssetsRecord t
where a.UID=t.UID and
a.UID= (select top 1 UID from AssetsRecord where pname=t.pname order by DataTime desc)