如何将表连接到自身并在 SQL 中选择最大值

本文介绍了如何将表连接到自身并在 SQL 中选择最大值的处理方法，对大家解决问题具有一定的参考价值

问题描述

我有一个 contracts 表:

contractId    date       price     partId
1             20120121   10        1
2             20110130   9         1
3             20130101   15        2
4             20110101   20        2

date 最大的合约是有效合约(别怪我，我怪 infor 创建 xpps)

The contract with greatest date being the active contract (don't blame me, I blame infor for creating xpps)

我需要创建查询以仅查看有效合同(每部分一份合同，日期最高的合同).

I need to create query to see only active contracts (one contract per part, the contract with highest date).

所以查询的结果应该是这样的:

So the result of the query should be like this:

contractId    date       price     partId
1             20120121   10        1
3             20130101   15        2

我在这里没有想法，我尝试了自我加入表格，我尝试了聚合函数，但我无法弄清楚.如果有人有任何想法，请与我分享..

I am out of ideas here, I tried self joining the table, I tried aggregation functions, but I can't figure it out. If anyone would have any idea, please share them with me..

推荐答案

这几乎适用于所有 RDBM，

this will work on almost all RDBMs,

SELECT  a.*
FROM    tableName A
        INNER JOIN
        (
            SELECT partID, MAX(date) maxDate
            FROM    tableName
            GROUP BY partID
        ) B on a.partID = b.partID AND
                a.date = b.maxDate

SQLFiddle 演示

如果您的 RDBMS 支持 Window Function，

if your RDBMS supports Window Function,

SELECT  contractId ,date, price,partId
FROM    
(
    SELECT contractId ,date, price,partId,
            ROW_NUMBER() OVER (PARTITION BY PartID
                                ORDER BY date DESC) rn
    FROM    tableName
) s
WHERE   rn = 1

SQLFiddle 演示

这篇关于如何将表连接到自身并在 SQL 中选择最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，WP2

如何将表连接到自身并在 SQL 中选择最大值

问题描述

推荐答案

admin_action_{$_REQUEST[‘action’]}

admin_footer-{$GLOBALS[‘hook_suffix’]}

customize_save_{$this->id_data[‘base’]}

customize_value_{$this->id_data[‘base’]}

get_comment_author_url

network_admin_edit_{$_GET[‘action’]}

network_sites_updated_message_{$_GET[‘updated’]}

pre_wp_is_site_initialized

WordPress 的SEO 教学：如何在网站中加入关键字（Meta Keywords）与Meta 描述（Meta Description）？

谷歌的SEO是什么