代码之家 › 专栏 › 技术社区 › Yanayaya

如何使用SQL查询根据条件获取下一条记录?

sql-server sql

Yanayaya · 技术社区 · 6 年前

Onhire Offhire

---------------
| Hire        |
---------------
| Id          |
| CarId       |
| Onhire      |
| Offhire     |
| HireCompany |
|-------------|

如果对该表运行基本select,我会看到以下数据。我添加了一个 WHERE

Id  |  CarId |  Onhire     |  Offhire    |  HireCompany
-------------------------------------------------------
10  |  272   |  2019-01-01 |  2019-03-01 |  Company A
11  |  272   |  2019-03-02 |  2019-04-01 |  Company B
-------------------------------------------------------

Company B 2019年3月2日。我需要我的查询来证明这辆车目前是出租的,但是在一个名为 ForwardHire (或其他)显示下一个雇佣公司,以及显示下一个雇佣开始日期的列。

Id  |  CarId |  Onhire     |  Offhire    |  ForwardHire |  ForwardHireDate
---------------------------------------------------------------------------
10  |  272   |  2019-01-01 |  2019-03-01 |  Company B   |  2019-03-02

注意:我已经知道如何从我的雇佣中返回单个结果

我希望我的问题有道理,有人能帮助我。在SQL查询方面,这对我来说是第一次,因此任何建议和指导都将不胜感激。

4 回复 | 直到 6 年前

Yogesh Sharma 6 年前

lead 功能?:

SELECT h.*
FROM (SELECT h.*,
             LEAD(HireCompany) OVER (PARTITION BY CarID ORDER BY Id) AS ForwardHire,
             LEAD(Onhire) OVER (PARTITION BY CarID ORDER BY Id) AS ForwardHireDate
      FROM Hire h
     ) h
WHERE ForwardHire IS NOT NULL AND ForwardHireDate IS NOT NULL;

EzLo tumao kaixin 6 年前

使用 OUTER APPLY:

SELECT
    H.*,
    T.ForwardHire,
    T.ForwardHireDate
FROM
    Hire AS H
    OUTER APPLY (
        SELECT TOP 1                        -- Just the next record
            ForwardHire = F.HireCompany,
            ForwardHireDate = F.OnHire
        FROM
            Hire AS F
        WHERE
            H.CarId = F.CarId AND           -- With the same car
            F.OnHire > H.OffHire            -- With later OnHire
        ORDER BY
            F.OnHire ASC                    -- Sorted by OnHire (closeste one first)
        ) AS T

Gordon Linoff 6 年前

你只是想 lead() ?

select h.*,
       lead(h.hirecompany) over (partition by h.carid order by h.onhire) as next_hirecompany
from hire h;

left join :

select h.*, hnext.hirecompany as next_hirecompany
from hire h left join
     hire hnext
     on hnext.carid = h.carid and
        hnext.onhire = dateadd(day, 1, h.offhire);

forpas 6 年前

onhire

select 
  h1.*,
  h2.hirecompany ForwardHire 
  h2.onhire ForwardHireDate
from hire h1 left join hire h2
on 
  (h2.carid = h1.carid) 
  and 
  (h2.onhire = (select min(onhire) from hire where carid = h1.carid and onhire > h1.offhire) )
where 
  h1.carid = 272
  and
  curdate() between h1.onhire and h1.offhire