如何合并 SQL Select 查询？

用户2536982

我一致执行了三个查询：

SELECT TOP 1 max(value) FROM tableA
where site = 18
    and (CAST(DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') as DATE) >= '2017-2-1'
    and CAST(DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') as DATE) <= '2017-2-28')
Group by CAST(DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') as DATE)
order by CAST(DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') as DATE) DESC;

SELECT TOP 1 max(value) FROM tableA
where site = 3
    and (CAST(DATEADD(s,stamp/1000,'1970-01-01 00:00:00') as DATE) >= '2017-2-1'
    and CAST(DATEADD(s,stamp/1000,'1970-01-01 00:00:00') as DATE) <= '2017-2-28')
Group by CAST(DATEADD(s,stamp/1000,'1970-01-01 00:00:00') as DATE)
order by CAST(DATEADD(s,stamp/1000,'1970-01-01 00:00:00') as DATE) DESC;

SELECT TOP 1 max(value) FROM tableA
where site = 4
    and (CAST(DATEADD(s,stamp/1000,'1970-01-01 00:00:00') as DATE) >= '2017-2-1'
    and CAST(DATEADD(s,stamp/1000,'1970-01-01 00:00:00') as DATE) <= '2017-2-28')
Group by CAST(DATEADD(s,stamp/1000,'1970-01-01 00:00:00') as DATE)
order by CAST(DATEADD(s,stamp/1000,'1970-01-01 00:00:00') as DATE) DESC;

我想将这三个查询合并为一个，并通过一个选择查询站点 18、3、4，但我不知道如何。请告知如何将这 3 个查询合并为一个。任何帮助将不胜感激！

戈登·利诺夫

您似乎想要在 2 月的最后一天拥有三个不同站点的最大值的数据。

如果是这样，这更简单：

select site_id, max(value)
from (select t.*,
             dense_rank() over (partition by site order by tstamp / (1000 * 24 * 60 * 60) desc) as seqnum
      from t
      where tstamp >= datediff(second, '1970-01-01', '2020-02-01') * 1000 and
            tstamp < datediff(second, '1970-01-01', '2020-02-29') * 1000 and
            site_id in (18, 3, 4)
     ) t
where seqnum = 1;

实际上，2020 年的 2 月有 29 天。也许你想要整个月；如果是，则'2020-03-01'用于第二次比较。

请注意，对日期/时间值的操作仅在“常量”方面进行。这允许查询在tstamp适当的索引可用时使用索引。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。