日期:2014-05-18 浏览次数:20620 次
if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[日期串] varchar(28)) insert [tb] select 1,'120312;120523;120727;' union all select 2,'120623;120719;' union all select 3,'120930;' union all select 4,'120623;120719;121011;130229;' go declare @sdt varchar(10),@edt varchar(10) select @sdt='120722',@edt='121002' select a.* from tb a join ( select right(convert(varchar(10),dateadd(dd,number,@sdt),112),6) as dt from master..spt_values where type='P' and dateadd(dd,number,@sdt)<=@edt ) b on charindex(';'+b.dt+';',';'+a.日期串)>0 /** ID 日期串 ----------- ---------------------------- 1 120312;120523;120727; 3 120930; (2 行受影响) **/
------解决方案--------------------
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba') BEGIN DROP TABLE tba END GO CREATE TABLE tba ( ID INT, 日期串 VARCHAR(100) ) GO INSERT INTO tba SELECT 1, '120312;120523;120727;' UNION SELECT 2, '120623;120719;' UNION SELECT 3, '120930;' UNION SELECT 4, '120623;120719;121011;130229;' GO SELECT ID,日期串 FROM tba AS A,( SELECT CONVERT(VARCHAR(6),DATEADD(DAY,number,'120722'),12) AS date FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY,number,'120722') <= '121002') AS B WHERE CHARINDEX(';' + RTRIM(B.date) + ';',';' + A.日期串) > 0 ID 日期串 1 120312;120523;120727; 3 120930;
------解决方案--------------------
--来个05XML create table tb(id int,date varchar(100)) insert into tb select 1 ,'120312;120523;120727;' union all select 2 ,'120623;120719;' union all select 3 ,'120930;' union all select 4 ,'120623;120719;121011;130229;' go select a.id,b.date from ( select id,date = convert(xml,'<v>' + REPLACE(date, ';', '</v><v>') + '</v>') from tb ) A outer apply( select date = N.v.value('.', 'varchar(100)') from A.date.nodes('/v') N(v) ) B where b.date between '120722' and '121002' group by a.id,b.date drop table tb /*************** id date ----------- ---------------------------------------------------------------- 1 120727 3 120930 (2 行受影响)
------解决方案--------------------
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [ID] int, [日期串] varchar(27) ) go insert [test] select 1,'120312;120523;120727;' union all select 2,'120623;120719;' union all select 3,'120930;' union all select 4,'120623;120719;121011;130229' go --方法一:系统表构造 select a.* from test a inner join ( select right(convert(varchar(10),dateadd(dd,number,'20120722'),112),6) as [date] from master..spt_values where type='p' and number between 0 and datediff(dd,'20'+'120722','20'+'121002'))t on charindex(t.[date],a.[日期串])>0 /* ID 日期串 ---------------------------------- 1 120312;120523;120727; 3 120930; */ --方法二:使用CTE拆分字符串 with T (id,P1,P2) as ( select id, charindex(';',';'+[日期串]), charindex(';',[日期串])+1 from [test] union all select a.id, b.P2, charindex(';',[日期串],b.P2)+1 from [test] a join T b on a.id=b.id where charindex(';',[日期串],b.P2)>0 ),m as( select a.id, [日期串]=substring(a.[日期串],b.P1,b.P2 - b.P1 - 1) from [test] a join T b on a.id=b.id ) select b.* from m inner join test b on m.ID=b.ID where m.[日期串