I have a stored procedure with the following query:
我有一个存储过程与以下查询:
SELECT (sum(addition)) AS [COUNT],
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102)
ORDER BY CONVERT(VARCHAR(12),CREATED,102)
I need to do a union all
, so I could get sum of results but from TWO tables, and I want the result to be grouped by and ordered by the same way.
我需要做一个联合所有,所以我可以从两个表中得到结果的总和,并且我希望结果按照相同的方式进行分组和排序。
This doesn't work:
这不起作用:
SELECT (sum(addition)) AS [COUNT],
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102)
ORDER BY CONVERT(VARCHAR(12),CREATED,102)
UNION ALL
SELECT (sum(addition)) AS [COUNT],
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_TWO]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102)
ORDER BY CONVERT(VARCHAR(12),CREATED,102)
I want to order the overall result, and group it by the date.
我想订购整体结果,并按日期分组。
1 个解决方案
#1
13
You could apply the group by
and order by
after the union all
:
你可以在工会之后申请分组和排序:
SELECT (SUM(addition)) AS [COUNT], MAX([date]) AS [max_date]
FROM (SELECT addition, CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE CONVERT(VARCHAR(12),CREATED,102)
BETWEEN CONVERT(date,@startdate) AND
CONVERT(date,@enddate)
AND [ServiceID]=@serid
UNION ALL
SELECT addition, (CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_TWO]
WHERE CONVERT(VARCHAR(12),CREATED,102)
BETWEEN CONVERT(date,@startdate) AND
CONVERT(date,@enddate)
AND [ServiceID]=@serid) t
GROUP BY [date]
ORDER BY 2
#1
13
You could apply the group by
and order by
after the union all
:
你可以在工会之后申请分组和排序:
SELECT (SUM(addition)) AS [COUNT], MAX([date]) AS [max_date]
FROM (SELECT addition, CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE CONVERT(VARCHAR(12),CREATED,102)
BETWEEN CONVERT(date,@startdate) AND
CONVERT(date,@enddate)
AND [ServiceID]=@serid
UNION ALL
SELECT addition, (CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_TWO]
WHERE CONVERT(VARCHAR(12),CREATED,102)
BETWEEN CONVERT(date,@startdate) AND
CONVERT(date,@enddate)
AND [ServiceID]=@serid) t
GROUP BY [date]
ORDER BY 2