[SQL] 두 SQL LEFT는 생산 잘못된 결과를 JOINS

두 SQL LEFT는 생산 잘못된 결과를 JOINS

내가 3 개 테이블이 :

users(id, account_balance)
grocery(user_id, date, amount_paid)
fishmarket(user_id, date, amount_paid)

두 fishmarket 및 식료품 테이블은 다른 날짜 및 특정 사용자에 대한 모든 지불 또는 아무것도 양의 같은 USER_ID에 대한 여러 번있을 수 있습니다. 나는 다음과 같은 쿼리를 시도 할 때 :

SELECT
     t1."id" AS "User ID",
     t1.account_balance AS "Account Balance",
     count(t2.user_id) AS "# of grocery visits",
     count(t3.user_id) AS "# of fishmarket visits"
FROM users t1
LEFT OUTER JOIN grocery t2 ON (t2.user_id=t1."id") 
LEFT OUTER JOIN fishmarket t3 ON (t3.user_id=t1."id") 
GROUP BY t1.account_balance,t1.id
ORDER BY t1.id

그것은 잘못된 결과를 "1", "12", "12". 내가 LEFT하려고 할 때하지만 "1", "3", "4"입니다 중 식료품 또는 fishmarket 방문을하는 경우 올바른 결과를 생성 한 테이블에 가입하세요.

어떻게 내가 여기 잘못 한거야? 나는 PostgreSQL을 9.1을 사용하고 있습니다.

해결법

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

   1.(괄호는 달리 지시하지 않는 한) 왼쪽에서 오른쪽으로 처리됩니다 조인. 당신은 하나의 사용자에게 세 가지 식료품을 LEFT JOIN (또는 유사한 효과를 가입)하는 경우는 3 행 (× 3 일)을 얻는다. 그런 다음 동일한 사용자 4 fishmarkets에 가입하는 경우, 당신은 당신이 희망 한 수처럼, 그것에 추가, 결과의 이전 카운트를하지 곱, 12 (3 × 4) 행을 얻을. 이것 모두 식료품 및 fishmarkets에 대한 방문을 곱하여.

   (괄호는 달리 지시하지 않는 한) 왼쪽에서 오른쪽으로 처리됩니다 조인. 당신은 하나의 사용자에게 세 가지 식료품을 LEFT JOIN (또는 유사한 효과를 가입)하는 경우는 3 행 (× 3 일)을 얻는다. 그런 다음 동일한 사용자 4 fishmarkets에 가입하는 경우, 당신은 당신이 희망 한 수처럼, 그것에 추가, 결과의 이전 카운트를하지 곱, 12 (3 × 4) 행을 얻을. 이것 모두 식료품 및 fishmarkets에 대한 방문을 곱하여.

   당신은 다음과 같은 작업을 할 수 있습니다 :

   SELECT u.id
        , u.account_balance
        , g.grocery_visits
        , f.fishmarket_visits
   FROM   users u
   LEFT   JOIN (
      SELECT user_id, count(*) AS grocery_visits
      FROM   grocery
      GROUP  BY user_id
      ) g ON g.user_id = u.id
   LEFT   JOIN (
      SELECT user_id, count(*) AS fishmarket_visits
      FROM   fishmarket
      GROUP  BY user_id
      ) f ON f.user_id = u.id
   ORDER  BY u.id;
   

   하나 또는 소수의 사용자에 대한 값을 집계하려면 제공 @Vince 같은 상관 관계 서브 쿼리는 잘 있습니다. 전체 테이블 또는 그 주요 부품의 경우, n 개의 테이블을 집계 한 번 결과에 가입 (훨씬) 더 효율적입니다. 이 방법은, 우리는 또한 외부 쿼리에 의해 다른 그룹이 필요하지 않습니다.

   grocery_visits 및 fishmarket_visits는 각각의 테이블에있는 모든 관련 항목이없는 사용자에 대한 NULL입니다. 대신 0 (또는 임의의 수)이 필요한 경우, 사용 COALESCE :

   SELECT u.id
        , u.account_balance
        , COALESCE(g.grocery_visits   , 0) AS grocery_visits
        , COALESCE(f.fishmarket_visits, 0) AS fishmarket_visits
   FROM   ...
   

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

   2.당신이 사전에 분류 결과를보고하여 그룹을 데려 갈 경우 카운트가 당신이었다 수신이 만들어진 이유를 원래 쿼리를 들어, 당신은 볼 수 있습니다.

   당신이 사전에 분류 결과를보고하여 그룹을 데려 갈 경우 카운트가 당신이었다 수신이 만들어진 이유를 원래 쿼리를 들어, 당신은 볼 수 있습니다.

   아마도 하위 쿼리를 사용하는 다음 쿼리는 의도 한 결과를 얻을 것이다 :

   SELECT
    t1."id" AS "User ID",
    t1.account_balance AS "Account Balance",
    (SELECT count(*) FROM grocery     t2 ON (t2.user_id=t1."id")) AS "# of grocery visits",
    (SELECT count(*) FROM fishmarket  t3 ON (t3.user_id=t1."id")) AS "# of fishmarket visits"
   FROM users t1
   ORDER BY t1.id
   

3. ==============================

   3.사용자 테이블이 식료품 테이블에 조인 할 때, 일치하는 3 개 레코드가 있기 때문입니다. 그러면이 세 각 레코드는 레코드 (12)를 생성 fishmarket에서 4 개 레코드와 일치한다. 당신은 당신이 찾고있는 것을 얻을 서브 쿼리가 필요합니다.

   사용자 테이블이 식료품 테이블에 조인 할 때, 일치하는 3 개 레코드가 있기 때문입니다. 그러면이 세 각 레코드는 레코드 (12)를 생성 fishmarket에서 4 개 레코드와 일치한다. 당신은 당신이 찾고있는 것을 얻을 서브 쿼리가 필요합니다.

from https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result by cc-by-sa and MIT license