使用联合合并多个计算

如果目标表只包含一行，我假设您发布的每个查询也只包含一行。因为，如果他们不这样做，你怎么知道该选择哪一行？

无论如何：一种选择是将当前查询用作 CTE（公共表表达式;一种使用子查询的新方法），交叉连接它们并获取您感兴趣的列。

例如：

with 
current_day as(
select Name, count(cast (id as int))  as current_day from (
    select Name,id from [Table1]
    where CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
    union all
    select Name,id from from [Table2]
    where CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
) x group by Name),
current_week as(

select Name, count(cast (id as int))  as current_week from (
    select Name,id from [Table1]
    where DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0
    union all
    select Name,id from from [Table2]
    where DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0
) x group by  Name),
current month as
(
select Name, count(cast (id as int))   as current_month from (
    select Name,id from [Table1]
    where DATEDIFF(m, dte_Uploaded, GETDATE()) = 0
    union all
    select Name,id from from [Table2]
    where DATEDIFF(m, dte_Uploaded, GETDATE()) = 0
) x group by  Name)
select a.name, a.current_day, b.current_week, c.current_month
from current_day a cross join current_week b cross join current_month c;

如果交叉联接不起作用，那么您应该知道如何正确联接每个 CTE 返回的结果。

最后，如何在目标表中插入值？喜欢这个：

insert into target (name, current_day, current_week, current_month)
with current_day as (the whole query posted above goes here)
select a.name, a.current_day, b.current_week, c.current_month
from current_day a cross join current_week b cross join current_month c

如果提前计算适当的日期范围并使用条件聚合来计算计数，则可能会获得更好的性能和更高的可读性。

在计算日期范围时，通常会计算一个包含的开始日期和一个独占的结束日期（第二天开始的 00：00：00）。这可以很好地处理日期/时间字段，而无需截断可能索引的列值。

条件聚合将聚合函数（在本例中）与包含或排除聚合中每个值的表达式组合在一起。因为行是计算的，而隐式的行不是。COUNT()CASECOUNT()THEN 1ELSE NULL

尝试如下操作：

-- Day, week, and month ranges. (End dates are exclusive.)
DECLARE @today      DATE = CONVERT(DATE, GETDATE())
DECLARE @day_from   DATE = @today
DECLARE @day_to     DATE = DATEADD(day, 1, @day_from)
DECLARE @week_from  DATE = DATETRUNC(week, @today)
DECLARE @week_to    DATE = DATEADD(week, 1, @week_from)
DECLARE @month_from DATE = DATETRUNC(month, @today)
DECLARE @month_to   DATE = DATEADD(month, 1, @month_from)

-- Overall range (to limit initial data retrieval)
DECLARE @range_from DATE = LEAST(@week_from, @month_from)
DECLARE @range_to   DATE = GREATEST(@week_to, @month_to)

SELECT
    Name,
    COUNT(CASE WHEN dt >= @day_from   AND dt < @day_to   THEN 1 END) AS current_day,
    COUNT(CASE WHEN dt >= @week_from  AND dt < @week_to  THEN 1 END) AS current_week,
    COUNT(CASE WHEN dt >= @month_from AND dt < @month_to THEN 1 END) AS current_month
FROM (
    SELECT Name, dte_Uploaded AS dt
    FROM Table1
    WHERE dte_Uploaded >= @range_from AND dte_Uploaded < @range_to
    UNION ALL
    SELECT Name, dte_Uploaded AS dt
    FROM Table2
    WHERE dte_Uploaded >= @range_from AND dte_Uploaded < @range_to
) X
GROUP BY Name

如果对dte_Uploaded编制了索引，则上述操作将仅有效地检索当前计算所需的那些行，而不是效率较低的表扫描。

如果使用的是不支持 、 和 函数的旧版本的 SQL Server，则可以使用以下备用日期计算：DATETRUNC()LEAST()GREATEST()

-- Day, week, and month ranges. (End dates are exclusive.)
DECLARE @today      DATE = CONVERT(DATE, GETDATE())
DECLARE @day_from   DATE = @today
DECLARE @day_to     DATE = DATEADD(day, 1, @today)
DECLARE @week_from  DATE = DATEADD(day, 1 - DATEPART(weekday, @today), @today)
DECLARE @week_to    DATE = DATEADD(week, 1, @week_from)
DECLARE @month_from DATE = DATEADD(day, 1 - DAY(@today), @today)
DECLARE @month_to   DATE = DATEADD(month, 1, @month_from)

-- Overall range (to limit initial data retrieval)
DECLARE @range_from DATE = CASE WHEN @week_from < @month_from THEN @week_from ELSE @month_from END
DECLARE @range_to   DATE = CASE WHEN @week_to > @month_to THEN @week_to ELSE @month_to END

根据您的网站定义一周开始时间的方式，可能需要调整一周范围。