TSQL Group by Error

Question

我有一个简单的查询，通过一些记录/XML解析，并为我做一些数学。然后，它返回一个包含我需要的数字的XML输出。

SELECT  A.[reimbursementDate],
                MONTH(A.[reimbursementDate]) as reimbursementMonth,
                YEAR(A.[reimbursementDate]) as reimbursementYear,
            (
                SELECT  SUM(ParamValues.x2.value('grandTotal[1]', 'INT')) as grandTotal,
                        SUM(ParamValues.x2.value('booksTotal[1]', 'INT')) as totalBooks,
                        SUM(ParamValues.x2.value('tuitionTotal[1]', 'INT')) as tuitionTotal
                FROM    A.[classXML].nodes('/courses') AS ParamValues(x2)
                FOR    XML PATH (''), TYPE, ELEMENTS
            )
        FROM   tuitionSubmissions as A
        WHERE A.[status] = 'Approved' AND YEAR(A.[reimbursementDate]) = YEAR(GETDATE())
        GROUP BY A.[reimbursementDate]
        FOR    XML PATH ('dataSet'), TYPE, ELEMENTS, ROOT ('root');

下面您将看到生成的输出:

            <root>
              <dataSet>
                <reimbursementMonth>6</reimbursementMonth>
                <reimbursementYear>2014</reimbursementYear>
                <grandTuition>1158</grandTuition>
                <totalBooks>863</totalBooks>
                <tuitionTotal>295</tuitionTotal>
              </dataSet>
              <dataSet>
                <reimbursementMonth>8</reimbursementMonth>
                <reimbursementYear>2014</reimbursementYear>
                <grandTuition>253</grandTuition>
                <totalBooks>143</totalBooks>
                <tuitionTotal>110</tuitionTotal>
              </dataSet>
              <dataSet>
                <reimbursementMonth>7</reimbursementMonth>
                <reimbursementYear>2014</reimbursementYear>
                <grandTuition>1488</grandTuition>
                <totalBooks>842</totalBooks>
                <tuitionTotal>986</tuitionTotal>
              </dataSet>
              <dataSet>
                <reimbursementMonth>6</reimbursementMonth>
                <reimbursementYear>2014</reimbursementYear>
                <grandTuition>616</grandTuition>
                <totalBooks>425</totalBooks>
                <tuitionTotal>191</tuitionTotal>
              </dataSet>
            </root>

我的问题是我想把月份/年份相同的数字组合起来。第一块和最后一块是第6个月所以它们应该和它们的总数合并在一起。有一个简单的方法，我可以从我的查询做到这一点吗?

Answer 1

如果您还想按月份和年份进行分组，则必须设置

SELECT  A.[reimbursementDate],
            MONTH(A.[reimbursementDate]) as reimbursementMonth,
            YEAR(A.[reimbursementDate]) as reimbursementYear,
        (
            SELECT  SUM(ParamValues.x2.value('grandTotal[1]', 'INT')) as grandTotal,
                    SUM(ParamValues.x2.value('booksTotal[1]', 'INT')) as totalBooks,
                    SUM(ParamValues.x2.value('tuitionTotal[1]', 'INT')) as tuitionTotal
            FROM    A.[classXML].nodes('/courses') AS ParamValues(x2)
            FOR    XML PATH (''), TYPE, ELEMENTS
        )
    FROM   tuitionSubmissions as A
    WHERE A.[status] = 'Approved' AND YEAR(A.[reimbursementDate]) = YEAR(GETDATE())
    GROUP BY A.[reimbursementDate]
            MONTH(A.[reimbursementDate])
            YEAR(A.[reimbursementDate])
    FOR    XML PATH ('dataSet'), TYPE, ELEMENTS, ROOT ('root');

或this，如果您只想按reimbursementDate

分组

SELECT
  [reimbursementDate],
   MONTH([reimbursementDate]) as reimbursementMonth,
   YEAR([reimbursementDate]) as reimbursementYear,
   XMLCol
FROM
(
    SELECT  A.[reimbursementDate],
        (
            SELECT  SUM(ParamValues.x2.value('grandTotal[1]', 'INT')) as grandTotal,
                    SUM(ParamValues.x2.value('booksTotal[1]', 'INT')) as totalBooks,
                    SUM(ParamValues.x2.value('tuitionTotal[1]', 'INT')) as tuitionTotal
            FROM    A.[classXML].nodes('/courses') AS ParamValues(x2)
            FOR    XML PATH (''), TYPE, ELEMENTS
        ) as XMLCol
    FROM   tuitionSubmissions as A
    WHERE A.[status] = 'Approved' AND YEAR(A.[reimbursementDate]) = YEAR(GETDATE())
    GROUP BY A.[reimbursementDate]
            MONTH(A.[reimbursementDate])
            YEAR(A.[reimbursementDate])
    FOR    XML PATH ('dataSet'), TYPE, ELEMENTS, ROOT ('root')
) T