mysql - tables - sql server join




在MYSQL中連接包含SUM問題的表 (2)

我總是遇到連接表上的SUM問題,總有一個問題,我可以通過運行兩個查詢得到我需要的結果,我想知道這兩個查詢是否可以組合成一個連接查詢,這裡是查詢我有和我嘗試加入查詢

查詢1

SELECT last_name, first_name, DATE_FORMAT( (mil_date),  '%m/%d/%y' ) AS dates, 
SUM( drive_time ) MINUTES FROM bhds_mileage LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_mileage.ds_id 
WHERE mil_date BETWEEN  '2016-04-11' AND  '2016-04-30'
AND bhds_mileage.ds_id =5
GROUP BY CONCAT( YEAR( mil_date ) ,  '/', WEEK( mil_date ) ) ,    
bhds_mileage.ds_id
ORDER BY last_name ASC , dates ASC 

以分鐘為單位的輸出是271,281,279

查詢2

SELECT last_name, first_name, DATE_FORMAT((tm_date), '%m/%d/%y') AS dates,   
SUM(tm_hours) total FROM bhds_timecard LEFT JOIN bhds_teachers i 
ON i.ds_id = bhds_timecard.ds_id 
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 
ORDER BY last_name ASC, dates ASC

這裡的輸出是33.00,36.00,26.75

現在我嘗試加入查詢

SELECT last_name, first_name, DATE_FORMAT((tm_date), '%m/%d/%y') AS dates,  
SUM(tm_hours) total,  SUM( drive_time ) MINUTES FROM bhds_timecard 
LEFT JOIN bhds_teachers i ON i.ds_id = bhds_timecard.ds_id 
LEFT JOIN bhds_mileage ON DATE_FORMAT((bhds_timecard.tm_date), '%m/%d/%y') = 
DATE_FORMAT((bhds_mileage.mil_date), '%m/%d/%y') AND bhds_timecard.ds_id = bhds_mileage.ds_id
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 

括號是預期的

輸出1044(271),1086(281),1215(279)


在主查詢中使用多個連接時,最終得到所有表的叉積,因此總和乘以另一個表中匹配的行數。 您需要將總和移動到子查詢中。

SELECT last_name, first_name, DATE_FORMAT(LEAST(mil_date, tm_date),  '%m/%d/%y' ) AS dates, 
        total, minutes
FROM bhds_teachers AS i
LEFT JOIN (
    SELECT ds_id, YEARWEEK(mil_date) AS week, MIN(mil_date) AS mil_date, SUM(drive_time) AS minutes
    FROM bhds_mileage
    WHERE mil_date BETWEEN '2016-04-11' AND  '2016-04-30'
    AND bhds_mileage.ds_id = 5
    GROUP BY ds_id, week) AS m 
ON m.ds_id = i.ds_id
LEFT JOIN (
    SELECT ds_id, YEARWEEK(tm_date) AS week, MIN(tm_date) AS tm_date, SUM(tm_hours) AS total
    WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
    GROUP BY ds_id, week) AS t 
ON t.ds_id = i.ds_id AND t.week = m.week

有兩個問題...... bhds_mileagebhds_timecard 之間的部分笛卡爾積(交叉積),因為來自一個表的每個細節行(在一個組內)將與另一個表中的細節行“交叉連接”。 這種情況發生在GROUP BY操作折疊行之前,併計算SUM。 這就解釋了為什麼你會看到“誇大”的價值觀。

解決方法是在內聯視圖中計算至少一個SUM()聚合...像第一個查詢之類的那樣完成SUM()/ GROUP BY()。 為清楚起見,您可以對兩個原始查詢執行相同的操作,然後從內聯視圖中加入結果。

MySQL本身不支持FULL外連接。 其中一個表需要是驅動表。 例如,我們可以使用 _timecard 作為驅動表,但這意味著我們必須從 _timecard 返回給定週的行,以便從 _timecard 返回相應的行。 也就是說,如果沒有 _timecard 的行,我們就無法從 _mileage 獲取一行。

我們注意到 bhds_teacher 的連接是外連接。 如果我們在 _mileage_timecard 之間有一個外鍵約束,引用 _teacher ,那麼不一定需要是外連接,我們可以使用內連接,並使用 _teacher 作為兩個外連接的驅動表。

另一個問題是SELECT列表中的非聚合...例如 DATE_FORMAT((tm_date), '%m/%d/%y')

GROUP BY是年份和周,因此DATE_FORMAT的值是不確定的...它可以來自組內的 任何 tm_date 。 我們無法保證您將獲得一周的第一天,即一周內的最早日期或其他任何內容。

此外,省略了 WEEK 函數的第二個參數,因此默認為 default_week_format 系統變量。 就個人而言,我會避免使用 YEARWEEKCONCAT 函數,並使用更簡單的 DATE_FORMAT ,使用明確包含本週mode參數的日期格式字符串。

如果您想加入“週”,那麼連接謂詞應該是“週”值,而不是一周內的一個不確定日期。

(對於我們不知道的數據可能存在一些特定的約束...如果在給定的一周中_mileage中有行,在星期一,那麼我們保證在同一個星期一有_timecard。更一般的情況,我們不會有這種保證。)

即使我們確實有這種保證,我們也不能保證SELECT列表中的非聚合不會從星期二_timecard和星期四_mileage返回日期...(除非有某種保證數據將僅包括_timecard和_mileage上具有“星期一”日期的行。 由此可見,非聚合表達式不是連接謂詞的可靠表達式。

假設 ds_id_teacher 上是唯一的,並且由 _mileage_timecard 的外鍵 ds_id 引用,則類似這樣的內容:

SELECT i.last_name
     , i.first_name
     , tm.dates
     , tm.total_hours
     , mm.total_minutes
  FROM bhds_teacher i 
  LEFT
  JOIN ( SELECT t.ds_id
              , DATE_FORMAT( t.tm_date,'%Y/%U')          AS week_
              , DATE_FORMAT( MIN(t.tm_date) ,'%m/%d/%y') AS dates
              , SUM(t.tm_hours)                          AS total_hours
           FROM bhds_timecard t
          WHERE t.tm_date BETWEEN '2016-04-11' AND '2016-04-30'   -- <
            AND t.ds_id = 5                                       -- <
          GROUP
             BY t.ds_id
              , DATE_FORMAT( t.tm_date,'%Y/%U')                   -- week
       ) tm
    ON tm.ds_id = i.ds_id
  LEFT
  JOIN ( SELECT m.ds_id
              , DATE_FORMAT( m.mil_date,'%Y/%U')           AS week_
              , DATE_FORMAT( MIN(m.mil_date), '%m/%d/%y' ) AS dates
              , SUM( m.drive_time )                        AS total_minutes 
           FROM bhds_mileage m
          WHERE m.mil_date BETWEEN '2016-04-11' AND '2016-04-30'  -- <
            AND m.ds_id = 5                                       -- <
          GROUP
             BY m.ds_id
              , DATE_FORMAT( m.mil_date,'%Y/%U')                  -- week
       ) mm
    ON mm.ds_id = i.ds_id
   AND mm.week_ = tm.week_
 WHERE i.ds_id = 5                                                -- <
 ORDER
    BY i.last_name ASC, tm.dates ASC




join