SQL Server 2012（T-SQL）选择日期到月份组

问题描述：

美好的一天，SQL Server 2012（T-SQL）选择日期到月份组

我正在将每日数据汇总到月度数据。

TABLE daily_data: 
item_from, 
item_to, 
agg_day, 
average_day_rate, 
min_day_rate, 
max_day_rate, 
closing_day_rate 

TABLE monthly_data: 
item_from, 
item_to, 
agg_month, 
average_month_rate, 
min_month_rate, 
max_month_rate, 
closing_month_rate

选择数据：

DECLARE @rundate  DATE; 
DECLARE @yesterday  DATE; 
DECLARE @firstofmonth DATE; 

SET @runday  = CONVERT(DATE, GETDATE()) 
SET @yesterday  = CONVERT(DATE, DATEADD(dd,-1,GETDATE())) 
SET @firstofmonth = DATEADD(dd, 1, EOMONTH(@yesterday, -1)) 

SELECT 
    item_from, 
    item_to, 
    DATEADD(dd, 1, EOMONTH(agg_day, -1))          agg_month, 
    SUM(average_day_rate)/COUNT(DISTINCT(agg_day))       average_month_rate, 
    MIN(min_day_rate)              min_month_rate, 
    MAX(max_day_rate)              max_month_rate, 
    (SELECT closing_day_rate FROM daily_data WHERE agg_day = MAX(agg_day)) closing_month_rate 
FROM 
    daily_data 
WHERE 
    agg_day >= @firstofmonth 
    and agg_day < @runday 
GROUP BY 
    item_from, 
    item_to, 
    DATEADD(dd, 1, EOMONTH(agg_day, -1))

结果：聚合不应出现在WHERE子句中，除非它是在含有HAVING子句或选择列表在子查询中，并且列被聚集的是外部参考。

我尝试过在组中使用agg-day，改变了在哪里有一个，但没有任何成功。在PL-SQL中，我将使用

SELECT 
    item_from, 
    item_to, 
    trunc(agg_day, MONTH)             agg_month, 
    SUM(average_day_rate)/COUNT(DISTINCT(agg_day))       average_month_rate, 
    MIN(min_day_rate)              min_month_rate, 
    MAX(max_day_rate)              max_month_rate, 
    (SELECT closing_day_rate FROM daily_data WHERE agg_day = MAX(agg_day)) closing_month_rate 
FROM 
    daily_data 
WHERE 
    agg_day >= @firstofmonth 
    and agg_day < @runday 
GROUP BY 
    item_from, 
    item_to, 
    trunc(agg_day, MONTH)

请帮助。

你为什么不把SELECT closing_day_rate FROM daily_data WHERE agg_day = MAX（agg_day）成CTE - 甚至更好：结果直接到一个变量？ – Tyron78

答

SOLUTION：

DECLARE @rundate  DATE; 
DECLARE @yesterday  DATE; 
DECLARE @firstofmonth DATE; 
DECLARE @lastdayclosing SMALLMONEY; 

SET @runday  = CONVERT(DATE, GETDATE()) 
SET @yesterday  = CONVERT(DATE, DATEADD(dd,-1,GETDATE())) 
SET @firstofmonth = DATEADD(dd, 1, EOMONTH(@yesterday, -1)) 
SET @lastdayclosing = (SELECT close_rate FROM exchange_rate_daily WHERE exr_date = @yesterday) 

SELECT 
    item_from, 
    item_to, 
    @firstofmonth         agg_month, 
    SUM(average_day_rate)/COUNT(DISTINCT(agg_day)) average_month_rate, 
    MIN(min_day_rate)        min_month_rate, 
    MAX(max_day_rate)        max_month_rate, 
    @lstdayclosing         closing_month_rate 
FROM 
    daily_data 
WHERE 
    agg_day >= @firstofmonth 
    and agg_day < @runday 
GROUP BY 
    item_from, 
    item_to 
;

SQL Server 2012（T-SQL）选择日期到月份组

相关推荐