代码之家 › 专栏 › 技术社区 › Midhul

WHERE子句中的事例-传递多个值

sql-server sql

Midhul · 技术社区 · 8 年前

我需要在WHERE子句中传递多个事例

SELECT *
FROM [dbname].[dbo].[tablename] CD, 
     [dbname].[dbo].Applications_N_Device_Master ADM
WHERE 
    CD.Product_ID = ADM.Product_ID
    AND CD.Reg_Date BETWEEN @FromDate AND @ToDate
    AND CD.CompType_ID = '2' 
    AND cd.closed IN (CASE 
                         WHEN @Status = 'yes' OR 'NO' 
                            THEN @status
                            ELSE 'YES', 'NO'
                      END)
ORDER BY 
    CD.Comp_ID DESC

我得到一个错误关于 else 'YES','NO' 部分

2 回复 | 直到 8 年前

RubioRic Danail Tsvetanov 8 年前

SELECT *
FROM [dbname].[dbo].[tablename] CD,  
     [dbname].[dbo].Applications_N_Device_Master ADM
WHERE CD.Product_ID = ADM.Product_ID 
  AND CD.Reg_Date BETWEEN @FromDate AND @ToDate 
  AND CD.CompType_ID='2' 
  AND (
        (@status IN ('yes', 'NO') AND cd.closed = @status) OR
        (@status NOT IN ('yes', 'NO') AND cd.closed IN ('YES', 'NO'))
      )
ORDER BY 
    CD.Comp_ID DESC

你可能想做一个 ISNULL 具有 @status 处理空值。

RubioRic Danail Tsvetanov 8 年前

SELECT * FROM [dbname].[dbo].[tablename] CD, 
              [dbname].[dbo].Applications_N_Device_Master ADM 
WHERE CD.Product_ID = ADM.Product_ID 
  AND CD.Reg_Date BETWEEN @FromDate AND @ToDate 
  AND CD.CompType_ID='2' 
  AND cd.closed in (CASE WHEN @Status='yes'THEN 
                         WHEN @Status='no' THEN @status 
                         ELSE 'YOURELSEPART' END) 
ORDER BY CD.Comp_ID DESC

我想这能解决你的问题

推荐文章