连接交易表并在Netezza/PDA中填充空值 [英] Joining Transaction Tables and Populating Null Values in Netezza / PDA

问题描述

我在Netezza中有两个交易表，如下所示.当在ID和transactionCount上连接表并尝试返回Answer时，第9998、9996、9995等行将为空.如何返回所有transactionCount值，并在Answer列中填充最后一个结果?

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?

例如，9998将返回U，9996起将返回Y，9988将返回N，依此类推.

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

感谢您的帮助.

推荐答案

您没有指定RDBMS，所以我只使用MySQL.

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

这里有一个小提琴来说明它的工作原理: http://sqlfiddle.com/#!2 /dde35e/3

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

现在sql小提琴是: http://sqlfiddle.com/#!2/36aacc /1

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

这篇关于连接交易表并在Netezza/PDA中填充空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！