前一个查询的结果,作为后一个查询的条件,该如何处理
前一个查询的结果,作为后一个查询的条件
tab
有这么一个表,有2列,a, b,输入条件是b的值,输出是a列的值.比如
拿15举例
------解决思路----------------------
tab
a b
1 10
2 20
4 30
有这么一个表,有2列,a, b,输入条件是b的值,输出是a列的值.比如
b:10 ->a:1
b:15->a:1.5
b:20->a:2
b25:->a:3
拿15举例
select a from tab where b <=15;//想求出a=1这条,但这个肯定不对
select a from tab where b>=15;//想求出a=20这条
然后根据 结果1的b和结果2的b做差,按比例算出要的a
------解决思路----------------------
SET NOCOUNT ON
DECLARE @tb TABLE(a int, b int)
INSERT INTO @tb
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 4,30
DECLARE @b int
DECLARE @a float
DECLARE @a1 float
DECLARE @b1 float
DECLARE @a2 float
DECLARE @b2 float
SET @b = 10
WHILE @b <= 25
BEGIN
-- 单独一次查询 {
SET @a = (SELECT a FROM @tb WHERE b = @b)
IF @a IS NULL
BEGIN
SELECT TOP 1
@a1 = a, @b1 = b
FROM @tb
WHERE b < @b
ORDER BY b DESC
SELECT TOP 1
@a2 = a, @b2 = b
FROM @tb
WHERE b > @b
ORDER BY b
SET @a = @a1 + (@b*1.0 - @b1) / (@b2 - @b1) * (@a2 - @a1)
END
SELECT @b b, @a a --, @b1, @a1, @b2, @a2
-- }
SET @b = @b + 5
END
b a
----------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
10 1 NULL NULL NULL NULL
b a
----------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
15 1.5 10 1 20 2
b a
----------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
20 2 10 1 20 2
b a
----------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
25 3 20 2 30 4