使用“ If”表示where子句中的条件 [英] Using "If" condition in where clause

问题描述

我喜欢在where子句中使用 IF条件。从各种线程来看，我知道选项之一是CASE表达式，但我想不通。

I like to use "IF" condition in where clause. From various threads, I understand that one of the options is CASE expression but I couldn't figure it out.

示例代码：

select * from sampleTable
where 
  If @taxtype = 'P' then
    (taxtype = 'P' or  (taxtype = 'E' and code in ('MER','SER')))
  Else
    (taxtype = 'E' and code not in ('MER','SER'))
  End If

任何帮助将不胜感激。

谢谢！

推荐答案

select * from sampleTable
where 
  case when @taxtype = 'P' then
    (taxtype = 'P' or  (taxtype = 'E' and code in ('MER','SER')))
  Else
    (taxtype = 'E' and code not in ('MER','SER'))
  end

看起来像，它将与Postres

Looks like this'll work with Postres

编辑：

离开我的起源我回答是因为要点有效，但 Postgres没有概念变量，就像其他RDBMS一样，因此我将其重写为

Leaving my original answer because the gist works but Postgres doesn't have a concept of variables like other RDBMSs so I re-wrote this as

WITH myconstants as (SELECT 'P'::text as vtaxtype)

select * from sampleTable
where 
  case when (select vTaxType from myconstants) = 'P' then
    (taxtype = 'P' or  (taxtype = 'E' and code in ('MER','SER')))
  Else
    (taxtype = 'E' and code not in ('MER','SER'))
  end;

这里是 SQL小提琴显示

这篇关于使用“ If”表示where子句中的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！