sql - 案例表达式中的案例表达式
问题描述
我正在使用 LIMS 数据库软件,并试图为我们的分析师简化事情。
最初我有这个直接的案例陈述,它适用于我们使用的每个“测试”
CASE
WHEN ([Dry Fertilizer].[Phosphorus ICP (< 6.99 Guar)].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.67))
THEN -1 ELSE 0
END
另一个
CASE
WHEN ([Dry Fertilizer].[Phosphorus ICP (10-13.99 Guar)].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.69)) THEN -1
ELSE 0
END
其目的是查看测试结果[磷],与已知数字[保证]进行比较,然后计算并输出“-1”,如果它低于因此失败或“0”并通过。
现在,我没有对每个“保证”进行不同的测试,而是考虑获取所有这些数字并进行相当大的计算,但是当我尝试运行计算时,我的软件就会挂起。我可以做些什么来简化此代码或使其工作?这是代码:
CASE When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =4) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.67)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =5) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.67)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =6) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.67)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =7) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.68)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =8) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.68)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =9) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.68)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =10) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.69)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =12) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.69)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =14) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.7)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =16) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.7)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =18) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.71)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =20) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.72)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =22) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.72)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =24) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.73)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =26) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.73)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =28) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.74)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =30) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.75)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =32) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.76)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =34) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.794)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =36) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.828)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =38) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.862)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =40) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.896)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =42) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.93)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =44) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.964)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =46) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.998)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =48) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-1.032)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =50) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-1.066)) THEN -1 ELSE 0 End
When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =52) Then
Case When ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-1.1)) THEN -1 ELSE 0 End
End
解决方案
为什么你需要一个嵌套的 CASE,你可以简单地使用 AND
SELECT CASE When ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee] =4) AND ([Dry Fertilizer].[Phosphorus ICP].[Phosphorus] < ([Dry Fertilizer].[Phosphorus Guarantee].[Phosphorus Guarantee]-0.67)) THEN -1
WHEN
WHEN
WHEN ETC
ELSE 0 END AS [YOURCOL]
推荐阅读
- java - META-INF/services/abc 中的多个条目:顺序重要吗?
- jquery - 使用 HTML5 数据属性而不是 CSS 类进行同位素过滤
- python - 带有德语字符编码问题的 CSV
- java - 为什么 Hibernate 只为我提供拦截器并且我捕获 JdbcSQLIntegrityConstraintViolationException: NULL not allowed?
- python - pytorch张量转换-张量的张量
- email - Apps 脚本:发送电子邮件 - 脚本每行发送 1 封电子邮件,而不是一封包含所有符合条件的信息的电子邮件
- excel - 如何在 excel 中以千万或缺少的形式格式化数字
- flutter - 从 Scaffold 子项添加 AppBar 操作,例如 Android 中的 Fragment.onCreateOptionsMenu(使用 InheritedWidget)
- django - Django - 能够在本地进行迁移和迁移,但不能在 Heroku 上
- statistics - Clickhouse 每个基表的最大物化视图数量