如有:
ID 日期串:
1 120312;120523;120727;
2 120623;120719;
3 120930;
4 120623;120719;121011;130229;
如我要选择得到日期范围是在 120722 ~ 121002 之间日期的 ID 出来？
结果: ID = 1,3 都是符合要求。
要如何做？
谢谢！
------解决方案--------------------
- SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([ID] int,[日期串] varchar(28))insert [tb]select 1,'120312;120523;120727;' union allselect 2,'120623;120719;' union allselect 3,'120930;' union allselect 4,'120623;120719;121011;130229;'godeclare @sdt varchar(10),@edt varchar(10)select @sdt='120722',@edt='121002'select a.*from tb ajoin ( 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)<[email protected]) b on charindex(';'+b.dt+';',';'+a.日期串)>0/**ID 日期串----------- ----------------------------1 120312;120523;120727;3 120930;(2 行受影响)**/
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( ID INT, 日期串 VARCHAR(100))GOINSERT INTO tbaSELECT 1, '120312;120523;120727;' UNIONSELECT 2, '120623;120719;' UNIONSELECT 3, '120930;' UNIONSELECT 4, '120623;120719;121011;130229;'GOSELECT ID,日期串FROM tba AS A,(SELECT CONVERT(VARCHAR(6),DATEADD(DAY,number,'120722'),12) AS dateFROM master..spt_valuesWHERE type = 'P' AND DATEADD(DAY,number,'120722') <= '121002') AS BWHERE CHARINDEX(';' + RTRIM(B.date) + ';',';' + A.日期串) > 0ID 日期串1 120312;120523;120727;3 120930;
------解决方案--------------------
- SQL code
--来个05XMLcreate table tb(id int,date varchar(100))insert into tbselect 1 ,'120312;120523;120727;' union allselect 2 ,'120623;120719;' union allselect 3 ,'120930;' union allselect 4 ,'120623;120719;121011;130229;'goselect a.id,b.datefrom ( 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)) Bwhere b.date between '120722' and '121002'group by a.id,b.datedrop table tb/***************id date----------- ----------------------------------------------------------------1 1207273 120930(2 行受影响)
------解决方案--------------------
- SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([ID] int,[日期串] varchar(27))goinsert [test]select 1,'120312;120523;120727;' union allselect 2,'120623;120719;' union allselect 3,'120930;' union allselect 4,'120623;120719;121011;130229'go--方法一：系统表构造select a.* from test ainner join (select right(convert(varchar(10),dateadd(dd,number,'20120722'),112),6) as [date]from master..spt_valueswhere type='p' and number between 0 and datediff(dd,'20'+'120722','20'+'121002'))ton 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),mas( 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 bon m.ID=b.IDwhere m.[日期串] between 120722 and 121002/*ID 日期串-------------------------------------1 120312;120523;120727;3 120930;*/