在 SQL 中替代 STRING

本文介绍了在 SQL 中替代 STRING_AGG的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张如下表

| activityName | UserID | deviceID | createdDate | |------------------------------------------------------------| | ON | 1 | adddsad |2020-01-09 00:02:59.477 | | OFF | 1 | adddsad |2020-01-09 00:50:39.857 | | ON | 2 | bdddsad |2020-01-09 00:51:11.480 | | OFF | 2 | bdddsad |2020-01-09 00:51:19.450 |

当我像这样使用 STRING_AGG 时，这是准确的并返回所需的结果

when I use STRING_AGG like this which is accurate and returns the desired result

SELECT STRING_AGG(activityName + ' - ' + CONVERT(varchar, createdDate), ' | ') AS tag, deviceID, UserID FROM (SELECT tag, deviceID, UserID FROM tbl_DailyLogMaster WHERE CONVERT(date, createdDate) = CONVERT(date, GETDATE()) GROUP BY userID) a GROUP BY UserID;

它会像这样返回

| tag | deviceID | UserID | |------------------------------------------------------------------------------------| | ON - 2020-01-09 00:02:59.477 | OFF - 2020-01-09 00:50:39.857 | adddsad | 1 | | ON - 2020-01-09 00:51:11.480 | OFF - 2020-01-09 00:51:19.450 | bdddsad | 2 |

在生产环境中，我运行了 SQL Server 2014，不得不为 STRING_AGG 开发替代方案，旧版本不支持此方案

On production I have SQL Server 2014 running and had to work on alternative for STRING_AGG which is not supported on older version

这是我创建的替代方案

SELECT deviceID, UserID, STUFF((SELECT activityName + ' - ' + CONVERT(varchar, createdDate) FROM tbl_DailyLogMaster WHERE userID = tbl_DailyLogMaster.UserID AND CONVERT(date, createdDate) = CONVERT(date, GETDATE()) ORDER BY UserID FOR XML PATH('')),1,1,'') AS tag FROM tbl_DailyLogMaster WHERE CONVERT(date, createdDate) = CONVERT(date, GETDATE()) GROUP BY UserID, deviceID, UserID, createdDate, activityName;

它像这样返回

| tag | deviceID | UserID | |---------------------------------------------------------------------------------------------------------------------------------------------------------| | N - Jan 9 2020 12:51AMOFF - Jan 9 2020 12:51AMON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857 | adddsad | 1 | | N - Jan 9 2020 12:51AMOFF - Jan 9 2020 12:51AMON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857 | adddsad | 1 | | N - Jan 9 2020 12:51AMOFF - Jan 9 2020 12:51AMON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857 | bdddsad | 2 | | N - Jan 9 2020 12:51AMOFF - Jan 9 2020 12:51AMON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857 | bdddsad | 2 |

我在第二次查询时做错了什么?

What I am doing wrong with second query?

推荐答案

有些盲目的猜测，但我认为这是正确的答案.您需要确保子查询正确关联:

A some what blind guess, but I think this is the correct answer. you needed to ensure the subquery was properly correlated:

SELECT deviceID, UserID, STUFF((SELECT ' | ' + sq.activityName + ' - ' + CONVERT(varchar(20),sq.createdDate, 0) FROM tbl_DailyLogMaster sq WHERE DLM.UserID = sq.UserId AND DLM.DeviceID = sq.DeviceID AND sq.createdDate >= CONVERT(date, GETDATE()) AND sq.createdDate < DATEADD(DAY, 1, CONVERT(date, GETDATE())) ORDER BY CreatedDate FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,3,'') AS tag --As yuou have no leading separator, no need for STUFF FROM tbl_DailyLogMaster DLM WHERE DLM.createdDate >= CONVERT(date, GETDATE()) AND DLM.createdDate < DATEADD(DAY, 1, CONVERT(date, GETDATE())) GROUP BY UserID, DeviceID;