SQL 加入两个sql查询

Join two sql queries

提问by Liam

I have two SQL queries, where the first one is:

我有两个 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;

and the second one is:

第二个是：

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;

(Dont mind the '?', they represent a parameter in birt). What i want to achieve is the following: I want an SQL query that returns the same as the first query, but with an additional (third) column which looks exactly like "Total Amount 2008" (from the 2nd query).

（不要介意'？'，它们代表一个参数）。我想要实现的是以下内容：我想要一个返回与第一个查询相同的 SQL 查询，但有一个额外的（第三个）列，它看起来与“2008 年的总金额”（来自第二个查询）完全一样。

回答by Liam

Some DBMSs support the FROM (SELECT ...) AS alias_namesyntax.

一些 DBMS 支持该FROM (SELECT ...) AS alias_name语法。

Think of your two original queries as temporary tables. You can query them like so:

将您的两个原始查询视为临时表。您可以像这样查询它们：

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

回答by Quassnoi

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

回答by cgreeno

I would just use a Union

我只会使用一个联盟

In your second query add the extra column name and add a ''in all the corresponding locations in the other queries

在您的第二个查询中添加额外的列名并''在其他查询中的所有相应位置添加一个

Example

例子

//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

回答by Haitham Alhumsi

Here's what worked for me:

以下是对我有用的内容：

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 used joins, not unions (I needed different columns for each query, a join puts it all in the same column) and I dropped the quotation marks (compared to what Liam was doing) because they were giving me errors.

我使用了连接，而不是联合（我需要为每个查询使用不同的列，连接将它们全部放在同一列中）并且我去掉了引号（与 Liam 所做的相比），因为它们给了我错误。

Thanks! I couldn't have pulled that off without this page! PS: Sorry I don't know how you're getting your statements formatted with colors. etc.

谢谢！如果没有这个页面，我就无法完成它！PS：对不起，我不知道你是如何用颜色格式化你的陈述的。等等。

回答by Rakesh_SQL_Specialist

You can use CTE also like below.

您也可以像下面一样使用 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 

回答by Shivasubramanian A

Try this:

尝试这个：

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;

Basically you have to JOIN Incomes2008 table with the output of your first query.

基本上，您必须将第一个查询的输出加入 Incomes2008 表。

回答by Il-Bhima

If you assume that values exist for all activities in both years then just do an inner join as follows

如果您假设这两年中所有活动的值都存在，则只需按如下方式进行内部联接

 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

If you can't assume this, then look at doing an outer join

如果你不能假设这一点，那么看看做一个外连接

回答by Natrium

perhaps not the most elegant way to solve this

也许不是解决这个问题的最优雅的方法

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;