SUM() 忽略 GROUP BY 並總結 4 行而不是 2

我在使用GROUP BYMySQL 時遇到了困難。

我的數據庫設置：

client_visit
   - id
   - member_id
   - status_type_id (type_of_visit table)
   - visit_starts_at
   - visit_ends_at

member
   - id

schedule_event
   - id
   - member_id
   - starts_at
   - ends_at

type_of_visit
   - id
   - type (TYPE_BOOKED, TYPE_PRESENT etc)

就這個問題而言： a在給定時間member教授課程或領導活動 (a schedule_event)。Aclient報名參加此類或活動。

例如：

客戶 A、B 和 C 書籍訪問以及那些訪問由和client_visit組成的表，因此我們知道哪個班級和哪個成員正在教學/或有活動。schedule_event_id``member_id

現在，我們想知道給定成員花費在客戶註冊的教學/領導活動上的總時間（基於client_visit type_of_visit相當於“預訂”或“出席”的列）。我們將成員 ID 82 作為我們的測試案例。

成員 ID 82 有 4 個客戶端在兩個不同的類中，所以如果每個類需要 2 小時 15 分鐘（8100 秒），這意味著總時間應該是 16200 秒。

這是我的第一個查詢：

SELECT cv.member_id AS `member_id`,
      sch.id AS `scheduleId`,
      cv.visit_starts_at AS `visitStartsAt`,
      TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at) AS `totalTime`
 FROM `schedule_event` AS `sch`
        LEFT JOIN `client_visit` AS `cv` ON cv.schedule_event_id = sch.id
        INNER JOIN `type_of_visit` AS `tov` ON tov.id = cv.status_type_id
WHERE (tov.type = 'TYPE_BOOKED' OR tov.type = 'TYPE_PRESENT') and cv.member_id = 82

結果如下：

這向我展示了第一堂課的客戶，以及第二堂課的客戶。我只想要兩行，每個班級一個。所以，我添加這個：

GROUP BY sch.id

現在，結果如下：

到目前為止，一切都很好，

我知道這個成員有兩個計劃 ID，所以我修改了 group by 以將它們合二為一：

GROUP BY sch.id AND cv.member_id

我希望它會首先基於sch.id（上圖已經顯示的結果）和cv.member_id（我們有兩行，所以合併後應該是一個）

結果是（我通過添加 GROUP_CONCAT 修改了 scheduleId，所以我們可以看到兩個計劃 ID 都在那裡）：

現在，就像我將兩個計劃 ID 匯總在一起一樣，我想將兩個計劃課程的時間相加。

我現在修改查詢：

SUM(TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at)) AS `totalTime`

結果是：

我得到了32400！由於某種原因，SUM 仍然看到所有 4 行，而不僅僅是唯一的 2 行。

我預計最終的結果是

+-----------+------------+
| member_id | total_time |
+-----------+------------+
| 82        | 16200      |
+-----------+------------+

不需要所有其他列，我只是讓它們看看發生了什麼

怎麼了？

正如 Willem Renzema 所說，你誤解了它的GROUP BY工作原理。既然你似乎沒有理解他所說的話，讓我試著換一種說法。

GROUP BY，從邏輯上講，用於將結果集中的行組合在一起。通常，您會提供一個列列表，用於將行分組在一起。GROUP BY sch.id, cv.member_id告訴 SQL 辨識這兩列的唯一值集，並根據這些值對結果集中的行進行分組。在您的情況下，這兩個值有兩個唯一值對：

• cv.member_id= 82, sch.id= 17101
• cv.member_id= 82, sch.id= 17153

因此，您將獲得兩組行 - 三組具有第一對值，另一組具有第二對值。

GROUP BY向子句添加額外的列永遠不會導致更少的組 - 新列在所有行中都相同（在這種情況下，您有相同數量的組），或者新列具有不同的值形式一個或多個小時原始組中的某些行（在這種情況下，您現在將擁有更多組）。

此外（正如 Willem 所指出的），您遇到了語法錯誤。列表中的列GROUP BY用逗號分隔。在您的GROUP BY sch.id AND cv.member_id中，您按計算進行分組：sch.id AND cv.member_id或將兩者都視為布爾值的sch.id結果cv.member_id。由於兩者都不是 0，因此當轉換為布爾值時，兩者都評估為 1（真），並且組合(true AND true)為真。所以，你最後只有一組，4 行。

讓我們退後一步，想想你實際上想要做什麼（看起來像）。對於給定的member_id，您需要他們參與“已預訂”或“目前”類型的活動的總時間。

請注意，總時間是從schedule_event表格中計算出來的。另外，請注意，給定的member_id可以schedule_event多次與相同的關聯。因此，要獲得總時間，我們需要辨識schedule_event我們所關聯的不同行member_id，並將這些唯一值的時間相加。

在這種情況下，最簡單的方法是使用子查詢來獲取與schedule_events我們member_id相關的不同列表，然後將這些不同事件的總時間相加。

這是一個可以做到這一點的查詢：

SELECT `member_id`
     ,SUM(`totalTime`) as `totalTime`
 FROM (
       SELECT DISTINCT
           cv.member_id AS `member_id`,
           sch.id AS `scheduleId`,
           TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at) AS `totalTime`
       FROM 
           `schedule_event` AS `sch`
           INNER JOIN `client_visit` AS `cv` ON cv.schedule_event_id = sch.id
           INNER JOIN `type_of_visit` AS `tov` ON tov.id = cv.status_type_id
       WHERE 
           (tov.type = 'TYPE_BOOKED' OR tov.type = 'TYPE_PRESENT') 
           AND cv.member_id = 82
      ) sq
GROUP BY `member_id`;

子查詢（想像中的標籤sq）基本上是您的原始查詢。我將您的更改LEFT JOIN為INNER JOIN，因為我們必須有client_visit記錄來辨識member_id和訪問類型。但是，我刪除了SUMon totalTime; 在這一點上，我們只想知道每個schedule_event將花費的時間。我還補充說DISTINCT- 我們不在乎這schedule_event齣現了多少次member_id；無論出現一次、3次還是207次，總時間都是一樣的。

一旦我們確定了schedule_event我們member_id所連接的數據，那麼我們需要所有這些schedule_event行的總時間。因此，我們獲取子查詢的結果，將它們分組member_id（以防有必要將其拉回以獲得多個member_id值），並總結每一schedule_event行的計算時間。

由於 joanolo 為您的問題設置了一個 dbfiddle，因此我接受了他的工作並在最後添加了此查詢，因此您可以看到結果是您想要的；更新的 dbfiddle 連結在這裡。

我希望這有助於闡明GROUP BY實際如何為您工作。

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