cusid pid st price begindate enddate source type
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-01 2009-10-10 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-10 2009-10-11 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-12 2009-10-20 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-26 2009-10-28 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-28 2009-10-31 13079 3
-------------------------------------------------------------------------------------
不用游标,如何把上面数据合并为两条记录?有办法吗?如下:
cusid pid st price begindate enddate source type
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-01 2009-10-20 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-26 2009-10-31 13079 3
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-01 2009-10-10 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-10 2009-10-11 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-12 2009-10-20 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-26 2009-10-28 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-28 2009-10-31 13079 3
-------------------------------------------------------------------------------------
不用游标,如何把上面数据合并为两条记录?有办法吗?如下:
cusid pid st price begindate enddate source type
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-01 2009-10-20 13079 3
-------------------------------------------------------------------------------------
1101002 1021 8 2.3000 2009-10-26 2009-10-31 13079 3
-------------------------------------------------------------------------------------
解决方案 »
- SQL server服务无法启动
- MS SQL 在学习版创建域约束
- sqlserver如何选取出用逗号隔开的结果
- 大家看看这个存储过程怎么错了?
- 我的SQL算法执行效率好低啊,数据库无响应,快进来帮帮我
- 如何分离数据库后,可以对库文件进行读写操作?
- 禁用 复制 出错, distribution 库 正在使用?推订阅时,在服务器执行2次, 拉式 订阅时 订阅服务器 上 运行一次快照代理就行?如果有 f
- 怎么通过C表关联A、B表,找出A、B表中相同的数据
- 哪里有SQL Sever 2000或7.0下载?
- 访问sql server的iis虚拟目录时,怎样才能在url中输入用户名密码才能访问网站呀?
- 征数据库优化方案
- sql多表查询
cusid,pid,st,price,max(begindate),max(enddate),source,type
from
tb
group by
cusid,pid,st,price,source,type
cusid,pid,st,price,max(begindate)begindate,max(enddate)enddate,source,type
from
tb
group by
cusid,pid,st,price,source,type
union all
select
cusid,pid,st,price,min(begindate)enddate,min(enddate)enddate,source,type
from
tb
group by
cusid,pid,st,price,source,type
insert into @t select 1101002,1021,8,2.3000,'2009-10-01','2009-10-10',13079,3
insert into @t select 1101002,1021,8,2.3000,'2009-10-10','2009-10-11',13079,3
insert into @t select 1101002,1021,8,2.3000,'2009-10-12','2009-10-20',13079,3
insert into @t select 1101002,1021,8,2.3000,'2009-10-26','2009-10-28',13079,3
insert into @t select 1101002,1021,8,2.3000,'2009-10-28','2009-10-31',13079,3 select
a.cusid,a.pid,a.st,a.price,a.begindate,min(b.enddate) as enddate,a.source,a.type
from
(select
t.*
from
@t t
where
not exists(select
1
from
@t
where
cusid=t.cusid and pid=t.pid and datediff(dd,enddate,t.begindate) between 0 and 1)) a,
(select
t.*
from
@t t
where
not exists(select
1
from
@t
where
cusid=t.cusid and pid=t.pid and datediff(dd,begindate,t.enddate) between 0 and 1)) b
where
a.cusid=b.cusid and a.pid=b.pid and a.begindate<b.enddate
group by
a.cusid,a.pid,a.st,a.price,a.begindate,a.source,a.type/*
cusid pid st price begindate enddate source type
----------- ----------- ----------- --------------------- ---------------------------- ---------------------------- ----------- -----------
1101002 1021 8 2.3000 2009-10-01 00:00:00.000 2009-10-20 00:00:00.000 13079 3
1101002 1021 8 2.3000 2009-10-26 00:00:00.000 2009-10-31 00:00:00.000 13079 3
*/
INSERT @TB
SELECT '1101002', '1021', 8, '2.3000', '2009-10-01', '2009-10-10', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-10', '2009-10-11', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-12', '2009-10-20', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-26', '2009-10-28', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-28', '2009-10-31', 13079, 3SELECT A.cusid,A.pid,A.st,A.price,MIN(A.begindate) as begindate,MIN(B.enddate) AS enddate,A.source,A.type
FROM (SELECT * FROM @TB AS T WHERE NOT EXISTS(SELECT * FROM @TB AS T2 WHERE cusid=T.cusid AND DATEADD(DAY,-1,T.begindate) BETWEEN T2.begindate AND T2.enddate)) AS A,
(SELECT * FROM @TB AS T WHERE NOT EXISTS(SELECT * FROM @TB AS T2 WHERE cusid=T.cusid AND DATEADD(DAY,-1,T2.begindate) BETWEEN T.begindate AND T.enddate)) AS B
WHERE A.begindate<B.begindate
GROUP BY A.cusid,A.pid,A.st,A.price,A.source,A.type,A.begindate
--> Test data : @t
declare @t table ([cusid] int,[pid] int,[st] int,[price] numeric(5,4),[begindate] datetime,[enddate] datetime,[source] int,[type] int)
insert into @t
select 1101002,1021,8,2.3000,'2009-10-01','2009-10-10',13079,3 union all
select 1101002,1021,8,2.3000,'2009-10-10','2009-10-11',13079,3 union all
select 1101002,1021,8,2.3000,'2009-10-12','2009-10-20',13079,3 union all
select 1101002,1021,8,2.3000,'2009-10-26','2009-10-28',13079,3 union all
select 1101002,1021,8,2.3000,'2009-10-28','2009-10-31',13079,3select t1.cusid
,t1.pid
,t1.st
,t1.price
,t1.begindate
,(select top 1 t4.enddate
from @t t4
left join @t t3
on (t4.enddate = t3.begindate
or t4.begindate = dateadd(day,-1,t3.enddate))
where t1.enddate<= t4.begindate
and (t3.begindate is null)
order by t4.begindate) begindate
from @t t1
left join @t t2
on (t1.begindate = t2.enddate
or t1.begindate = dateadd(day,1,t2.enddate))
where t2.begindate is null
cusid pid st price begindate begindate
----------- ----------- ----- ------- ---------- -----------
1101002 1021 8 2.3000 2009-10-01 2009-10-20
1101002 1021 8 2.3000 2009-10-26 2009-10-31
INSERT @TB
SELECT '1101002', '1021', 8, '2.3000', '2009-10-01', '2009-10-10', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-10', '2009-10-11', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-12', '2009-10-20', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.4000', '2009-10-21', '2009-10-25', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-26', '2009-10-28', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-28', '2009-10-31', 13079, 3SELECT A.cusid,A.pid,A.st,A.price,MIN(A.begindate) as begindate,MIN(B.enddate) AS enddate,A.source,A.type
FROM (SELECT * FROM @TB AS T WHERE NOT EXISTS(SELECT * FROM @TB AS T2 WHERE cusid=T.cusid AND DATEADD(DAY,-1,T.begindate) BETWEEN T2.begindate AND T2.enddate)) AS A,
(SELECT * FROM @TB AS T WHERE NOT EXISTS(SELECT * FROM @TB AS T2 WHERE cusid=T.cusid AND DATEADD(DAY,-1,T2.begindate) BETWEEN T.begindate AND T.enddate)) AS B
WHERE A.begindate<B.begindate
GROUP BY A.cusid,A.pid,A.st,A.price,A.source,A.type,A.begindate谢谢各位,这个多了一条记录
SELECT '1101002', '1021', 8, '2.4000', '2009-10-21', '2009-10-25', 13079, 3 UNION ALL 就会出现这样的结果:
1101002 1021 8 2.3000 2009-10-01 00:00:00.000 2009-10-31 00:00:00.000 13079 3
SELECT '1101002', '1021', 8, '2.4000', '2009-10-21', '2009-10-25', 13079, 3 UNION ALL
--------------------------------------
这条记录错了price不一样的话,Source+Type就不会相同
--------------------------------------
最后修改如下:
各位看看还有什么漏洞
DECLARE @TB TABLE([cusid] VARCHAR(7), [pid] VARCHAR(4), [st] INT, [price] VARCHAR(6), [begindate] DATETIME, [enddate] DATETIME, [source] INT, [type] INT)
INSERT @TB
SELECT '1101002', '1021', 8, '2.3000', '2009-10-01', '2009-10-10', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-10', '2009-10-11', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-12', '2009-10-20', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.4000', '2009-10-21', '2009-10-25', 13080, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-26', '2009-10-28', 13079, 3 UNION ALL
SELECT '1101002', '1021', 8, '2.3000', '2009-10-28', '2009-10-31', 13079, 3SELECT A.cusid
, A.pid
, A.st
, A.price
, MIN(A.begindate) AS begindate
, MIN(B.enddate) AS enddate
, A.source
, A.type
FROM (SELECT *
FROM @TB AS T
WHERE NOT EXISTS
(SELECT *
FROM @TB AS T2
WHERE cusid = T .cusid
AND pid = T .pid
AND st = T .st
AND price = T .price
AND source = T .source
AND [type] = T .type
AND DATEADD(DAY, - 1, T .begindate) BETWEEN T2.begindate AND T2.enddate)) AS A,
(SELECT *
FROM @TB AS T
WHERE NOT EXISTS
(SELECT *
FROM @TB AS T2
WHERE cusid = T .cusid
AND pid = T .pid
AND st = T .st
AND price = T .price
AND source = T .source
AND [type] = T .type
AND DATEADD(DAY, - 1, T2.begindate) BETWEEN T .begindate AND T .enddate)) AS B
WHERE A.begindate <= B.begindate
GROUP BY A.cusid, A.pid, A.st, A.price, A.source, A.type, A.begindate