本文介绍了在 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;更多推荐
在 SQL 中替代 STRING
发布评论