在 SQL 中替代 STRING_AGG [英] Alternative to STRING_AGG in with SQL
本文介绍了在 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_AGG的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文