如何在SQL中执行l excel查找功能 [英] How do perform l excel lookup function in SQL
问题描述
如何在sql中执行excel查找功能。
根据我的理解,我可以使用where条件搜索表中的值,因为当精确值匹配时它作为查找工作,这里的场景是当我们执行查找操作时excel不同,如果目标列中没有查找值,它将从同一列中选择最小值
EX: - 我想要在可乐中看这个值33.33%并且需要从colc返回值为13
excel我得到了正确的结果。但是如何在sql server查询中执行此操作
问题1)33.33%不在于可乐值
问题没有2)我如何从colc中选择13值,当33.33%接近33.42%然后我将从colc获得14
COLA COLB COLC
33.06%1.06 13
33.42%1.07 14
33.78%1.08 15
我尝试了什么:
i尝试使用where条件
选择 colc 来自 tablename 其中 cola = 33。 33
SELECT sum(colc)/ 3 从 tablename 其中 round(COLA, 0 )= 33。 00 - 随机尝试
使用此示例表create table tablename
(
COLA float ,
COLB float ,
COLC int
)
插入 进入 tablename 值
( 33 。 06 , 1 。 06 , 13 ),
( 33 。 42 , 1 。 07 , 14 ),
( 33 。 78 , 1 。 08 , 15 )
获取所需数据的最简单方法可能是根据输入值和COLA之间的差异对表格进行排序。然后选择返回的顶行。即
声明 @ lookupval float = 33 。 33
声明 @ returnval int =( SELECT TOP 1 COLC FROM tablename
ORDER BY ABS(COLA - @ lookupval ))
声明 @returnval int =( SELECT TOP 1 COLC FROM tablename
WHERE cola< @ lookupval ORDER BY COLA)
结合我之前的两种方法,给输入的最接近值低于输入
声明 @ lookupval float = 33 。 33
声明 @ returnval int =( SELECT TOP 1 COLC FROM tablename
WHERE cola< @ lookupval
ORDER BY ABS(COLA - @ lookupval ))
print @ returnval 跨度>
How do i perform excel lookup function in sql.
As per my understanding i can use where condition for searching a value in table as it works as lookup when exact values matched, Here the scenario is different in excel when we perform lookup operation if the looking value is not available in target column it will pick the least value from the same column
EX:- I want to look this value 33.33% in cola and need to return value from colc as 13
in excel am getting correct result. but how do i perform this operation in sql server query
Problem no 1)33.33% not lies in cola values
Problem no 2)How do i pick 13 value from colc , when 33.33% near to 33.42% then i will get 14 from colc
COLA COLB COLC
33.06% 1.06 13
33.42% 1.07 14
33.78% 1.08 15
What I have tried:
i tried using where condition
select colc from tablename where cola=33.33
SELECT sum(colc)/3 From tablename where round(COLA,0)=33.00 -- randomly tried
Using this example tablecreate table tablename ( COLA float, COLB float, COLC int ) insert into tablename values (33.06, 1.06, 13), (33.42, 1.07, 14), (33.78, 1.08, 15)
Probably the easiest way to get the data you want is to order the table by the difference between your input value and COLA. Then just pick the top row returned. I.e.
declare @lookupval float = 33.33 declare @returnval int = (SELECT TOP 1 COLC FROM tablename ORDER BY ABS( COLA - @lookupval ))
[Edit - apologies I misread this as requiring the closest value, not the closest still lower]
declare @returnval int = (SELECT TOP 1 COLC FROM tablename WHERE cola < @lookupval ORDER BY COLA)
[Edit 2 - after OP comment]
Combining both my earlier approaches gives the closest value to the input that is lower than the input
declare @lookupval float = 33.33 declare @returnval int = (SELECT TOP 1 COLC FROM tablename WHERE cola < @lookupval ORDER BY ABS( COLA - @lookupval )) print @returnval
这篇关于如何在SQL中执行l excel查找功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!