如何使用row_number、延迟/引导、联接SQL解决问题
如何使用row_number、延迟/引导、联接SQL解决问题
提问于 2019-09-05 17:35:23
在sql中，我是全新的，但是我只需要使用row_number、延迟/引导、联接来解决这个问题。我已经为这个问题挣扎了两天了，我就是不能正确地解决它。
我们有一个包含数据的表，您需要从以下方面获得答案：
Client 2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E这是我的密码
if object_id('tempdb..#c1') is not null
drop table #c1
create table #c1 (datemessage datetime2 ,id_tasks nvarchar(255),Whowritedmessage nvarchar(255));
insert into #c1
values
('2017-11-01 04:59:07.614','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
('2017-11-01 05:05:17.500','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
('2017-11-01 05:06:53.909','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
('2017-11-01 05:06:53.909','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
('2017-11-01 05:07:18.702','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
('2017-11-01 05:07:43.128','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
('2017-11-01 05:07:59.578','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
('2017-11-01 05:09:55.063','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
('2017-11-01 05:14:12.587','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
('2017-11-01 05:14:56.369','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
('2017-11-01 06:06:56.108','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
('2017-11-01 06:07:07.279','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
('2017-11-01 06:21:17.548','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
('2017-11-01 06:21:38.428','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent')
SELECT
tabC.whowritedmessage,
tabA.datemessage,
tabA.answer,
tabA.whowritedmessage,
tabA.id_tasks
FROM(SELECT
ROW_NUMBER() OVER (PARTITION BY whowritedmessage ORDER BY datemessage) as number,
whowritedmessage
FROM #c1
where whowritedmessage ='Client') as tabC
join (SELECT
top 50 percent
ROW_NUMBER() OVER (PARTITION BY whowritedmessage ORDER BY datemessage) as number,
LEAD(datemessage,7) OVER (ORDER BY whowritedmessage) as datemessage,
datemessage as answer,
whowritedmessage,
id_tasks
FROM #c1
) as tabA on tabA.number = tabC.number我们有一个包含数据的表，您需要从以下方面获得答案：
Client 2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E但是我经常得到7行，虽然应该有5行，因为如果客户端使用相同的下一行，则不考虑这一点，与代理也是一样的。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-09-05 18:24:22
看看这个剧本-
WITH CTE AS
(
SELECT *,
LAG(whowritedmessage) OVER (ORDER BY datemessage) who_lag
FROM #c1
)
SELECT whowritedmessage,
datemessage,
ans,
ISNULL(who_lag,'Agent') whowritedmessage2,
id_tasks
FROM
(
SELECT *,
LEAD(datemessage) OVER (ORDER BY datemessage) ans,
LEAD(whowritedmessage) OVER (ORDER BY datemessage) who_lead
FROM CTE
WHERE (whowritedmessage <> who_lag OR who_lag IS NULL)
)A
WHERE whowritedmessage = 'Client'输出是-
whowritedmessage datemessage ans whowritedmessage2 id_tasks
Client 2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E改变了：为了更好的理解，我在这里增加了1个CTE，输出与上面的脚本相同。
WITH CTE AS
(
SELECT *,
LAG(whowritedmessage) OVER (ORDER BY datemessage) who_lag
FROM #c1
),
CTE2 AS
(
SELECT whowritedmessage, datemessage,
LEAD(datemessage) OVER (ORDER BY datemessage) ans,
ISNULL(who_lag,'Agent') whowritedmessage2,
id_tasks
FROM CTE
WHERE (whowritedmessage <> who_lag OR who_lag IS NULL)
)
--SELECT * FROM CTE
--SELECT * FROM CTE2
SELECT * FROM CTE2 WHERE whowritedmessage = 'Client'您可以一个接一个地运行SELECT语句(现在注释)来了解数据是如何一步一步地转换的。记住，第三条SELECT语句是您的最终脚本，其他2条注释SELECT仅供您检查。希望这能帮助你理解整个过程。
Stack Overflow用户
发布于 2019-09-06 05:01:08
SELECT t2.whowritedmessage,t2.datemessage,t2.answer,ISNULL(this_lag,'Agent') as whowritedmessage2,t2.id_tasks
FROM
(SELECT *,
LEAD(datemessage) over (order by datemessage) answer,
LEAD(whowritedmessage) over (order by datemessage) this_lead
FROM
(SELECT *,
LAG(whowritedmessage) over (order by datemessage) this_lag
FROM #c1) as t1
Where whowritedmessage <> this_lag or this_lag IS NULL) as t2
Where whowritedmessage = 'Client'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57810522
复制相关文章
相似问题
腾讯云开发者
