sql条件检查 [英] sql condition checking

问题描述

我有一个表，其中包含一列标记".在那个标记中，我必须检查它是否大于50.如果是50，则传递失败，否则失败，为此我写了一个查询，

  SELECT 标记，

              案例 何时标记> =  50   THEN  '  PASS'

               ELSE  ' 失败'

               END   AS 状态

               FROM  TABLENAME

在这里我可以得到预期的结果，但是问题是我需要提供一个参数，因为如果给定0，则仅显示失败结果；如果给定1，则仅显示通过；如果为2，则失败和通过. ........任何人都可以如何帮助

解决方案

以下是代码:

  DECLARE   @ ParamVal   int 
 SET   @ ParamVal  =  2 

选择
案例  @ ParamVal 
何时  0  > THEN 
   选择标记，
   案例 时间标记<  50   THEN  ' 
    END   AS 状态
时间  1  时间
   选择标记，
   案例 何时标记> =  50   THEN  '  PASS'
    END   AS 状态
    FROM  TABLENAME
时间  2  时间
   选择标记，
   案例 何时标记> =  50   THEN  '  PASS'
    ELSE  ' 失败'
    END   AS 状态
    FROM  TABLENAME 

下面的查询应该可以正常工作

  DECLARE   @ Param   AS   BIT  =  1  - 用于通过/失败选择的参数

 选择标记，
    案例 何时标记> =  50   THEN  '  PASS'
     ELSE  ' 失败'
     END   AS 状态
 FROM  TABLENAME
位置( @ Param  =  1   AND 标记> =  50 )- 通过结果
 OR ( @ Param  =  0   AND 标记<  50 )- 结果失败
或  @ Param   IS   NULL  - 所有结果 

I have a table consist of a column ''Marks''. In that marks I''ve to check whether it is greater than 50 or not. If it is 50 its pass else fail, for this I wrote a query as

SELECT MARKS,

              CASE WHEN MARKS >= 50 THEN 'PASS'

              ELSE 'FAIL'

              END AS STATUS

              FROM TABLENAME

here I can get the expected result, but the problem is I need to give a parameter as, if I give 0 it shold display only the fail results, and if I give 1 should show only pass, if 2, both fail and pass.........how is it possible can anyone help

解决方案

Here is the code:

DECLARE @ParamVal int
SET @ParamVal = 2

SELECT
CASE @ParamVal
WHEN 0 THEN 
   SELECT MARKS,
   CASE WHEN MARKS < 50 THEN 'FAIL'
   END AS STATUS
WHEN 1 THEN 
   SELECT MARKS,
   CASE WHEN MARKS >= 50 THEN 'PASS'
   END AS STATUS
   FROM TABLENAME
WHEN 2 THEN 
   SELECT MARKS,
   CASE WHEN MARKS >= 50 THEN 'PASS'
   ELSE 'FAIL'
   END AS STATUS
   FROM TABLENAME

Below query should work fine

DECLARE @Param AS BIT = 1 -- Parameter for PASS/FAIL selection

 SELECT MARKS,
    CASE WHEN MARKS >= 50 THEN 'PASS'
    ELSE 'FAIL'
    END AS STATUS
FROM TABLENAME
WHERE (@Param = 1 AND MARKS >= 50) -- Pass results
OR (@Param = 0 AND MARKS < 50) -- Fail results
OR @Param IS NULL -- All results

这篇关于sql条件检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！