SQL - Return value from row above

debugcn 에 게시 Dev

LaLa

I have a table like:

Book        ¦Time Out       ¦Time In 
123456789   ¦01/01/2013 ¦07/07/2013
123456788   ¦15/01/2013 ¦20/01/2013
123456788   ¦23/01/2013 ¦30/01/2013
123144563   ¦01/02/2013 ¦18/02/2013
123144563   ¦20/02/2013 ¦NULL
124567892   ¦03/03/2013 ¦10/03/2013

I would like it to look like this:

Book        ¦Time Out       ¦Time In        ¦Next Time Out
123456789   ¦01/01/2013     ¦07/07/2013     ¦NULL
123456788   ¦15/01/2013     ¦20/01/2013     ¦23/01/2013
123456788   ¦23/01/2013     ¦30/01/2013     ¦NULL
123144563   ¦01/02/2013     ¦18/02/2013     ¦20/02/2013
123144563   ¦20/02/2013     ¦NULL           ¦NULL
124567892   ¦03/03/2013     ¦10/03/2013     ¦NULL

Code:

SELECT nextout.Book,
       nextout.[Time In] AS NextTimeIn
FROM   BookTable nextout
       JOIN BookTable nextoutsec
         ON nextout.Book = nextoutsec.Book
WHERE  nextout.[Time In] = (SELECT MAX(maxtbl.[Time In])
                            FROM   BookTable maxtbl
                            WHERE  maxtbl.Book = nextout.Book)

This returns for the duplicate book id's the same 'Next Time Out'. rather than 1 correct value and 1 null value.

Thank You!

Lieven Keersmaekers

Untested but something like the following should get you started

;WITH q as (
  SELECT Book, [Time In], ROW_NUMBER() OVER (PARTITION BY Book ORDER BY [Time In]) AS rn
  FROM   BookTable
)
SELECT  bt.*, q2.[Time In] AS NextTimeIn
FROM    BookTable bt
        INNER JOIN q q1 ON q1.Book = bt.Book AND ISNULL(q1.[Time In], 0) = ISNULL(bt.[Time In], 0)
        LEFT OUTER JOIN q q2 ON q2.Book = q1.Book AND q2.rn = q1.rn + 1

The gist of this is

q adds a row number to each book, ordered by [Time In]
q2 joins with q1 to get the next [Time In] value.
q1 joins with BookTable to get all the original values

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-05-29

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

SQL - Return value from row above

SQL - Return value from row above

Remove rows from Dataframe where row above or below has same value in a specific column

Return value if Row(Record) does not Exist in SQL SERVER

SQL Return a clean value

return value from eval

Return value from helper asynchronously?

How to get value of cell knowing neighboring cell from same row in sql db?

Return string value from sql select command vb.net code behind

column width not controlled by row above it

SQL insert into row based on column value?

SQL Incrementing Value for non existent row

SELECT sql row when value changes

Find value in Pandas df row and return the column name

Find first number greater than x and return another value in that row

MySQL return a single row only when all rows are the value of the WHERE

Sum value from fields which are in one row

SQL QUERY replace NULL value in a row with a another value

Getting return value from Task.Run

How return value from anonymous method to label?

Return as String Value from For Loop Java

getting address of pointer from function return value

return value from function is always undefined and order

Mysql - Return max value from joined table

Return generic type from dictionary (else return a default generic value)

SQL (MS SQL Server) return column value as the result of the multiplication

Join two tables and return only one row from second table

Sql delete value from database

Smallest expressible value above given value

how to subtract between sql row and textbox value php codes?

How to return the time() value in Select SQL report query