select missing row from table

temperature  decrease_capacity
----------    -开发者_如何学C---------------
125           5
150           10
175           15

etc... if i want to select decrease_capacity for temperature=166, how i will get.

The math for a linear interpolation would be:

c1 + (t - t1) / (t2 - t1) * (c2 - c1)

t  = input tempurate
t1 = nearest lower temperature
t2 = nearest upper temperature
c1 = capacity belonging to t1
c2 = capacity belonging to t2

Here's some SQL to do that math:

declare @YourTable table (temperature int, decrease_capacity int)
insert @YourTable values (125, 5), (150, 10), (175, 15)

declare @temp int
set @temp = 166

select  case 
        when below.temperature is null then above.decrease_capacity
        when above.temperature is null then below.decrease_capacity
        else below.decrease_capacity + 1.0 *
             (@temp - below.temperature) / 
             (above.temperature - below.temperature) *
             (above.decrease_capacity - below.decrease_capacity)
        end
from    (
        select  min(temperature) as mintemp
        from    @YourTable 
        where   temperature >= @temp
        ) abovetemp
left join
        @YourTable above
on      above.temperature = abovetemp.mintemp
cross join
        (
        select  max(temperature) as maxtemp
        from    @YourTable 
        where   temperature < @temp
        ) belowtemp
left join
        @YourTable below
on      below.temperature = belowtemp.maxtemp

Example on odata.

You haven't stated RDBMS. This uses SQL Server specific TOP (could be replaced with row_number or limit dependant on flavour) and a SQL Server specific variable for ease of testing.

I opted to return NULL if 2 data points were not available.

DECLARE @SearchTemp FLOAT = 166;

WITH T(temperature, decrease_capacity) AS
(
SELECT 125.0,5.0 UNION ALL
SELECT 150.0,10 UNION ALL
SELECT 175.0,15 
), T2 AS
(
SELECT TOP 1 'L' as bound, temperature, decrease_capacity
FROM T 
WHERE temperature <= @SearchTemp
ORDER BY temperature DESC
UNION ALL
SELECT TOP 1 'U' as bound,  temperature, decrease_capacity
FROM T 
WHERE temperature >= @SearchTemp
ORDER BY temperature 
)
SELECT CASE
         WHEN COUNT(*) = 2 THEN CASE
                                  WHEN COUNT(DISTINCT temperature) = 1 THEN MAX(decrease_capacity)
                                  ELSE ((@SearchTemp-MAX(CASE WHEN bound = 'L' THEN temperature END) )/(MAX(CASE WHEN bound = 'U' THEN temperature END) -MAX(CASE WHEN bound = 'L' THEN temperature END) )) * (MAX(CASE WHEN bound = 'U' THEN decrease_capacity END)-MAX(CASE WHEN bound = 'L' THEN decrease_capacity END)) + MAX(CASE WHEN bound = 'L' THEN decrease_capacity END)
                                END
       END
FROM   T2  

If linear interpolation is the desired result, then the following extremely ugly, stupid, and probably slow query might produce that result. I shortened the field names to t and dc. I say "might" because there is a huge caveat with this query. As written, it assumes that the decrease_capacity value always increases as temperature increases (I have no idea if that would be true or not). The select max(dc) from test where t <= 166 is one part that is based on that assumption. This also assumes that the fields are floating point values (otherwise it would be doing integer math as written).

Oh ... and caveat #3. This would also fail if the specified temperature was less than or greater than all the temperature values in the table.

select (select max(dc) from test where t <= 166) + 
       (166 - (select max(t) from test where t <= 166)) * 
       ((select min(dc) from test where t >= 166) - 
        (select max(dc) from test where t <= 166)) / 
       ((select min(t) from test where t >= 166) - 
        (select max(t) from test where t <= 166))

It would probably make more sense to implement this as a stored procedure and just do the individual SELECT statements and compute the linear interpolation result from that. It would be a lot easier to read and almost certainly more efficient.