SQL取数据问题

wdxgdiy 2009-10-17 09:35:01
数据库表有很多字段就不一一 写出了 大致问题是这样

有一张表 temp

标识ID 外键uid 时间time
1 1 2009-9-26 11:57:20
2 1 2009-9-27 11:57:20
3 1 2009-9-21 11:57:20
4 2 2009-1-26 11:57:20
5 2 2009-2-27 11:57:20
6 3 2009-3-21 11:57:20

想取出结果为 每个外键ID只取一条记录(order by [ID] desc)也就是取此外键最新的ID

想得出下列结果应该怎样写SQL语句呢?
3 1 2009-9-21 11:57:20
5 2 2009-2-27 11:57:20
6 3 2009-3-21 11:57:20
...全文
36 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2009-10-17
  • 打赏
  • 举报
回复
create table [temp](ID int,uid int, time datetime)
insert into [temp] values(1 , 1 , '2009-9-26 11:57:20')
insert into [temp] values(2 , 1 , '2009-9-27 11:57:20')
insert into [temp] values(3 , 1 , '2009-9-21 11:57:20')
insert into [temp] values(4 , 2 , '2009-1-26 11:57:20')
insert into [temp] values(5 , 2 , '2009-2-27 11:57:20')
insert into [temp] values(6 , 3 , '2009-3-21 11:57:20')
go

select t.* from [temp] t where id = (select max(id) from [temp] where uid = t.uid) order by t.uid
select t.* from [temp] t where not exists (select 1 from [temp] where uid = t.uid and id > t.id) order by t.uid

drop table [temp]

/*
ID uid time
----------- ----------- ------------------------------------------------------
3 1 2009-09-21 11:57:20.000
5 2 2009-02-27 11:57:20.000
6 3 2009-03-21 11:57:20.000

(所影响的行数为 3 行)
*/
--小F-- 2009-10-17
  • 打赏
  • 举报
回复
应该是2,5,6吧 楼主
--小F-- 2009-10-17
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-17 09:42:03
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[uid] int,[time] datetime)
insert [tb]
select 1,1,'2009-9-26 11:57:20' union all
select 2,1,'2009-9-27 11:57:20' union all
select 3,1,'2009-9-21 11:57:20' union all
select 4,2,'2009-1-26 11:57:20' union all
select 5,2,'2009-2-27 11:57:20' union all
select 6,3,'2009-3-21 11:57:20'
--------------开始查询--------------------------
select * from tb a where time=(select max(time) from tb where uid=a.uid) order by 2
----------------结果----------------------------
/* ID uid time
----------- ----------- -----------------------
2 1 2009-09-27 11:57:20.000
5 2 2009-02-27 11:57:20.000
6 3 2009-03-21 11:57:20.000

(3 行受影响)
*/
dawugui 2009-10-17
  • 打赏
  • 举报
回复
select t.* from temp t where id = (select max(id) from temp where uid = t.uid)
select t.* from temp t where not exists (select 1 from temp where uid = t.uid and id > t.id)
sgtzzc 2009-10-17
  • 打赏
  • 举报
回复
或者

select *
from tb t
where
id=(select top 1 id from tb where uid=t.uid order by id desc)
xiequan2 2009-10-17
  • 打赏
  • 举报
回复
http://topic.csdn.net/u/20091013/15/9f058df7-4d29-47bf-a338-b63fcab2abc0.html?51371
sgtzzc 2009-10-17
  • 打赏
  • 举报
回复
select * from tb t where not exists(select 1 from tb where uid=t.uid and id>t.id)
wdxgdiy 2009-10-17
  • 打赏
  • 举报
回复
时间是乱写的。大家忽略

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