Joining Transaction Tables and Populating Null Val

问题:

I've got two transaction tables in Netezza set up like the below. When joining the tables on ID and transactionCount, and trying to return Answer, there will be nulls for row 9998, 9996, 9995 etc. How would I return all transactionCount values with the Answer column populated with the last result?

So for example 9998 would return U, 9996 onward would return Y, 9988 would return N, and so on.

  ID    transactionCount  ID    transactionCount    Answer
  1        9999            1         9999             U
  1        9998            1
  1        9997            1         9997             Y
  1        9996            1
  2        9999            2         9999             Y
  2        9998            2        
  2        9997            2
  2        9996            2         9996             N
  2        9995            2         
  3        9999            3         9999             Y
  3        9998            3
  3        9997            3         9997             N
  3        9996            3    
  3        9995            3

Thanks for any help.

回答1:

You didn't specify an RDBMS so I'll just use MySQL.

select transactionCount1, 
(select answer
 from t2
 where transactionCount2 >= t1.transactionCount1
 order by transactionCount2
 limit 1) as answer
from t1
order by 1 desc

Here is a fiddle to show it working: http://sqlfiddle.com/#!2/dde35e/3

EDIT...

In response to a change to the question, the query becomes:

select id1, transactionCount1, 
(select answer
 from t2
 where id2 = id1 and transactionCount2 >= t1.transactionCount1
 order by transactionCount2
 limit 1) as answer
from t1
order by 1 asc, 2 desc

And the sql fiddle is now: http://sqlfiddle.com/#!2/36aacc/1