select P.ID,P.Productname,U.username,P.updatetime from Product P ( select * from 用户表 U where not exists(select * from 用户表 where userid=U.userid and groupid<U.groupid) ) U on P.userid=U.userid order by updatetime
select * from Product p,用户表 u where p.userid=u.userid and not exists(select 1 from 用户表 where userid=u.userid and updatetime>u.updatetime)
貌似写错啦更正下select P.ID,P.Productname,U.username,P.updatetime from ( select * from Product P where not exists (select * from Productname=P.Productname and Userid=U.Userid and updatetime<p.updatetime) ) P join 用户表 U on U.Userid=P.Userid order by groupid,updatetime
Select p.Productname,max(p.Userid),max(p.updatetime),max(u.username),max(u.groupid) From Product p,user u Where u.userid=p.Userid Group By p.Productname Order by u.groupid desc,updatetime desc
select * from Productname=P.Productname and Userid=U.Userid 四楼这句是什么?没有看出来,也没有运行成功
给点测试数据 及其结果 在试下 select P.ID,P.Productname,U.username,P.updatetime from ( select * from Product P where not exists (select * from Product where Productname=P.Productname and Userid=U.Userid and updatetime<p.updatetime) ) P join 用户表 U on U.Userid=P.Userid order by groupid,updatetime
insert into product(productname,userid) values('苹果',1) insert into product(productname,userid) values('梨',1) insert into product(productname,userid) values('葡萄',2) insert into product(productname,userid) values('香蕉',2) insert into product(productname,userid) values('哈密瓜',2) insert into product(productname,userid) values('西瓜',3)insert into puser(username,groupid) values('aaa',3) insert into puser(username,groupid) values('bbb',0) insert into puser(username,groupid) values('cc',1)这是数据
测试结果是: ID productname userid username 2 梨 1 aaa 6 西瓜 3 cc 5 哈密瓜 2 bbb
if object_id('product') is not null drop table product create table product ( ID int identity(1,1), Productname nvarchar(20), Userid int, updatetime datetime ) insert into product(productname,userid,updatetime) values('苹果',1,'2010-5-5') insert into product(productname,userid,updatetime) values('梨',1,'2010-5-15') insert into product(productname,userid,updatetime) values('葡萄',2,'2010-6-15') insert into product(productname,userid,updatetime) values('香蕉',2,'2010-7-15') insert into product(productname,userid,updatetime) values('哈密瓜',2,'2010-8-15') insert into product(productname,userid,updatetime) values('西瓜',3,'2010-9-1')if object_id('puser') is not null drop table puser create table puser ( ID int identity(1,1), username nvarchar(20), groupid int ) insert into puser(username,groupid) values('aaa',3) insert into puser(username,groupid) values('bbb',0) insert into puser(username,groupid) values('cc',1)select P.ID,P.productname,P.userid,U.username from ( select * from product P where not exists (select * from product where Userid=P.Userid and updatetime>P.updatetime) ) P join puser U on P.userid=U.ID order by groupid descID productname userid username ----------- -------------------- ----------- -------------------- 2 梨 1 aaa 6 西瓜 3 cc 5 哈密瓜 2 bbb(3 行受影响)
谢谢楼上,你的也运行成功了。select * from product p,puser u where u.userid=p.userid and updatetime in(select max(updatetime) from product c group by c.userid) order by groupid desc我是这样写的,运行成功,不知道可行?效率如何?
from
Product P
(
select * from 用户表 U where not exists(select * from 用户表 where userid=U.userid and groupid<U.groupid)
) U
on P.userid=U.userid
order by updatetime
where p.userid=u.userid
and not exists(select 1 from 用户表 where userid=u.userid and updatetime>u.updatetime)
from
(
select * from Product P where not exists
(select * from Productname=P.Productname and Userid=U.Userid and updatetime<p.updatetime)
) P
join
用户表 U
on U.Userid=P.Userid
order by groupid,updatetime
四楼这句是什么?没有看出来,也没有运行成功
及其结果
在试下
select P.ID,P.Productname,U.username,P.updatetime
from
(
select * from Product P where not exists
(select * from Product where Productname=P.Productname and Userid=U.Userid and updatetime<p.updatetime)
) P
join
用户表 U
on U.Userid=P.Userid
order by groupid,updatetime
insert into product(productname,userid) values('梨',1)
insert into product(productname,userid) values('葡萄',2)
insert into product(productname,userid) values('香蕉',2)
insert into product(productname,userid) values('哈密瓜',2)
insert into product(productname,userid) values('西瓜',3)insert into puser(username,groupid) values('aaa',3)
insert into puser(username,groupid) values('bbb',0)
insert into puser(username,groupid) values('cc',1)这是数据
ID productname userid username
2 梨 1 aaa
6 西瓜 3 cc
5 哈密瓜 2 bbb
if object_id('product') is not null drop table product
create table product
(
ID int identity(1,1),
Productname nvarchar(20),
Userid int,
updatetime datetime
)
insert into product(productname,userid,updatetime) values('苹果',1,'2010-5-5')
insert into product(productname,userid,updatetime) values('梨',1,'2010-5-15')
insert into product(productname,userid,updatetime) values('葡萄',2,'2010-6-15')
insert into product(productname,userid,updatetime) values('香蕉',2,'2010-7-15')
insert into product(productname,userid,updatetime) values('哈密瓜',2,'2010-8-15')
insert into product(productname,userid,updatetime) values('西瓜',3,'2010-9-1')if object_id('puser') is not null drop table puser
create table puser
(
ID int identity(1,1),
username nvarchar(20),
groupid int
)
insert into puser(username,groupid) values('aaa',3)
insert into puser(username,groupid) values('bbb',0)
insert into puser(username,groupid) values('cc',1)select P.ID,P.productname,P.userid,U.username
from
(
select * from product P where not exists
(select * from product where Userid=P.Userid and updatetime>P.updatetime)
) P
join puser U
on P.userid=U.ID
order by groupid descID productname userid username
----------- -------------------- ----------- --------------------
2 梨 1 aaa
6 西瓜 3 cc
5 哈密瓜 2 bbb(3 行受影响)