从联接表中选择右百分位数

Question

如何根据多列中的值从联接表中选择正确的值。我开始使用sqlfiddle：http://sqlfiddle.com/#!9/f92daa/2主桌：

id  val
1   1
2   4
3   67
4   78
5   22

联合表：

obs_id  perc1   perc2   perc3   perc4   perc5   perc6   perc7   perc8   perc9   perc10
1   1   2   3   4   5   6   7   8   9   10
2   1   2   3   4   5   6   7   8   9   10
3   10  20  30  40  50  60  70  80  90  100
4   10  20  30  40  50  60  70  80  90  100
5   10  20  30  40  50  60  70  80  90  100

预期结果应是：

id  val perc
1   1   1
2   4   4
3   67  70
4   78  80
5   22  30

perc -应该是perc表中的数字(由id连接)，而不大于最高值形式列perc1 10。

表perc存储观察的百分位数。目标是找出从主表中命中值的间隔，并取上频带。

Answer 1

您可以使用case表达式来完成此操作。这假设perc1 < perc2 < perc3 < perc4 <.诸若此类。

SELECT obs.id, obs.val, 
case when val <= perc1 then perc1 
     when val <= perc2 then perc2 
     when val <= perc3 then perc3
     when val <= perc4 then perc4 
     when val <= perc5 then perc5 
     when val <= perc6 then perc6 
     when val <= perc7 then perc7 
     when val <= perc8 then perc8 
     when val <= perc9 then perc9
     when val <= perc10 then perc10 
     end
perc
FROM obs
INNER JOIN perc
ON obs.id = perc.obs_id

SQL Fiddle

Answer 2

如果您确信perc表始终具有升序 order then中的值

select 
a.id, a.val,

case
when b.perc1 >= a.val then b.perc1
when b.perc2 >= a.val then b.perc2
when b.perc3 >= a.val then b.perc3
when b.perc4 >= a.val then b.perc4
when b.perc5 >= a.val then b.perc5
when b.perc6 >= a.val then b.perc6
when b.perc7 >= a.val then b.perc7
when b.perc8 >= a.val then b.perc8
when b.perc9 >= a.val then b.perc9
when b.perc10 >= a.val then b.perc10
end as perc

from idval a , perc b
where a.id = b.obs_id