表结构及数据
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,谢谢SQL怎么写呢
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,谢谢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*/
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]
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]这个呢?
能否写一下你的思路,谢谢!
意义是不是这样的:
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
*/
能否写一下你的思路,谢谢!
意义是不是这样的:
tb(t)表中不在tb表中,条件是class相同,姓名相同,tb表中的日期<t表的日期呢,然后按日期排序?
是滴
但是,你们两个都有一个问题
引用 12 楼 DBA_Huangzj 的回复
班级中最大日期相同时就会多出最大日期的变成两条
引用 6 楼 Beirut 的回复
班级中最小日期相同时就会多出最小日期的变成两条
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去