Edit an SQL query [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

table1 has the columns:

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 has the columns

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

result:

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

the query so far is (without the event of r and t to give very high and very low is):

    SELECT table1.id
     , table1.event
     , table2.risk 
    FROM table1
     JOIN table2
      ON table2.value = ( SELECT MAX(table2.v开发者_StackOverflow社区alue)
                   FROM table2
                   WHERE table2.value <= table1.value
                 )
    ORDER BY table1.value DESC

---

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

---

Try this, it works in SQL Server 2008 and should be pretty close to what you need in 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