使用WHERE IN子句的SQL查询 [英] SQL query using WHERE IN clause

问题描述

这可能很简单，但我是SQL新手，找不到确切的操作方法。

This might be simple but I'm new to SQL and couldn't find how to do this exactly.

我有下表：

我的要求如下：

我需要，对于每个Dim，对于每个频率，我都需要最新的Date&最新日期的最高版本。
例如：Dim‘A’&频率每月及其最新的日期和时间；最新日期的最高版本。并且还会有Dim'A'&的另一行频率为每周及其最新的日期和时间；最新日期的最高版本。

I need, for every Dim, for each Frequency, I need latest Date & maximum Version of that latest Date. For example: There will be one row for Dim 'A' & Frequency 'Monthly' with their latest Date & the latest date's maximum Version. and There will be another row for Dim 'A' & Frequency 'Weekly' with their latest Date & the latest date's maximum Version.

有人可以帮助我吗？

我尝试使用以下查询，但是它不会返回正确的值：

I tried using following query but it not returning correct values:

SELECT Dim, Frequency, Date, Version
               FROM   sample_tbl 
               WHERE  ( Frequency, Date, 
                        Version ) IN ( 
select Frequency, max(Date), max(Version)
from sample_tbl
group by 1
);

推荐答案

我是Postgres，我认为

I Postgres, I think distinct on does what you want:

select distinct on (dim, frequency) s.*
from sample_tbl s
order by dim, frequency, date desc, version desc;

每个 dim / frequency 组合返回一行。该行是根据 order by 子句遇到的第一行。

For each dim/frequency combination this returns one row. That row is the first row encountered based on the order by clause.

这篇关于使用WHERE IN子句的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！