php - Mysql查询: filter with condition

Question

我有一个表 Orders(OID, OrderType, ODep, ReqUser)

我想像下面的逻辑一样过滤:

SELECT * FROM Orders if  (Ordertype='confidential') filter by ReqUser='$UserID'  else filter by ODep

我不确定这是否可能!

Answer 1

在这些情况下，您通常可以将 if a then b else c 替换为 (a AND b) OR ((NOT a) AND c)，如下所示:

SELECT * 
FROM Orders 
WHERE (Ordertype='confidential' AND ReqUser='$UserID')
   OR (Ordertype<>'confidential' AND ODep)
;

或者，根据您的数据，这也可能有效:

SELECT * 
FROM Orders 
WHERE ReqUser='$UserID'
   OR (Ordertype<>'confidential' AND ODep)
;

或者这个:

SELECT * 
FROM Orders 
WHERE ODep AND (ReqUser='$UserID' OR Ordertype <> 'confidential')
;