编辑SQL查询

Question

table1包含以下各列：

id name  value event
1  name1  0.5   f
2  name2  1.9   f
3  name3  2.6   f
4  name4  0.2   f
5  name5  0     r
6  name6  text  r
7  name7  text2 t
8  name8  5     t
....
999  name999 4.7  f

table2具有以下列

id risk     value
1  very low 0
1  low      0.5
2  medium   1.5
3  high     2.5
4 very high 3

结果：

1  name5    very high
2  name6    very high
3  name999  high
4  name3    high
5  name2    medium
6  name1    low
7  name7    very low
8  name8    very low

到目前为止的查询是(没有r和t的事件，给出了非常高和非常低的is)：

    SELECT table1.id
     , table1.event
     , table2.risk 
    FROM table1
     JOIN table2
      ON table2.value = ( SELECT MAX(table2.value)
                   FROM table2
                   WHERE table2.value <= table1.value
                 )
    ORDER BY table1.value DESC

Answer 1

SELECT table1.id, table1.name,
   CASE table1.event
     WHEN 'r' THEN 'very high'
     WHEN 't' THEN 'very low'
     ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value
           ORDER BY table2.value DESC LIMIT 1)
   END AS risk
FROM table1
ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC

Answer 2

试试这个，它可以在SQL Server2008中运行，应该非常接近您在mySQL中所需的内容

select t1.*,coalesce(a.risk,b.risk,c.risk) as risk
from t1 
left join 
( select risk from t2 where id = (SELECT MAX(t2.value) 
      FROM t2,t1 WHERE t2.value <= t1.value))
 a on t1.event='f' 
left join t2 b on t1.event='r' and b.id=5
left join t2 c on t1.event='t' and c.id=1
order by t1.value desc