27,582
社区成员




create table tb(tstat int, tduty varchar(10),tem_num varchar(10), tstate_d datetime, tend_d datetime)
insert into tb values(0518 ,'423A','600479','2002-11-16 00:00:00.000','2007-04-03 00:00:00.000')
insert into tb values(0518 ,'423A','000814','2007-04-03 00:00:00.000','2008-01-22 00:00:00.000')
insert into tb values(0518 ,'434E','005301','2003-10-02 00:00:00.000','2005-08-28 00:00:00.000')
insert into tb values(0518 ,'434E','500567','2004-09-01 00:00:00.000','2005-04-05 00:00:00.000')
insert into tb values(0518 ,'434E','009113','2005-08-28 00:00:00.000','2006-09-01 00:00:00.000')
insert into tb values(0518 ,'434E','005301','2006-09-01 00:00:00.000','2007-01-05 00:00:00.000')
insert into tb values(0518 ,'434E','501028','2006-11-09 00:00:00.000','2008-01-01 00:00:00.000')
insert into tb values(0518 ,'434E','005301','2007-01-05 00:00:00.000','2008-01-22 00:00:00.000')
go
--需要使用临时表拆分时间。
SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b
select t1.tduty , (case when exists (select 1 from
(
select m.tduty , m.tstate_d + n.id dt from tb m ,# n where m.tstate_d + n.id < m.tend_d
group by m.tduty , m.tstate_d + n.id
having count(1) > 1
) t2 where t2.tduty = t1.tduty) then 1 else 0 end) + 1 result
from (select distinct tduty from tb) t1
drop table tb , #
/*
tduty result
---------- -----------
423A 1
434E 2
(所影响的行数为 2 行)
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CSIS201](
[TID] [bigint] NOT NULL,
[TEM_NUM] [char](6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TE_ID] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
[TSTATE] [char](7) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TSTATE_D] [datetime] NOT NULL,
[TDUTY] [char](4) COLLATE Chinese_PRC_CI_AS NULL,
[TREAL_DUTY] [char](4) COLLATE Chinese_PRC_CI_AS NULL,
[TPLACE] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[TR_EMNUM] [char](6) COLLATE Chinese_PRC_CI_AS NULL,
[TUSER_ID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[TIN_D] [datetime] NULL,
[TREMARK] [varchar](400) COLLATE Chinese_PRC_CI_AS NULL,
[TPAY_PLACE] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[TTRANSFER_PLACE] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[TPRE_TERM] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,
[TEND_D] [datetime] NULL,
[TDED_SUP] [decimal](18, 4) NULL CONSTRAINT [DF_CSIS201_TDED_SUP] DEFAULT (0),
[TYEARMON] [varchar](6) COLLATE Chinese_PRC_CI_AS NULL,
[TPRECONTROLID] [bigint] NULL,
[TSIGN] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[TBXFLAG] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[TRESTEND_D] [datetime] NULL,
[TdedsupNote] [varchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[TyearHolidayFlag] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
[TUP_D] [datetime] NULL,
[TF_REALDUTY] [varchar](4) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_PSD201_1__13] PRIMARY KEY CLUSTERED
(
[TID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF