[SQL] 두 개의 SQL 쿼리를 가입

두 개의 SQL 쿼리를 가입

첫 번째는 어디 두 SQL 쿼리를 가지고 :

select Activity, SUM(Amount) as "Total Amount 2009"
from Activities, Incomes
where Activities.UnitName = ? AND
      Incomes.ActivityId = Activities.ActivityID
GROUP BY Activity
ORDER BY Activity;

그리고 두 번째는 :

select Activity, SUM(Amount) as "Total Amount 2008"
from Activities, Incomes2008
where Activities.UnitName = ? AND
      Incomes2008.ActivityId = Activities.ActivityID
GROUP BY Activity
ORDER BY Activity;

(그나마 '?', 그들은 BIRT에 매개 변수를 대표하는 마음). 내가 달성하고자하는 것은 다음과 같다 : 나는 첫 번째 쿼리와 같은 반환하는 SQL 쿼리를 원하지만 정확하게 (2 쿼리) "총액 2008"과 같은 추가 (제 3 호) 컬럼.

해결법

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

   1.일부의 DBMS는 ALIAS_NAME 구문 AS (SELECT ...)에서 지원합니다.

   일부의 DBMS는 ALIAS_NAME 구문 AS (SELECT ...)에서 지원합니다.

   임시 테이블로 당신이 원래의 쿼리를 생각하십시오. 당신은 너무처럼를 조회 할 수 있습니다 :

   SELECT t1.Activity, t1."Total Amount 2009", t2."Total Amount 2008"
   FROM (query1) as t1, (query2) as t2
   WHERE t1.Activity = t2.Activity
   

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

   2.

   SELECT Activity, arat.Amount "Total Amount 2008", abull.Amount AS "Total Amount 2009"
   FROM
     Activities a
   LEFT OUTER JOIN
     (
     SELECT ActivityId, SUM(Amount) AS Amount
     FROM Incomes ibull
     GROUP BY
       ibull.ActivityId
     ) abull
   ON abull.ActivityId = a.ActivityID
   LEFT OUTER JOIN
     (
     SELECT ActivityId, SUM(Amount) AS Amount
     FROM Incomes2008 irat
     GROUP BY
       irat.ActivityId
     ) arat
   ON arat.ActivityId = a.ActivityID
   WHERE a.UnitName = ?
   ORDER BY Activity
   

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

   3.난 그냥 연합 (EU)을 사용

   난 그냥 연합 (EU)을 사용

   두 번째 쿼리에서 여분의 열 이름을 추가하고 다른 쿼리의 모든에서 '해당 위치를 추가

   예

   //reverse order to get the column names
   select top 10 personId, '' from Telephone//No Column name assigned 
   Union 
   select top 10 personId, loanId from loan
   

4. ==============================

   4.여기에 나를 위해 일한 내용은 다음과 같습니다

   여기에 나를 위해 일한 내용은 다음과 같습니다

   select visits, activations, simulations, simulations/activations
      as sims_per_visit, activations/visits*100
      as adoption_rate, simulations/activations*100
      as completion_rate, duration/60
      as minutes, m1 as month, Wk1 as week, Yr1 as year 
   
   from
   (
       (select count(*) as visits, year(stamp) as Yr1, week(stamp) as Wk1, month(stamp)
       as m1 from sessions group by week(stamp), year(stamp)) as t3
   
       join
   
       (select count(*) as activations, year(stamp) as Yr2, week(stamp) as Wk2,
       month(stamp) as m2 from sessions where activated='1' group by week(stamp),
       year(stamp)) as t4
   
       join
   
       (select count(*) as simulations, year(stamp) as Yr3 , week(stamp) as Wk3,
       month(stamp) as m3 from sessions where simulations>'0' group by week(stamp),
       year(stamp)) as t5
   
       join
   
       (select avg(duration) as duration, year(stamp) as Yr4 , week(stamp) as Wk4,
       month(stamp) as m4 from sessions where activated='1' group by week(stamp),
       year(stamp)) as t6
   )
   where Yr1=Yr2 and Wk1=Wk2 and Wk1=Wk3 and Yr1=Yr3 and Yr1=Yr4 and Wk1=Wk4
   

   내가 조인 사용하지 않는 노동 조합 (I는 각 쿼리에 대해 서로 다른 열을 필요로하는이 같은 열에서 풋 모두 가입) 그리고 나는 그들이 나에게 오류를주고 있었기 때문에 (리암은 무엇을하고 있었는지에 비해) 인용 부호를 떨어졌다.

   감사! 나는이 페이지없이 뽑아 수 없었다! PS : 당신이 색상으로 포맷 된 명령문을 받고있어 얼마나 미안 나도 몰라. 기타

