将具有不同模式的两个SELECT语句中的“生成”结果连接到一个表中

So I have the following SELECT statements:

所以我有以下SELECT语句:

SELECT COUNT(A.Award) AS US, SUBSTRING(CAST(M.Year as char(4)), 0 , 4)  AS Decade
FROM Movies M, Awards A
WHERE {SOME WHERE CLAUSE}
GROUP BY Decade;

and

SELECT COUNT(*) AS Total, SUBSTRING(CAST(A2.Year as char(4)), 0 , 4) AS Decade
FROM Awards A2
WHERE {SOME WHERE CLAUSE}
GROUP BY Decade;

The first one is "generating" a table with columns (US, Decade) and the second one is "generating" another table with columns (Total, Decade). I want to join those two tables so that I get a table (US, Total, Decade). How can I do it?

第一个是“生成”带有列的表(US,Decade),第二个是“生成”另一个带有列的表(Total,Decade)。我想加入这两个表,以便得到一个表(US,Total,Decade)。我该怎么做?

1 个解决方案

#1

Put them in subqueries and do a JOIN:

将它们放在子查询中并执行JOIN:

SELECT a.US, a.decade, b.total FROM

  (SELECT COUNT(A.Award) AS US, SUBSTRING(CAST(M.Year as char(4)), 0 , 4)  AS Decade
  FROM Movies M, Awards A
  WHERE {SOME WHERE CLAUSE}
  GROUP BY Decade ) AS a

INNER JOIN

  (SELECT COUNT(*) AS Total, SUBSTRING(CAST(A2.Year as char(4)), 0 , 4) AS Decade
  FROM Awards A2
  WHERE {SOME WHERE CLAUSE}
  GROUP BY Decade) AS b

ON a.decade = b.decade