work_id 时间 guid
气瓶回收工序 2009-11-17 10:17:34.000 1144DF946AA11740B4E89C7F8713AE7F
充前检查工序 2009-11-17 13:33:59.000 1144DF946AA11740B4E89C7F8713AE7F
充装登记工序 2009-11-12 09:30:24.000 1144DF946AA11740B4E89C7F8713AE7F
充装登记工序 2009-11-17 14:13:33.000 EB0B3A2DE8F3F048A21CFAF39F1B4C56
气瓶发送工序 2009-11-17 12:17:34.000 1144DF946AA11740B4E89C7F8713AE7F
要求同一个guid号,选出时间最大的那条记录work_id 时间 guid
充前检查工序 2009-11-17 13:33:59.000 1144DF946AA11740B4E89C7F8713AE7F
充装登记工序 2009-11-17 14:13:33.000 EB0B3A2DE8F3F048A21CFAF39F1B4C56
气瓶回收工序 2009-11-17 10:17:34.000 1144DF946AA11740B4E89C7F8713AE7F
充前检查工序 2009-11-17 13:33:59.000 1144DF946AA11740B4E89C7F8713AE7F
充装登记工序 2009-11-12 09:30:24.000 1144DF946AA11740B4E89C7F8713AE7F
充装登记工序 2009-11-17 14:13:33.000 EB0B3A2DE8F3F048A21CFAF39F1B4C56
气瓶发送工序 2009-11-17 12:17:34.000 1144DF946AA11740B4E89C7F8713AE7F
要求同一个guid号,选出时间最大的那条记录work_id 时间 guid
充前检查工序 2009-11-17 13:33:59.000 1144DF946AA11740B4E89C7F8713AE7F
充装登记工序 2009-11-17 14:13:33.000 EB0B3A2DE8F3F048A21CFAF39F1B4C56
from tb t
where not exists(select 1 from tb where work_id=t.work_id and 时间>t.时间)
select *from table1 A where 时间 = (select max(时间) from table where guid = A.guid )
select *
from tb t
where not exists(select 1 from tb where guid=t.guid and 时间>t.时间)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-19 14:13:57
-- 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]([work_id] varchar(12),[时间] datetime,[guid] varchar(32))
insert [tb]
select '气瓶回收工序','2009-11-17 10:17:34.000','1144DF946AA11740B4E89C7F8713AE7F' union all
select '充前检查工序','2009-11-17 13:33:59.000','1144DF946AA11740B4E89C7F8713AE7F' union all
select '充装登记工序','2009-11-12 09:30:24.000','1144DF946AA11740B4E89C7F8713AE7F' union all
select '充装登记工序','2009-11-17 14:13:33.000','EB0B3A2DE8F3F048A21CFAF39F1B4C56' union all
select '气瓶发送工序','2009-11-17 12:17:34.000','1144DF946AA11740B4E89C7F8713AE7F'
--------------开始查询--------------------------
select * from tb t where 时间=(select max(时间) from tb where guid=t.guid)
----------------结果----------------------------
/* work_id 时间 guid
------------ ----------------------- --------------------------------
充装登记工序 2009-11-17 14:13:33.000 EB0B3A2DE8F3F048A21CFAF39F1B4C56
充前检查工序 2009-11-17 13:33:59.000 1144DF946AA11740B4E89C7F8713AE7F(2 行受影响)
*/
--1
select * from tb t where 时间=(select max(时间) from tb where guid=t.guid)
--2
select t.* from tb t ,(select guid,max(时间) as 时间 from tb group by guid) as A
where t.时间=A.时间 and t.guid=A.guid
--3
select t.* from tb t where not exists (select 1 from tb where guid = t.guid and 时间 > t.时间)
select t.* from table t where not exists (select 1 from table where guid = t.guid and 时间 > t.时间)select t.* from table t where 时间 = (select max(时间) from table where guid = t.guid)[/sql]
select t.* from table t where not exists (select 1 from table where guid = t.guid and 时间 > t.时间) select t.* from table t where 时间 = (select max(时间) from table where guid = t.guid)
select t.* from tb t where not exists (select null from tb where guid = t.guid and 时间 > t.时间)