...

Get the Record Where the a Certain Field value First Corresponds to Another Field Value

 
SQL(179)

【Question】

Tid  workid  kind  creattime  price 

1001  201  sales  2015-04-01  200 

1002  201  sales  2015-04-02  100 

5001  201  sales  2015-05-20  500 

1001  702  rework  2015-05-20  -100 

 

Requirement: get records for the report with workid 201 on date 2015-05-20:

 

5001  201  sales  2015-05-20  500 

1001  702  rework  2015-05-20  -100 

 

Since workid 201 is the first who handles Tid 1001, I also need to display the record where Tid is 1001 on 2015-05-20 in the report.

 

【Answer】

The logic is clear, but SQL handles an order-based calculation in a roundabout way. In a Java environment, we can deal with it in SPL. The Structured Process Language produces intuitive code:

 

A

1

$select * from tb order by creattime

2

=A1.select(creattime==date("2015-05-20")   && (workid==201 || (t=Tid,A1.select@1(Tid==t).workid==201)))

A1: Retrieve data from the source table sorted by creattime with a SQL-like statement.

A2: Get records where creattime is 2015-05-20 and worked is 201, or the first record under same Tid where creattime is 2015-05-20 and workid is 201.


SQL(179)
 
 •  340 View  •