id time usersId
1 2010-8-1 2
2 2010-8-2 2
3 2010-8-3 1
4 2010-8-4 2
5 2010-8-5 1
6 2010-8-6 2
7 2010-8-7 1
8 2010-8-8 2要求写个sql语句得到效果 2 2010-8-8
1 2010-8-7
注:usersId是个外键
1 2010-8-1 2
2 2010-8-2 2
3 2010-8-3 1
4 2010-8-4 2
5 2010-8-5 1
6 2010-8-6 2
7 2010-8-7 1
8 2010-8-8 2要求写个sql语句得到效果 2 2010-8-8
1 2010-8-7
注:usersId是个外键
2 2010-8-8
1 2010-8-7
from (select distinct userID from table1 )a
cross join
(select top 1 * from table1 where userID=a.UserID order by [time] desc)b
但是报:列前缀 'a' 与查询中所用的表名或别名不匹配。
SELECT * FROM table1 AS a WHERE ID=(SELECT TOP 1 ID FROM table1 WHERE UserID=a.UserID ORDER BY [time] desc)也可用not exists
1 2010-8-1 2
2 2010-8-2 2
3 2010-8-3 1
4 2010-8-4 2
5 2010-8-5 1
6 2010-8-6 2
7 2010-8-7 1
8 2010-8-8 2
上面是表
然后写个sql语句查出:
用户id 时间
2 2010-8-8
1 2010-8-7这个sql语句怎么写?问题就是这样。
select 1,'2010-8-1',2 union all
select 2,'2010-8-2',2 union all
select 3,'2010-8-3',1 union all
select 4,'2010-8-4',2 union all
select 5,'2010-8-5',1 union all
select 6,'2010-8-6',2 union all
select 7,'2010-8-7',1 union all
select 8,'2010-8-8',2 select top 2 [time],userID from Test1 order by [time] desc
用户id 时间
2 2010-8-8
1 2010-8-7
union all
select max(time) from table where userid=1
go
--判断是否存在表#T
if object_id('#T') is not null
drop table #T--新建表#T
create table #T(
[id] int identity(1,1),
[time] varchar(10),
userId int )--添加测试值
insert into #T([time],userId)
select '2010-8-1', 2 union all
select '2010-8-2', 2 union all
select '2010-8-3', 1 union all
select '2010-8-4', 2 union all
select '2010-8-5', 1 union all
select '2010-8-6', 2 union all
select '2010-8-7', 1 union all
select '2010-8-8', 2--按userId分组,然后降序排列,再取时间最大的那个。
select userId,max([time]) as [time] from #T
group by userId
order by userId desc运行结果:
-----------------------------
userId time
2 2010-8-8
1 2010-8-7