这种SQL怎么写呢？大家有什么好的想没有？

[CatNetMouse]

表结构及数据
class xm datetime
60 张三 2013-03-13 00:18:33.000
60 李四 2013-03-13 00:16:55.000
60 李智 2013-03-13 01:31:26.000
60 李智 2013-03-13 01:31:26.000
70 李智 2013-03-13 01:31:55.000

目标显示
要求：1：招时间升序排
2：同一个班级相同的人仅显示一条
3：显示结果如下

class xm
60 李四
60 张三
60 李智
70 李智

能否给出SQL，谢谢

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-05-14 21:46:44
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
--	Jun 17 2011 00:57:23 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([class] int,[xm] varchar(4),[datetime] datetime)
insert [huang]
select 60,'张三','2013-03-13 00:18:33.000' union all
select 60,'李四','2013-03-13 00:16:55.000' union all
select 60,'李智','2013-03-13 01:31:26.000' union all
select 60,'李智','2013-03-13 01:31:26.000' union all
select 70,'李智','2013-03-13 01:31:55.000'
--------------开始查询--------------------------

select DISTINCT * 
from [huang]
ORDER BY [datetime]
----------------结果----------------------------
/* 
class       xm   datetime
----------- ---- -----------------------
60          李四   2013-03-13 00:16:55.000
60          张三   2013-03-13 00:18:33.000
60          李智   2013-03-13 01:31:26.000
70          李智   2013-03-13 01:31:55.000

*/

[發糞塗牆]

兄弟，基础题啊~~~~~~~~多多练一下基本功吧

[CatNetMouse]

不好意思，这是我有个时间搞错了 60 李智 2013-03-13 01:31:26.000 60 李智 2013-03-13 01:31:28.000

[發糞塗牆]

如果是数据搞错了而已的话代码不用改

[發糞塗牆]

下次引用一下，不然不知道你回复了

[黄_瓜]

select * from [tb] as t where not exists(select 1 from tb where class=t.class and xm=t.xm and [datetime]<t.[datetime]) ORDER BY [datetime]

[CatNetMouse]

不改SQL就会有4条出来

[CatNetMouse]

6楼的正确的，谢谢

[發糞塗牆]

SELECT *
FROM Huang a
WHERE EXISTS (SELECT 1 FROM (
select DISTINCT class,xm
from [huang])b WHERE a.class=b.class AND a.xm=b.xm)
ORDER BY [datetime]

这个呢？

[CatNetMouse]

不行，多出一条了

[CatNetMouse]

引用 6 楼 Beirut 的回复:

select * from [tb] as t where not exists(select 1 from tb where class=t.class and xm=t.xm and [datetime]<t.[datetime]) ORDER BY [datetime]

能否写一下你的思路，谢谢！ 意义是不是这样的： tb（t）表中不在tb表中，条件是class相同，姓名相同，tb表中的日期<t表的日期呢，然后按日期排序？

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-05-14 22:29:13
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
--	Jun 17 2011 00:57:23 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([class] int,[xm] varchar(4),[datetime] datetime)
insert [huang]
select 60,'张三','2013-03-13 00:18:33.000' union all
select 60,'李四','2013-03-13 00:16:55.000' union all
select 60,'李智','2013-03-13 01:31:26.000' union all
select 60,'李智','2013-03-13 01:31:28.000' union all
select 70,'李智','2013-03-13 01:31:55.000'
--------------开始查询--------------------------

SELECT *
FROM Huang a
WHERE EXISTS (SELECT 1 FROM (
select  class,xm,MAX([datetime])[datetime]
from [huang]
GROUP BY class,xm)b WHERE a.class=b.class AND a.xm=b.xm AND a.[datetime]=b.[datetime])
ORDER BY [datetime]
----------------结果----------------------------
/* 
class       xm   datetime
----------- ---- -----------------------
60          李四   2013-03-13 00:16:55.000
60          张三   2013-03-13 00:18:33.000
60          李智   2013-03-13 01:31:28.000
70          李智   2013-03-13 01:31:55.000
*/

[CatNetMouse]

引用 12 楼 DBA_Huangzj 的回复:

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-05-14 22:29:13
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
--	Jun 17 2011 00:57:23 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([class] int,[xm] varchar(4),[datetime] datetime)
insert [huang]
select 60,'张三','2013-03-13 00:18:33.000' union all
select 60,'李四','2013-03-13 00:16:55.000' union all
select 60,'李智','2013-03-13 01:31:26.000' union all
select 60,'李智','2013-03-13 01:31:28.000' union all
select 70,'李智','2013-03-13 01:31:55.000'
--------------开始查询--------------------------

SELECT *
FROM Huang a
WHERE EXISTS (SELECT 1 FROM (
select  class,xm,MAX([datetime])[datetime]
from [huang]
GROUP BY class,xm)b WHERE a.class=b.class AND a.xm=b.xm AND a.[datetime]=b.[datetime])
ORDER BY [datetime]
----------------结果----------------------------
/* 
class       xm   datetime
----------- ---- -----------------------
60          李四   2013-03-13 00:16:55.000
60          张三   2013-03-13 00:18:33.000
60          李智   2013-03-13 01:31:28.000
70          李智   2013-03-13 01:31:55.000
*/

也正确，谢谢你们俩。等下就给你们分。呵 真得向你们学习哦。。

[黄_瓜]

引用 11 楼 CatNetMouse 的回复:

[quote=引用 6 楼 Beirut 的回复:] select * from [tb] as t where not exists(select 1 from tb where class=t.class and xm=t.xm and [datetime]<t.[datetime]) ORDER BY [datetime]

能否写一下你的思路，谢谢！ 意义是不是这样的： tb（t）表中不在tb表中，条件是class相同，姓名相同，tb表中的日期<t表的日期呢，然后按日期排序？ [/quote] 是滴

[CatNetMouse]

你们的思路是差不多的，一个是取小于的日期，另一个是取最大日期的 但是，你们两个都有一个问题 引用 12 楼 DBA_Huangzj 的回复 班级中最大日期相同时就会多出最大日期的变成两条 引用 6 楼 Beirut 的回复 班级中最小日期相同时就会多出最小日期的变成两条

[發糞塗牆]

SELECT *
FROM Huang a
WHERE EXISTS (SELECT 1 FROM (
SELECT DISTINCT  class,xm,MAX([datetime])[datetime]
from [huang]
GROUP BY class,xm)b WHERE a.class=b.class AND a.xm=b.xm AND a.[datetime]=b.[datetime])
ORDER BY [datetime]

加个distinct去