表值函数作为参数传递，以在case语句中生成输出
表值函数作为参数传递，以在case语句中生成输出
提问于 2020-12-29 07:47:01
我的数据集如下所示
Server 2012。
DECLARE @ELECTRONICS TABLE
(
RESISTORID INT,
CAPACITORID VARCHAR(10),
VOLT DECIMAL(6,2),
WATT INT,
PASSIVENUMBER INT
)
INSERT @ELECTRONICS
SELECT 100, 'TH', 1.2, 5, 93 UNION ALL
SELECT 200, 'TH', 1.2, 5, 93 UNION ALL
SELECT 300, 'TH', 1.5, 5, 93 UNION ALL
SELECT 100, 'TH', -2.9, 5, 93 UNION ALL
SELECT 500, 'RT', 3.3, 5, 93 UNION ALL
SELECT 540, 'TH', 0, 5, 93 UNION ALL
SELECT 540, 'SN', 3.3, 5, 93 UNION ALL
SELECT 540, 'UL', 4.2, 5, 93 UNION ALL
SELECT 800, 'TH', -2.4, 5, 93 UNION ALL
SELECT 300, 'RN', 2.2, 4, 35 UNION ALL
SELECT 300, 'RN', 2.5, 6, 35 UNION ALL
SELECT 100, 'RN', 1.2, 9, 35 UNION ALL
SELECT 200, 'RN', 1.2, 9, 35 UNION ALL
SELECT 300, 'RN', 1.5, 9, 35 UNION ALL
SELECT 100, 'RN', -2.9, 9, 35 UNION ALL
SELECT 800, 'RN', -2.4, 9, 31我试图编写一个表值UDF，它接受参数@PASSIVENUMBER，并基于CAPACITORID、VOLT和WATT为POWERTHRESHOLD和RESISTORID生成值。
函数在下面的SQL代码中使用。
SELECT *, CASE WHEN RESISTORID IN ('100','540') THEN 'A03' ELSE 'A01' END AS RESISTORID,
CASE WHEN CAPACITORID = 'TH' AND VOLT >0 THEN (VOLT * 1000)
WHEN CAPACITORID = 'TH' AND VOLT = 0 THEN (WATT * 1000)
WHEN CAPACITORID <> 'TH' AND VOLT >0 THEN VOLT
ELSE WATT END AS POWERTHRESHOLD
FROM @ELECTRONICS
WHERE PASSIVENUMBER = 93
SELECT * ,CASE WHEN RESISTORID IN ('300','800') THEN 'B03' ELSE 'B01' END AS RESISTORID,
CASE WHEN CAPACITORID = 'RN' AND VOLT >0 THEN (VOLT * 10*3/56)
WHEN CAPACITORID = 'RN' AND VOLT = 0 THEN (WATT * 100*2/21)
WHEN CAPACITORID <> 'RN' AND VOLT >10 THEN VOLT
ELSE WATT END AS POWERTHRESHOLD
FROM @ELECTRONICS
WHERE PASSIVENUMBER = 35PASSIVENUMBER = 93时的输出
RESISTORID CAPACITORID VOLT WATT PASSIVENUMBER RESISTORID POWERTHRESHOLD
100 TH 1.20 5 93 A03 1200.00
200 TH 1.20 5 93 A01 1200.00
300 TH 1.50 5 93 A01 1500.00
100 TH -2.90 5 93 A03 5.00
500 RT 3.30 5 93 A01 3.30
540 TH 0.00 5 93 A03 5000.00
540 SN 3.30 5 93 A03 3.30
540 UL 4.20 5 93 A03 4.20
800 TH -2.40 5 93 A01 5.00当PASSIVENUMBER = 35时输出
RESISTORID CAPACITORID VOLT WATT PASSIVENUMBER RESISTORID POWERTHRESHOLD
300 RN 2.20 4 35 B03 2200.00
300 RN 2.50 6 35 B03 2500.00
100 RN 1.20 9 35 B01 1200.00
200 RN 1.20 9 35 B01 1200.00
300 RN 1.50 9 35 B03 1500.00
100 RN -2.90 9 35 B01 9.00如何实现用case语句检查值的函数。
我尝试过这样的代码，但没有给出正确的结果。
CREATE FUNCTION [dbo].[fnchkID]
(
@PASSIVENUMBER INT
)
RETURNS INT
AS
BEGIN
RETURN
CASE
WHEN CAPACITORID = RN and @VOLT>0 THEN VOLT * 1000
ELSE WATT from @ELECTRONICS
END
END请分享你的想法。
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-12-29 08:47:24
创建一个函数是很好的，documented，所以应该是您的起点，因为它们有大量的示例可供您开始。
我建议使用内联表值函数，因为它们通常比多语句表值函数的性能要好得多。
CREATE FUNCTION [dbo].[fnchkID]
(
@PASSIVENUMBER INT
)
RETURNS TABLE
RETURN
SELECT RESISTORID, CAPACITORID, VOLT, WATT, PASSIVENUMBER
, CASE WHEN RESISTORID IN ('100','540') THEN 'A03' ELSE 'A01' END AS NEWRESISTORID
, CASE WHEN PASSIVENUMBER = 93 THEN
CASE WHEN CAPACITORID = 'TH' AND VOLT > 0 THEN (VOLT * 1000)
WHEN CAPACITORID = 'TH' AND VOLT = 0 THEN (WATT * 1000)
WHEN CAPACITORID <> 'TH' AND VOLT > 0 THEN VOLT
ELSE WATT END
WHEN PASSIVENUMBER = 35 THEN
CASE WHEN CAPACITORID = 'RN' AND VOLT >0 THEN (VOLT * 10*3/56)
WHEN CAPACITORID = 'RN' AND VOLT = 0 THEN (WATT * 100*2/21)
WHEN CAPACITORID <> 'RN' AND VOLT >10 THEN VOLT
ELSE WATT END
END AS POWERTHRESHOLD
FROM @ELECTRONICS
WHERE PASSIVENUMBER = @PASSIVENUMBER;注意:您的计算列不能具有与现有列相同的名称，因此NEWRESISTORID而不是RESISTORID。
Stack Overflow用户
发布于 2020-12-29 08:22:04
从这里开始..。并相应地更改代码。
CREATE FUNCTION [dbo].[fnchkID] (@PASSIVENUMBER INT)
RETURNS @fnchkID TABLE
(
RESISTORID INT,
CAPACITORID VARCHAR(10),
VOLT DECIMAL(6,2),
WATT INT,
PASSIVENUMBER INT
)AS
BEGIN
INSERT @fnchkID
select * from YourTable WHERE PASSIVENUMBER=@PASSIVENUMBER /* Your Logic goes here*/
RETURN
END呼叫TVF
select * from [dbo].[fnchkID] (93)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65488932
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号