多表选择，限制结果数

x8diyxa7 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(292)

假设您有一个查询来检索数据库中所有棒球运动员的统计数据，该数据库不断更新统计数据，如步行、本垒打等，例如：

SELECT pl.player_name, pl.player_number, bat.at_bats, pit.era 
FROM players pl
LEFT JOIN batting bat ON pl.player_id = bat.player_id
LEFT JOIN pitching pit ON pl.player_id = pit.player_id

现在假设您还想从另一个表中提取统计数据，比如工资表，但是您只想提取最近的工资，例如：

SELECT pl.player_name, pl.player_number, bat.at_bats, pit.era, sal.salary
FROM players pl
LEFT JOIN batting bat ON pl.player_id = bat.player_id
LEFT JOIN pitching pit ON pl.player_id = pit.player_id
LEFT JOIN salaries sal ON pl.player_id = sal.player_id

但你只想得到每个球员最近的工资数字。你怎么把这些结合起来？希望我能解释清楚。

sql

来源：https://stackoverflow.com/questions/62286730/multi-table-select-limiting-number-of-results

2条答案

按热度按时间

brvekthn1#

SELECT pl.player_name, pl.player_number, bat.at_bats, pit.era, s.salary
FROM players pl
LEFT JOIN batting bat ON pl.player_id = bat.player_id
LEFT JOIN pitching pit ON pl.player_id = pit.player_id
cross apply (select top 1 * from salaries sal where pl.player_id = sal.player_id order by 
              sal.SalaryDate desc) s

赞(0）回复(0）举报 2021-07-29

wmomyfyw2#

我在本文档中遇到了一些有趣的代码（第6页-它是为sas编写的，但由于sas使用ansi sql，因此代码适用于所有sql系统）：https://support.sas.com/resources/papers/proceedings17/0930-2017.pdf
对于您的应用程序，它看起来像这样（不是正面的，但应该很接近）：

SELECT pl.player_name, pl.player_number, bat.at_bats, pit.era, sal.salary
FROM players pl
LEFT JOIN batting bat ON pl.player_id = bat.player_id
LEFT JOIN pitching pit ON pl.player_id = pit.player_id
LEFT JOIN (
    select salary,
        player_id
    from salaries sal1
    where salDate = (
                    select max(salDate)
                    from salaries sal2
                    where sal1.player_id = sal2.player_id
                   )
           ) as sal ON pl.player_id = sal.player_id

在这种情况下有点麻烦，因为您需要2个子查询来完成您所描述的任务。我可能会将所有的薪水加入一个cte，然后执行max（）self join来选择最近的一个作为最终选择。

赞(0）回复(0）举报 2021-07-29

我来回答

多表选择，限制结果数

2条答案

相关问题

热门标签

最新问答