帶有 OR/AND 子句的 GROUP BY？

假設我的數據庫如下所示：

表：消息

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 2  | 0        | 0        |  
| 2  | 1    | 0  | 1        | 2        |  
| 3  | 2    | 0  | 2        | 1        |  
| 4  | 2    | 1  | 0        | 0        |  
| 5  | 2    | 3  | 0        | 0        |  
| 6  | 2    | 0  | 1        | 3        |  
+----+------+----+----------+----------+  

然後在我從表中選擇的內容中，我想按以下方式分組：

• double_1將和相同的行分組double_2，而to= 0
• 分組from相同的行，而to= $id 和double_1= 0 和double_2= 0
• 分組to相同的行，而from= $id 和double_1= 0 和double_2= 0

任何人有任何想法如何做到這一點？

編輯：似乎我確實讓一些人誤解了這一點。我現在將解釋得更好。正如我所說的我想 GROUP BY 的方式，然後當說 時group rows where double_1 and double_2 is the same...，它對相同的行進行double_1分組double_2。就像我有這兩行：

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 0  | 1        | 1        |  
| 2  | 1    | 0  | 2        | 2        |    
+----+------+----+----------+----------+    

它們不應分組，因為ID 1’s double_1ANDdouble_2與ID 2’s double_1AND不同double_2。雖然這將分組：

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 0  | 1        | 2        |  
| 2  | 1    | 0  | 1        | 2        |    
+----+------+----+----------+----------+  

很抱歉造成誤會，希望這裡的人仍然願意幫助我。

您可以為需要獲取的每個組生成一個標誌列：

CREATE OR REPLACE VIEW vw_messages AS
SELECT id
     , `from`
     , `to`
     , double_1
     , double_2
     , case when double_1 = double_2 
             and `to` = 0
            then 1
       else 0
       end flag_1
     , case when double_1 = 0
             and double_2 = 0
             and `to` = $id
            then 1
       else 0
       end flag_2
     , case when double_1 = 0
             and double_2 = 0
             and `from` = $id
            then 1
       else 0
       end flag_3
FROM messages

這樣，您以後可以獲取完成一個組或任何組組合的行。

引用自：https://dba.stackexchange.com/questions/40771