SQL:条件，其中与相互涉及的所有变量

Question

如何为单个组编写特定的double或(multiple) where语句条件，这些条件应该包含在所有变量中。我示范给你看。

select 
case, group, department, riskscore 
from sometable 
where department in ('A', 'B')  -- condition that count for group A, B
or (group = 'one' and riskscore>5.5) -- condition count for one
or (group = 'two' and riskscore>8.0) - condition count for two

你可以看到程序将选择所有组1 + 5.5>两个+ 8.0>但我需要A、B部门也参与进来。

我知道你可以这样写，但我想知道我是否可以分开写，这样我就不必重复所有的信息，为两个组计数。

select 
case, group, department, riskscore 
from sometable 
where (group in ('A', 'B') and group in ('one') and riskscore>5.5)
or (group in ('A', 'B') and group in ('two') and riskscore>8.0)

Answer 1

您需要逻辑AND之间的条件:

department in ('A', 'B')

和条件:

(`group` = 'one' and riskscore>5.5) or (`group` = 'two' and riskscore>8.0)

:

where department in ('A', 'B')  
and (     
(`group` = 'one' and riskscore>5.5)
or (`group` = 'two' and riskscore>8.0)
)

Answer 2

您可以使用嵌套查询(根据情况可能会有性能问题)。例如:

select required_columns 
from (
SELECT required_columns 
from some_table
where department in ('A', 'B')
) Where (group = 'one' and riskscore>5.5) 
or (group = 'two' and riskscore>8.0)