日期:2014-05-16 浏览次数:20500 次
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-06 14:16:34
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([jobno] varchar(13),[name] varchar(4),[desc] varchar(9),[opdate] datetime)
insert [tb]
select 'DT14012103535','周福','生成费用','2014-03-05 12:10:14.000' union all
select 'DT14012103535','周福','核销计划!','2014-03-05 12:10:17.213' union all
select 'DT14012103535','周福','新增业务!','2014-03-05 12:10:17.000' union all
select 'DT14012103535','周福','核销计划!','2014-03-05 12:10:19.280' union all
select 'DT14012103536','周福','生成费用','2014-03-06 12:10:14.000' union all
select 'DT14012103536','周福','核销计划!','2014-03-06 12:10:17.213' union all
select 'DT14012103536','周福','新增业务!','2014-03-06 12:10:17.000' union all
select 'DT14012103536','张福','核销计划!','2014-03-06 15:10:19.280'
--------------开始查询--------------------------
select * from [tb] t WHERE EXISTS(SELECT 1 FROM TB WHERE DATEDIFF(mi,opdate,t.opdate)=0 AND jobno=t.jobno AND [desc]=T.[DESC] AND opdate<>t.opdate)
----------------结果----------------------------
/* jobno name desc opdate
------------- ---- --------- -----------------------
DT14012103535 周福 核销计划! 2014-03-05 12:10:17.213
DT14012103535 周福 核销计划! 2014-03-05 12:10:19.280
(2 行受影响)
*/
WITH a1(jobno,name,[desc],opdate) AS
(
SELECT 'DT14012103535','周福','生成费用','2014-03-05 12:10:14.000' UNION ALL
SELECT 'DT14012103535','周福','核销计划!','2014-03-05 12:10:17.213' UNION ALL
SELECT 'DT14012103535','周福','新增业务!','2014-03-05 12:10:17.000'