5. ==============================

   5.아마도 아니라 가장 우아한 방법은이 문제를 해결하기 위해

   아마도 아니라 가장 우아한 방법은이 문제를 해결하기 위해

   select  Activity, 
           SUM(Amount) as "Total_Amount",
           2009 AS INCOME_YEAR
   from    Activities, Incomes
   where Activities.UnitName = ? AND
         Incomes.ActivityId = Activities.ActivityID
   GROUP BY Activity
   ORDER BY Activity;
   
   UNION
   
   select  Activity, 
           SUM(Amount) as "Total_Amount",
           2008 AS INCOME_YEAR
   from Activities, Incomes2008
   where Activities.UnitName = ? AND
         Incomes2008.ActivityId = Activities.ActivityID
   GROUP BY Activity
   ORDER BY Activity;
   

6. ==============================

   6.당신이 값이 모두 몇 년 동안 모든 활동에 존재한다고 가정한다면 단지 내부가 아니라 다음에 가입 할

   당신이 값이 모두 몇 년 동안 모든 활동에 존재한다고 가정한다면 단지 내부가 아니라 다음에 가입 할

    select act.activity, t1.amount as "Total 2009", t2.amount as "Total 2008"
   from Activities as act,
       (select activityid,  SUM(Amount) as amount
       from Activities, Incomes
       where Activities.UnitName = ? AND
             Incomes.ActivityId = Activities.ActivityID
       GROUP BY Activityid) as t1,
       (select activityid, SUM(Amount) as amount
       from Activities, Incomes2008
       where Activities.UnitName = ? AND
             Incomes2008.ActivityId = Activities.ActivityID
       GROUP BY Activityid) as t2
       WHERE t1.activityid= t2.activityid
       AND act.activityId = t1.activityId
       ORDER BY act.activity
   

   이 가정 수없는 경우 외부 조인을하고 보면

7. ==============================

   7.이 시도:

   이 시도:

   select Activity, SUM(Incomes.Amount) as "Total Amount 2009", SUM(Incomes2008.Amount) 
   as "Total Amount 2008" from
   Activities, Incomes, Incomes2008
   where Activities.UnitName = ?  AND
   Incomes.ActivityId = Activities.ActivityID  AND
   Incomes2008.ActivityId = Activities.ActivityID GROUP BY
   Activity ORDER BY Activity;
   

   기본적으로 당신은 당신의 첫 번째 쿼리의 출력과 Incomes2008 테이블을 가입해야합니다.

8. ==============================

   8.당신은 또한 다음과 같은 CTE를 사용할 수 있습니다.

   당신은 또한 다음과 같은 CTE를 사용할 수 있습니다.

   With cte as
   (select Activity, SUM(Amount) as "Total Amount 2009"
      from Activities, Incomes
     where Activities.UnitName = ? AND
           Incomes.ActivityId = Activities.ActivityID
     GROUP BY Activity
   ),
   cte1 as 
   (select Activity, SUM(Amount) as "Total Amount 2008"
      from Activities, Incomes2008
     where Activities.UnitName = ? AND
           Incomes2008.ActivityId = Activities.ActivityID
     GROUP BY Activity
   )
   Select cte.Activity, cte.[Total Amount 2009] ,cte1.[Total Amount 2008]      
     from cte join cte1 ON cte.ActivityId = cte1.ActivityID
    WHERE a.UnitName = ?
    ORDER BY cte.Activity 
   

from https://stackoverflow.com/questions/564997/join-two-sql-queries by cc-by-sa and MIT license