主页 / dba / 问题 / 344288
Accepted
Daylon Hunt
Asked: 2024-12-19 06:04:19 +0800 CST

分组表中的最大字段

  • 772

我有一个如下所示的分组表：

月末日期 版本号
2024-06-30 1
2024-06-30 2
2024-07-31 1
2024-07-31 2
2024-07-31 3
2024-07-31 5

对于每个月末，我都希望提取最大版本 ID，以便最终获得唯一的月末日期和最大值 (VersionId)。我尝试编写代码，但无法正常工作。

select distinct c.END_OF_MONTH_DATE, c.VERSION_ID, 'Assets'
    from dbo.Contracts_Version_Details c
        inner join(
            select distinct max(c1.Version_Id) as MaxVersionId, c1.END_OF_MONTH_DATE
            from dbo.Contracts_Version_Details c1
            group by c1.End_of_month_date) mv
            on c.VERSION_ID = mv.MaxVersionId
        inner join(
                    select distinct max(c2.END_OF_MONTH_DATE) as MaxDate, c2.VERSION_ID
                    from dbo.Contracts_Version_Details c2
                    group by c2.VERSION_ID
                ) as md
                on c.END_OF_MONTH_DATE = md.MaxDate
sql-server

3 个回答

  1. Best Answer

    每个月末...唯一的月末日期和（最大）版本 ID。

    这个怎么样？

    select 
  End_of_Month_Date
, max( VersionId ) 
from Contracts_Version_Details 
group by End_of_Month_Date
order by End_of_Month_Date 
;
    • 1

  2. 这可能会让你更接近：

    SELECT DISTINCT c.END_OF_MONTH_DATE, c.VERSION_ID, mv.max_version_id, meom.max_EOM, 'Assets'
from dbo.Contracts_Version_Details c
INNER JOIN (
    SELECT END_OF_MONTH_DATE, max(x.version_id) as max_version_id
    FROM dbo.Contracts_Version_Details as x
    GROUP BY END_OF_MONTH_DATE
) as mv
    ON c.END_OF_MONTH_DATE = mv.END_OF_MONTH_DATE
INNER JOIN (
    SELECT x.version_id, max(x.END_OF_MONTH_DATE) as max_EOM
    FROM dbo.Contracts_Version_Details as x
    GROUP BY version_id
) as meom
    ON c.version_id = meom.version_id
    • 0

  3. 每个月末，我想提取最大版本 ID

    MAX(VersionId) OVER (PARTITION BY End_of_Month_Date)就足够了。

    • 0

相关问题