请问如何合并这些数据库记录？谢谢！

liuhong2003 2009-10-30 03:16:43
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
-------------------------------------------------------------------------------------



...全文
135 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
liuhong2003 2009-10-30
  • 打赏
  • 举报
 回复
谢谢各位,这个多了一条记录
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, 3



SELECT 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
michaelmodna 2009-10-30
  • 打赏
  • 举报
 回复
顶起，学习中！！！
gsk09 2009-10-30
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 gsk09 的回复:]
SQL code--> Test data : @tdeclare@ttable ([cusid]int,[pid]int,[st]int,[price] numeric(5,4),[begindate]datetime,[enddate]datetime,[source]int,[type]int)insertinto@tselect1101002,1021,8,2.3000,'2009-10-¡­
[/Quote]

有漏洞
子陌红尘 2009-10-30
  • 打赏
  • 举报
 回复
楼主把 [price] 作为匹配条件也放在SQL语句中即可，之前的SQL大作只比对了前两、三个字段。
liuhong2003 2009-10-30
  • 打赏
  • 举报
 回复 
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', 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, 3



SELECT 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
lang071234 2009-10-30
  • 打赏
  • 举报
 回复
牛人好多。。菜菜路过。学习。
gsk09 2009-10-30
  • 打赏
  • 举报
 回复 


--> 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,3



select 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
子陌红尘 2009-10-30
  • 打赏
  • 举报
 回复
楼上的SQL处理更严谨，考虑了两个时间段存在部分时间交集的情况。
csdyyr 2009-10-30
  • 打赏
  • 举报
 回复 
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.3000', '2009-10-26', '2009-10-28', 13079, 3 UNION ALL 

SELECT '1101002', '1021', 8, '2.3000', '2009-10-28', '2009-10-31', 13079, 3



SELECT 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
bancxc 2009-10-30
  • 打赏
  • 举报
 回复
钻钻出场 不一般啊
华夏小卒 2009-10-30
  • 打赏
  • 举报
 回复
潇湘大侠出场

。这样的代码，我上次就学习了半天。最后过2天又忘了
--小F-- 2009-10-30
  • 打赏
  • 举报
 回复
我的神哦 钻钻
子陌红尘 2009-10-30
  • 打赏
  • 举报
 回复 
declare @t table(cusid int,pid int,st int,price money,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 

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

*/
--小F-- 2009-10-30
  • 打赏
  • 举报
 回复
还是错误了 等楼下来好了 都不好意思了
--小F-- 2009-10-30
  • 打赏
  • 举报
 回复 
select 

  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
--小F-- 2009-10-30
  • 打赏
  • 举报
 回复 
select 

  cusid,pid,st,price,max(begindate),max(enddate),source,type 

from 

  tb

group by

   cusid,pid,st,price,source,type
用 nebula_dart_gdbc 在移动设备玩图数据库，泰酷辣！
当 Dart 遇上数据库，如何实现二者的相结合，让用户在移动端操作数据库呢？本文给出了它的答案，先搞定数据库数据访问接口，再搞定数据传输，最后是封装连接，一切就那么简单。
MySQL数据库面试题（2020最新版）
文章目录数据库基础知识为什么要使用数据库什么是SQL？什么是MySQL?数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式？分别有什么区别？数据类型mysql有哪些数据类型引擎MySQL存储...
MySQL多表关联查询效率高点还是多次单表查询效率高，为什么？
谢谢！ 高手解答： 先说效率哪个更高：A,B两个表数据规模十几万，数据规模都不大，单机MySQL够用了。 在单机的基础上要关联两表的数据， 先说一个极端情况，A,B两个表都没有索引，并且关联是笛卡尔积，那关联结果会...
网站镜像-文件镜像方法介绍-数据库镜像提问?
网站镜像：目的将一个网站（数据库和页面都在同一台服务器上）镜像到另一台服务器上（同样，网站页面和数据库都在这个服务器上），做到网站文件的定时同步，数据库数据时时同步。并且两个网站都要能正常访问。镜像...
oracle数据库纪录顺序,oracle怎样按物理顺序提取记录？(from itpub)-数据库专栏,ORACLE...
加入资料库 上一主题 下一主题 作者主题 x_x长期郁闷中……注册日期: 2002 oct来自: 福州发帖数量: 458怎样按物理顺序提取记录？rtselect * from tabname和select /*+first_rows*/ from tabname都是吗？谢谢________...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章