MySQL 如何构建 Where Clause

Question

我有一个包含四个字段的表——table_id、table_who、table_whom、table_when。

-------------------------------------------------------
table_id | table_who | table_whom | table_when
-------------------------------------------------------    
1        | aaa       |   w1       | 02/10/2012 19:05:03
2        | aaa       |   w2       | 08/10/2012 08:05:03
3        | bbb       |   w3       | 08/10/2012 08:03:05
4        | bbb       |   w4       | 07/10/2012 08:05:03

我想选择以下所有行：

• table_who=”aaa”和(Now()-table_when)>12
• table_who<>”aaa”

如果我使用：

    SELECT Table.table_id, Table.table_who, Table.table_whom, Table.table_when, Format(Now()-[table_when],"hh") AS Diff
    FROM [Table]
    WHERE (((Table.table_who)<>"aaa") AND ((Format(Now()-[table_when],"hh"))<12));

我只有两行，table_id=3和4.但我想要的是拥有table_ID=1，3和4。

Answer 1

where (Table.table_who<>"aaa") OR (Format(Now()-[table_when],"hh")<12 AND Table.table_who="aaa") 

如果我理解正确的话。它不是 aaa 或它 aaa 当现在 - 当>12 ？

Answer 2

试试这段代码：

SELECT Table.table_id, Table.table_who, Table.table_whom, Table.table_when, Format(Now()-[table_when],"hh") AS Diff
    FROM [Table]
    WHERE (Table.table_who)<>"aaa") 
    OR ((Table.table_who)="aaa") AND ((Format(Now()-[table_when],"hh"))<12) ); 

Answer 3

只需按照您定义的条件编写条件：

WHERE
 (Table.table_who = 'aaa' AND Format(Now() - [table_when], 'hh') < 12) OR
 Table.table_who <> 'aaa'

在某些情况下，您只需要括号，当然不应该过度使用它们，但是当您需要（A和B）或C时，我喜欢在这种情况下将条件组合在一起。如果你这样写，表达式的计算顺序就不会有错。

不同的缩进可能会使其更加清晰，具体取决于您习惯的内容。

WHERE
 (
    Table.table_who = 'aaa' 
    AND Format(Now() - [table_when], 'hh') < 12
 ) 
 OR Table.table_who <> 'aaa'

Answer 4

我认为这就是你想要的：

WHERE (Table.table_who = "aaa" AND Table.table_when > SUBDATE(CURDATE(), INTERVAL 12 DAY)) OR Table.table_who <> "aaa"

Answer 5

请尝试：

   SELECT Table.table_id, Table.table_who, Table.table_whom, Table.table_when, Format(Now()-  [table_when],"hh") AS Diff
    FROM [Table]
    WHERE (((Table.table_who)<>"aaa") AND ((Format(Now()-[table_when],"hh"))<12))
    OR (((Table.table_who)="aaa") AND ((Format(Now()-[table_when],"hh"))<12))