时间戳

首页 > 解决方案 > 使用 group by 子句中的多行作为单个记录

sql - 使用 group by 子句中的多行作为单个记录

问题描述

我试图在一行中加入多个列，但没有得到所需的输出。STUFF 中的查询返回所有数据，但我只想要与外部条件匹配的数据。任何帮助，将不胜感激

这是一个小提琴

SELECT
COUNT (*) AS COUNT,
A.AlarmType,
A.DeviceNumber,
Latlong = STUFF (
    (
        SELECT
            ',' + CONVERT (VARCHAR, B.Latitude)
        FROM
            TableAlarmPacket B
        WHERE
            B.DeviceNumber = A.DeviceNumber AND B.AlarmType = A.AlarmType 
        FOR XML PATH('')), 1, LEN(','), '')

FROM
TableAlarmPacket AS A
WHERE
A.DeviceNumber IN ('213WP2017006316')
AND A.AlarmType IN (4)
AND (
A.GPSDateTime BETWEEN '2018-09-01 14:00:00'
AND '2018-09-03 13:59:59'
)
GROUP BY

A.DeviceNumber,
A.AlarmType,
dateadd(
    MINUTE,
    datediff(MINUTE, 0, A.GPSDateTime) / 10 * 10,
    0
)
ORDER BY
MAX(A.Id) DESC

这就是我得到的。

6   4   213WP2017006316 -38.0348,-38.0353,-38.019,-37.9938,-37.9756,-37.9869,-38.0015
3   4   213WP2017006316 -38.0348,-38.0353,-38.019,-37.9938,-37.9756,-37.9869,-38.0015
2   4   213WP2017006316 -38.0348,-38.0353,-38.019,-37.9938,-37.9756,-37.9869,-38.0015
2   4   213WP2017006316 -38.0348,-38.0353,-38.019,-37.9938,-37.9756,-37.9869,-38.0015
2   4   213WP2017006316 -38.0348,-38.0353,-38.019,-37.9938,-37.9756,-37.9869,-38.0015
3   4   213WP2017006316 -38.0348,-38.0353,-38.019,-37.9938,-37.9756,-37.9869,-38.0015

预期输出是

6   4   213WP2017006316 -37.6735,-37.6855,-37.6907,-37.6973,-37.7275,-37.7315
3   4   213WP2017006316 -37.6782,-37.6776,-37.6712
2   4   213WP2017006316 -37.7074,-37.7068
2   4   213WP2017006316 -37.76,-37.7356
2   4   213WP2017006316 -37.8276,-37.8279
3   4   213WP2017006316 -37.9271,-37.893,-37.8856

第一个字段是计数的。我的计数和 Latlong 字段没有反映相同的数据。

标签： sqlsql-server

解决方案

您需要GPSDateTime在STUFF查询中添加条件，如下所示：

SELECT
    COUNT (*) AS COUNT,
    A.AlarmType,
    A.DeviceNumber,
    Latlong = STUFF (
        (
            SELECT
                ',' + CONVERT (VARCHAR, B.Latitude)
            FROM
                TableAlarmPacket B
            WHERE
                B.DeviceNumber = A.DeviceNumber AND B.AlarmType = A.AlarmType 
    and dateadd(
        MINUTE,
        datediff(MINUTE, 0, A.GPSDateTime) / 10 * 10,
        0
    ) = dateadd(
        MINUTE,
        datediff(MINUTE, 0, B.GPSDateTime) / 10 * 10,
        0
    )

            FOR XML PATH('')), 1, LEN(','), '')

FROM
    TableAlarmPacket AS A
WHERE
    A.DeviceNumber IN ('213WP2017006316')
AND A.AlarmType IN (4)
AND (
    A.GPSDateTime BETWEEN '2018-09-01 14:00:00'
    AND '2018-09-03 13:59:59'
)
GROUP BY

    A.DeviceNumber,
    A.AlarmType,
    dateadd(
        MINUTE,
        datediff(MINUTE, 0, A.GPSDateTime) / 10 * 10,
        0
    )
ORDER BY
    MAX(A.Id) DESC

SQLFiddle

推荐阅读