--1 select *,id = row_number() over (partition by 类别,名称 order by 创建时间) from tb
CREATE TABLE #Test (类别 VARCHAR(10),名称 VARCHAR(10),创建时间 CHAR(10),) INSERT #Test SELECT '动物','小狗','2001-01-01' UNION ALL SELECT '动物','小狗','2001-01-02' UNION ALL SELECT '动物','小狗','2001-01-03' UNION ALLSELECT '动物','小猫','2001-01-11' UNION ALL SELECT '动物','小猫','2001-01-12' UNION ALL SELECT '动物','小猫','2001-01-12' UNION ALL SELECT '动物','小猫','2001-01-19' UNION ALLSELECT '家俱','餐桌','2001-01-21' UNION ALL SELECT '家俱','餐桌','2001-01-22' UNION ALL SELECT '家俱','餐桌','2001-01-23' UNION ALL SELECT '家俱','餐桌','2001-01-23' UNION ALL SELECT '家俱','餐桌','2001-01-24' UNION ALLSELECT '家俱','椅子','2001-02-11' UNION ALL SELECT '家俱','椅子','2001-01-12' UNION ALL SELECT '家俱','椅子','2001-01-13' UNION ALL SELECT '家俱','椅子','2001-01-23' UNION ALL SELECT '家俱','椅子','2001-01-23' UNION ALL SELECT '家俱','椅子','2001-01-26' select *,row_number() over (partition by 类别,名称 order by 创建时间) as 自定义编号 from #Test drop table #Test/* 类别 名称 创建时间 自定义编号 ---------- ---------- ---------- -------------------- 动物 小狗 2001-01-01 1 动物 小狗 2001-01-02 2 动物 小狗 2001-01-03 3 动物 小猫 2001-01-11 1 动物 小猫 2001-01-12 2 动物 小猫 2001-01-12 3 动物 小猫 2001-01-19 4 家俱 餐桌 2001-01-21 1 家俱 餐桌 2001-01-22 2 家俱 餐桌 2001-01-23 3 家俱 餐桌 2001-01-23 4 家俱 餐桌 2001-01-24 5 家俱 椅子 2001-01-12 1 家俱 椅子 2001-01-13 2 家俱 椅子 2001-01-23 3 家俱 椅子 2001-01-23 4 家俱 椅子 2001-01-26 5 家俱 椅子 2001-02-11 6 */
select *,row_number() over (partition by 类别,名称 order by 创建时间) as 编号 from tb
--2 with t as ( select *,id = row_number() over (partition by 类别,名称 order by 创建时间) from tb ) select *,(select max(id)%2 from t where 类别 = a.类别 and 名称 = a.名称)cn, (select max(id)/2 from t where 类别 = a.类别 and 名称 = a.名称)cm into #t from t aselect 类别,名称,(case when cn = 1 then (select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1) else dateadd(dd,datediff(dd,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm),(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1))/2,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1))) from #t b
CREATE TABLE tb (类别 VARCHAR(10),名称 VARCHAR(10),创建时间 CHAR(10),) INSERT tb SELECT '动物','小狗','2001-01-01' UNION ALL SELECT '动物','小狗','2001-01-02' UNION ALL SELECT '动物','小狗','2001-01-03' UNION ALLSELECT '动物','小猫','2001-01-11' UNION ALL SELECT '动物','小猫','2001-01-12' UNION ALL SELECT '动物','小猫','2001-01-12' UNION ALL SELECT '动物','小猫','2001-01-19' UNION ALLSELECT '家俱','餐桌','2001-01-21' UNION ALL SELECT '家俱','餐桌','2001-01-22' UNION ALL SELECT '家俱','餐桌','2001-01-23' UNION ALL SELECT '家俱','餐桌','2001-01-23' UNION ALL SELECT '家俱','餐桌','2001-01-24' UNION ALLSELECT '家俱','椅子','2001-02-11' UNION ALL SELECT '家俱','椅子','2001-01-12' UNION ALL SELECT '家俱','椅子','2001-01-13' UNION ALL SELECT '家俱','椅子','2001-01-23' UNION ALL SELECT '家俱','椅子','2001-01-23' UNION ALL SELECT '家俱','椅子','2001-01-26' gowith t as ( select *,id = row_number() over (partition by 类别,名称 order by getdate()) from tb ) select *,(select max(id)%2 from t where 类别 = a.类别 and 名称 = a.名称)cn, (select max(id)/2 from t where 类别 = a.类别 and 名称 = a.名称)cm into #t from t aselect distinct 类别,名称,(case when cn = 1 then (select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1) else dateadd(dd,datediff(dd,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm) ,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1))/2 ,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1)) end)创建时间 from #t bdrop table tb,#t 类别 名称 创建时间 ---------- ---------- ----------------------- 动物 小狗 2001-01-02 00:00:00.000 动物 小猫 2001-01-12 00:00:00.000 家俱 餐桌 2001-01-23 00:00:00.000 家俱 椅子 2001-01-28 00:00:00.000(4 行受影响)
declare @Test TABLE (类别 VARCHAR(MAX),名称 VARCHAR(MAX),创建时间 CHAR(10)) INSERT @Test SELECT '动物','小狗','2001-01-01' UNION ALL SELECT '动物','小狗','2001-01-02' UNION ALL SELECT '动物','小狗','2001-01-03' UNION ALLSELECT '动物','小猫','2001-01-11' UNION ALL SELECT '动物','小猫','2001-01-12' UNION ALL SELECT '动物','小猫','2001-01-12' UNION ALL SELECT '动物','小猫','2001-01-19' UNION ALLSELECT '家俱','餐桌','2001-01-21' UNION ALL SELECT '家俱','餐桌','2001-01-22' UNION ALL SELECT '家俱','餐桌','2001-01-23' UNION ALL SELECT '家俱','餐桌','2001-01-23' UNION ALL SELECT '家俱','餐桌','2001-01-24' UNION ALLSELECT '家俱','椅子','2001-02-11' UNION ALL SELECT '家俱','椅子','2001-01-12' UNION ALL SELECT '家俱','椅子','2001-01-13' UNION ALL SELECT '家俱','椅子','2001-01-23' UNION ALL SELECT '家俱','椅子','2001-01-23' UNION ALL SELECT '家俱','椅子','2001-01-26' select *, row_number() over(partition by 类别,名称 order by 创建时间) from @Test
;with cte_test_1 as ( select 类别, 名称, 创建时间, ID = ROW_NUMBER() over(partition by 类别, 名称 order by 创建时间) from #test ), cte_test_2 as ( select 类别, 名称, tcount = COUNT(*) from #test group by 类别, 名称 )select b.类别, b.名称, 创建时间 = convert(char(10), case when (b.tcount%2) = 1 then (select 创建时间 from cte_test_1 where 类别 = b.类别 and 名称 = b.名称 and ID = (b.tcount/2+1)) else DATEADD(DAY,DATEDIFF(DAY, (select 创建时间 from cte_test_1 where 类别 = b.类别 and 名称 = b.名称 and ID = (b.tcount/2)), (select 创建时间 from cte_test_1 where 类别 = b.类别 and 名称 = b.名称 and ID = (b.tcount/2+1)))/2, (select 创建时间 from cte_test_1 where 类别 = b.类别 and 名称 = b.名称 and ID = (b.tcount/2))) end, 120)
--1
select *,id = row_number() over (partition by 类别,名称 order by 创建时间)
from tb
CREATE TABLE #Test (类别 VARCHAR(10),名称 VARCHAR(10),创建时间 CHAR(10),)
INSERT #Test
SELECT '动物','小狗','2001-01-01' UNION ALL
SELECT '动物','小狗','2001-01-02' UNION ALL
SELECT '动物','小狗','2001-01-03' UNION ALLSELECT '动物','小猫','2001-01-11' UNION ALL
SELECT '动物','小猫','2001-01-12' UNION ALL
SELECT '动物','小猫','2001-01-12' UNION ALL
SELECT '动物','小猫','2001-01-19' UNION ALLSELECT '家俱','餐桌','2001-01-21' UNION ALL
SELECT '家俱','餐桌','2001-01-22' UNION ALL
SELECT '家俱','餐桌','2001-01-23' UNION ALL
SELECT '家俱','餐桌','2001-01-23' UNION ALL
SELECT '家俱','餐桌','2001-01-24' UNION ALLSELECT '家俱','椅子','2001-02-11' UNION ALL
SELECT '家俱','椅子','2001-01-12' UNION ALL
SELECT '家俱','椅子','2001-01-13' UNION ALL
SELECT '家俱','椅子','2001-01-23' UNION ALL
SELECT '家俱','椅子','2001-01-23' UNION ALL
SELECT '家俱','椅子','2001-01-26' select *,row_number() over (partition by 类别,名称 order by 创建时间) as 自定义编号 from #Test
drop table #Test/*
类别 名称 创建时间 自定义编号
---------- ---------- ---------- --------------------
动物 小狗 2001-01-01 1
动物 小狗 2001-01-02 2
动物 小狗 2001-01-03 3
动物 小猫 2001-01-11 1
动物 小猫 2001-01-12 2
动物 小猫 2001-01-12 3
动物 小猫 2001-01-19 4
家俱 餐桌 2001-01-21 1
家俱 餐桌 2001-01-22 2
家俱 餐桌 2001-01-23 3
家俱 餐桌 2001-01-23 4
家俱 餐桌 2001-01-24 5
家俱 椅子 2001-01-12 1
家俱 椅子 2001-01-13 2
家俱 椅子 2001-01-23 3
家俱 椅子 2001-01-23 4
家俱 椅子 2001-01-26 5
家俱 椅子 2001-02-11 6
*/
编号 from tb
--2
with t as
(
select *,id = row_number() over (partition by 类别,名称 order by 创建时间)
from tb
)
select *,(select max(id)%2 from t where 类别 = a.类别 and 名称 = a.名称)cn,
(select max(id)/2 from t where 类别 = a.类别 and 名称 = a.名称)cm
into #t
from t aselect 类别,名称,(case when cn = 1
then (select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1)
else dateadd(dd,datediff(dd,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm),(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1))/2,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1)))
from #t b
CREATE TABLE tb (类别 VARCHAR(10),名称 VARCHAR(10),创建时间 CHAR(10),)
INSERT tb
SELECT '动物','小狗','2001-01-01' UNION ALL
SELECT '动物','小狗','2001-01-02' UNION ALL
SELECT '动物','小狗','2001-01-03' UNION ALLSELECT '动物','小猫','2001-01-11' UNION ALL
SELECT '动物','小猫','2001-01-12' UNION ALL
SELECT '动物','小猫','2001-01-12' UNION ALL
SELECT '动物','小猫','2001-01-19' UNION ALLSELECT '家俱','餐桌','2001-01-21' UNION ALL
SELECT '家俱','餐桌','2001-01-22' UNION ALL
SELECT '家俱','餐桌','2001-01-23' UNION ALL
SELECT '家俱','餐桌','2001-01-23' UNION ALL
SELECT '家俱','餐桌','2001-01-24' UNION ALLSELECT '家俱','椅子','2001-02-11' UNION ALL
SELECT '家俱','椅子','2001-01-12' UNION ALL
SELECT '家俱','椅子','2001-01-13' UNION ALL
SELECT '家俱','椅子','2001-01-23' UNION ALL
SELECT '家俱','椅子','2001-01-23' UNION ALL
SELECT '家俱','椅子','2001-01-26'
gowith t as
(
select *,id = row_number() over (partition by 类别,名称 order by getdate())
from tb
)
select *,(select max(id)%2 from t where 类别 = a.类别 and 名称 = a.名称)cn,
(select max(id)/2 from t where 类别 = a.类别 and 名称 = a.名称)cm
into #t
from t aselect distinct 类别,名称,(case when cn = 1
then (select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1)
else dateadd(dd,datediff(dd,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm)
,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1))/2
,(select 创建时间 from #t where 类别 = b.类别 and 名称 = b.名称 and id = cm + 1)) end)创建时间
from #t bdrop table tb,#t
类别 名称 创建时间
---------- ---------- -----------------------
动物 小狗 2001-01-02 00:00:00.000
动物 小猫 2001-01-12 00:00:00.000
家俱 餐桌 2001-01-23 00:00:00.000
家俱 椅子 2001-01-28 00:00:00.000(4 行受影响)
INSERT @Test
SELECT '动物','小狗','2001-01-01' UNION ALL
SELECT '动物','小狗','2001-01-02' UNION ALL
SELECT '动物','小狗','2001-01-03' UNION ALLSELECT '动物','小猫','2001-01-11' UNION ALL
SELECT '动物','小猫','2001-01-12' UNION ALL
SELECT '动物','小猫','2001-01-12' UNION ALL
SELECT '动物','小猫','2001-01-19' UNION ALLSELECT '家俱','餐桌','2001-01-21' UNION ALL
SELECT '家俱','餐桌','2001-01-22' UNION ALL
SELECT '家俱','餐桌','2001-01-23' UNION ALL
SELECT '家俱','餐桌','2001-01-23' UNION ALL
SELECT '家俱','餐桌','2001-01-24' UNION ALLSELECT '家俱','椅子','2001-02-11' UNION ALL
SELECT '家俱','椅子','2001-01-12' UNION ALL
SELECT '家俱','椅子','2001-01-13' UNION ALL
SELECT '家俱','椅子','2001-01-23' UNION ALL
SELECT '家俱','椅子','2001-01-23' UNION ALL
SELECT '家俱','椅子','2001-01-26' select *, row_number() over(partition by 类别,名称 order by 创建时间) from @Test
(
select 类别, 名称, 创建时间, ID = ROW_NUMBER() over(partition by 类别, 名称 order by 创建时间) from #test
),
cte_test_2 as
(
select 类别, 名称, tcount = COUNT(*) from #test group by 类别, 名称
)select
b.类别, b.名称,
创建时间 = convert(char(10),
case when (b.tcount%2) = 1
then (select 创建时间 from cte_test_1 where 类别 = b.类别 and 名称 = b.名称 and ID = (b.tcount/2+1))
else DATEADD(DAY,DATEDIFF(DAY,
(select 创建时间 from cte_test_1 where 类别 = b.类别 and 名称 = b.名称 and ID = (b.tcount/2)),
(select 创建时间 from cte_test_1 where 类别 = b.类别 and 名称 = b.名称 and ID = (b.tcount/2+1)))/2,
(select 创建时间 from cte_test_1 where 类别 = b.类别 and 名称 = b.名称 and ID = (b.tcount/2)))
end, 120)
from cte_test_2 b