[SQL] MYSQL에서 SUM의 문제를 테이블에 참여

MYSQL에서 SUM의 문제를 테이블에 참여

, 문제는 항상 내가 항상 문제가 테이블을 조인에 합계를 받고 있었다이다, 나는 두 개의 쿼리를 실행하여 원하는 결과를 얻을 수있는이 두 개의 질의가 하나가 쿼리에 가입하게 결합 될 수 있는지, 궁금하고, 여기에 쿼리 I입니다 이 쿼리에 가입 내 시도

쿼리 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

출력은 여기, 36.00, 33.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 

괄호가 예상되는 것입니다

이것은 (279) 1044 (271) 1086 (281), (1215)에 출력한다

해결법

1. ==============================

   1.여러 메인 쿼리에 조인 사용하면 합계가 다른 테이블에 일치하는 행의 수를 곱한 얻을 수 있도록, 당신은 모든 테이블의 외적으로 끝낼. 당신은 하위 쿼리로 금액을 이동해야합니다.

   여러 메인 쿼리에 조인 사용하면 합계가 다른 테이블에 일치하는 행의 수를 곱한 얻을 수 있도록, 당신은 모든 테이블의 외적으로 끝낼. 당신은 하위 쿼리로 금액을 이동해야합니다.

   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
   

2. ==============================

   2.한 테이블에서 (그룹 내) 각 세부 행이 다른 테이블에서 세부 행이 "가입 십자가"되기 때문에 문제 ... bhds_mileage와 bhds_timecard 사이의 부분 데카르트 제품 (외적)의 부부가있다. GROUP BY 조작이 행을 축소하고 합을 계산하기 전에 그리고 그 발생합니다. 그것은 당신이보고있는 이유는 값을 "팽창"을 설명합니다.

   한 테이블에서 (그룹 내) 각 세부 행이 다른 테이블에서 세부 행이 "가입 십자가"되기 때문에 문제 ... bhds_mileage와 bhds_timecard 사이의 부분 데카르트 제품 (외적)의 부부가있다. GROUP BY 조작이 행을 축소하고 합을 계산하기 전에 그리고 그 발생합니다. 그것은 당신이보고있는 이유는 값을 "팽창"을 설명합니다.

   그에 대한 해결 방법은 ... 인라인 뷰에서 SUM () 집계 중 적어도 하나를 계산 얻을 수있는 SUM () / 첫 번째 쿼리 중 하나가하는 것처럼 수행 GROUP BY (). 명확하게하기 위해, 당신은 원래의 쿼리에 동일한 일을하고 인라인 뷰의 결과에 가입 할 수있다.

   MySQL은 기본적으로 전체 외부 조인을 지원하지 않습니다. 테이블 중 하나는 운전 테이블이 필요합니다. 예를 들어, 우리는 구동 테이블로 _timecard 사용할 수 있지만 그것은 우리가 _mileage에서 해당 행을 반환하기 위해 _timecard에서 특정 주에 대한 행을 반환해야한다는 것을 의미한다. 즉, _timecard의 행없이, 우리는 _mileage에서 행을 가져올 수 없습니다.

   우리는이 bhds_teacher에 가입한다는 것은 외부 조인 알 수 있습니다. 우리는 모두 _mileage 및 _timecard, _teacher를 참조에 ds_id 사이에 외래 키 제약 조건이있는 경우, 그 반드시 외부, 우리는 내부 조인 사용할 수 있습니다 가입 할 필요가 없습니다 것입니다, 2 개 개의 외부의 운전 테이블로 사용 _teacher 조인.

   또 다른 문제는 SELECT 목록에서 비 집계입니다 ... 예를 DATE_FORMAT ((tm_date) '%의 m / % D / % Y')

   ...이 그룹 내 모든 tm_date에서 할 수있는 DATE_FORMAT의 값이 결정되지 않도록 GROUP BY는 올해 주에 있습니다. 당신이주의 첫 날, 주 또는 무엇이든 내에서 가장 빠른 날짜를 얻을 수 있습니다 것이라는 보장은 없습니다.

   즉 default_week_format 시스템 변수를 기본값 그럼에도, WEEK 함수의 두번째 파라미터는 생략한다. 개인적으로, 나는 올해, WEEK 및 CONCAT 함수를 사용하지 거라고, 명시 적으로 주간 모드 매개 변수를 포함하는 날짜 형식 문자열을 사용하여, 간단한 DATE_FORMAT과 함께 할 것입니다.

   당신이 "주"에 가입하려는 경우,이 술어는 "주"값에 수 주 내에없는 한 확정 날짜해야 가입 할 수 있습니다.

   (월요일에, 우리가 인식하지 못합니다 ... _mileage 행이 주어진 일주일이있는 경우 데이터에 대한 일부 특정 제약이있을 수 있습니다, 우리는 같은 월요일에 대한 _timecard이 보장됩니다.에서 일반적인 경우, 우리는 보장이없는 것입니다.)

   우리가 보증을 할 경우에도, 우리는 SELECT 목록에서 비 집계는 화요일 _timecard에서 날짜를 반환하지 않습니다 보장되지 않으며, 목요일 _mileage (보증의 어떤 종류가 아니라면 그 데이터 _timecard 및 _mileage에 "월요일"날짜) 만 행이 포함됩니다. 그 Withouat, 비 집계 표현식은 술어 가입에 대한 신뢰할 수있는 표현이 아니다.

   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
   

from https://stackoverflow.com/questions/37978511/join-tables-with-sum-issue-in-mysql by cc-by-sa and MIT license