有三个字段,id,name,sex,time查找name不相同的前10条记录,根据time由高到低排序
解决方案 »
- 通过路径读取图片,将图片名字显示出来
- 求助,JS(jquery)如何获取iframe里页面的title
- 一到面试题,高人指点。。完成foo()函数的内容,要求弹出对话框提示当前选中的是第几个单选框
- 页面传递中文问题
- 我想按一次"回车"相当于按两次"回车"javascript怎么写啊
- [馨郁星愿]程序操作数据库超时及求解决方案或相关原因!
- @ASP.NET开发网页是用DataGrid好!还是Table好!
- 续昨天的一个一百分题,再补充一个问题:怎样同时显示多篇同时包含文字和图片的帖子
- SqlParameter 的参数问题
- 几百人在线1对1聊天jquery轮询效率实现问题
- DropDownExtender高级应用
- 奇怪的问题,关于DbDataAdapter.Fill
如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中。
select a.* from tb inner join (select max(id),name from tb group by name)b on a.id=b.id order by time desc
如果time不重复的话(select max(id),name from tb group by name),也可以改成(select max(time),name from tb group by name) b on a.time=b.time;
insert into @tbl
select 1,'A',0,'2009-1-1' union all
select 2,'B',0,'2009-1-2' union all
select 3,'B',0,'2009-1-3' union all
select 4,'D',1,'2009-2-1' union all
select 5,'E',1,'2009-3-1' union all
select 6,'F',0,'2009-4-1' union all
select 7,'G',1,'2009-5-1' union all
select 8,'H',1,'2009-7-1' union all
select 9,'I',0,'2009-10-2' union all
select 10,'J',1,'2009-10-1' union all
select 11,'J',1,'2009-10-11' union all
select 11,'L',0,'2009-12-1' select top 10* from @tbl a where not exists(
select* from @tbl b where b.name=a.name and b.id>a.id
)
order by time desc
id /name/sex/time
11 L 0 2009-12-01 00:00:00.000
11 J 1 2009-10-11 00:00:00.000
9 I 0 2009-10-02 00:00:00.000
8 H 1 2009-07-01 00:00:00.000
7 G 1 2009-05-01 00:00:00.000
6 F 0 2009-04-01 00:00:00.000
5 E 1 2009-03-01 00:00:00.000
4 D 1 2009-02-01 00:00:00.000
3 B 0 2009-01-03 00:00:00.000
1 A 0 2009-01-01 00:00:00.000
如果ID为主键的话,可以用这种方法:select id,name,sex,time from 表名 where id in (select distinct name from 表名 order by time desc)
drop table [dbo].[table1]
GOCREATE TABLE [dbo].[table1] (
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
[name] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[sex] [int] NULL default(0),
[time] [datetime] NULL default(GetDate())
)
GOINSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('aa',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('bb',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('aa',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('bb',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('cc',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('gg',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('hh',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('tt',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('ll',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('aa',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('bb',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('gg',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('bb',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('cc',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('ff',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('kk',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('aa',1,GetDate())
INSERT INTO [dbo].[table1]([name],[sex],[time]) VALUES('ee',1,GetDate())
GOSELECT top 10 [name],[time] FROM [dbo].[table1] GROUP BY [name],[time] ORDER BY [time] DESC
GO
。。 没看清 这个可以满足你要求了SELECT top 10 [id],[name],[sex],[time] FROM [dbo].[table1] GROUP BY [name],[id],[sex],[time] ORDER BY [time] DESC
select top 10 A.* from table1 a where A.id in
( select top 1 id from table1 where id=a.id ) order by time ASC
declare @tbl table(id int,name nchar(1),sex bit,time datetime )
insert into @tbl
select 1,'A',0,'2009-1-1' union all
select 2,'B',0,'2009-1-2' union all
select 3,'B',0,'2009-1-3' union all
select 4,'D',1,'2009-2-1' union all
select 5,'E',1,'2009-3-1' union all
select 6,'F',0,'2009-4-1' union all
select 7,'G',1,'2009-5-1' union all
select 8,'H',1,'2009-7-1' union all
select 9,'I',0,'2009-10-2' union all
select 10,'J',1,'2009-10-1' union all
select 11,'J',1,'2009-10-11' union all
select 11,'L',0,'2009-12-1'
select a.name,a.id,a.time from @tbl as a
inner join (select max(id) as id,[name] from @tbl group by name) as b on a.id=b.id
group by a.name,a.id,a.time
order by time desc
结果:
name id time
---------------------------------
L 11 2009-12-01 00:00:00.000
J 11 2009-10-11 00:00:00.000
I 9 2009-10-02 00:00:00.000
H 8 2009-07-01 00:00:00.000
G 7 2009-05-01 00:00:00.000
F 6 2009-04-01 00:00:00.000
E 5 2009-03-01 00:00:00.000
D 4 2009-02-01 00:00:00.000
B 3 2009-01-03 00:00:00.000
A 1 2009-01-01 00:00:00.000
上面的错了,应该这样
select top 10 A.* from table1 a where A.id in
( select top 1 id from table1 where NAME=a.NAME ) order by a.time ASC