加入两个查询到一个为了加入结果
问题描述：
我有一个小的SQL问题给你。加入两个查询到一个为了加入结果
我有两个疑问：
SELECT tbl_CostPrevisions.Month, SUM(tbl_CostPrevisions.Value) AS 'COST' FROM tbl_CostPrevisions
WHERE tbl_CostPrevisions.Year = @YEAR
GROUP BY tbl_CostPrevisions.Month
SELECT tbl_IncomePrevisions.Month, SUM(tbl_IncomePrevisions.Value) AS 'INCOME' FROM tbl_IncomePrevisions
WHERE tbl_IncomePrevisions.Year = @YEAR
GROUP BY tbl_IncomePrevisions.Month
给予的结果是这样的：
1 48550,41
2 61082,86
3 49479,63
4 46529,74
5 46549,74
6 48619,63
7 49649,63
8 48279,92
9 48192,42
10 48467,42
11 48201,42
12 48262,42
而且
1 8123,16
2 9174,15
3 50009,12
4 50705,00
5 31971,00
6 15217,00
7 13151,00
8 9677,00
9 9616,00
10 9653,00
11 9698,00
12 9605,00
我怎么能写只是一个查询，这将使我的以下结果？
1 48550,41 8123,16
2 61082,86 9174,15
3 49479,63 50009,12
4 46529,74 50705
5 46549,74 31971
6 48619,63 15217
7 49649,63 13151
8 48279,92 9677
9 48192,42 9616
10 48467,42 9653
11 48201,42 9698
12 48262,42 9605
感谢
答
尝试以下内部联接：
SELECT tbl_CostPrevisions.Month, SUM(tbl_CostPrevisions.Value) AS 'COST' , SUM(tbl_IncomePrevisions.Value) AS 'INCOME'
FROM tbl_CostPrevisions
INNER JOIN tbl_IncomePrevisions on tbl_CostPrevisions.Month = tbl_IncomePrevisions.Month
WHERE tbl_CostPrevisions.Year = @YEAR AND tbl_IncomePrevisions.Year = @YEAR
GROUP BY tbl_CostPrevisions.Month
答
SELECT
tbl_CostPrevisions.Month,
SUM(tbl_CostPrevisions.Value) AS 'COST',
SUM(tbl_IncomePrevisions.Value) AS 'INCOME'
FROM
tbl_CostPrevisions,
tbl_IncomePrevisions
WHERE tbl_CostPrevisions.Year = @YEAR
AND tbl_IncomePrevisions.Year = @YEAR
AND tbl_CostPrevisions.Month = tbl_IncomePrevisions.Month
GROUP BY
tbl_CostPrevisions.Month,
tbl_IncomePrevisions.Month
答
尝试下面的查询。 tbl_IncomePrevisions上的INNER JOIN可以使用大量的数据。我已经把表格命名为让你更容易。
SELECT cp.Month, SUM(cp.Value) AS 'COST', SUM(ip.Value) AS 'INCOME'
FROM tbl_CostPrevisions cp
INNER JOIN tbl_IncomePrevisions ip ON ip.Month = cp.Month AND ip.Year = cp.Year
WHERE cp.Year = @YEAR AND ip.Year = @YEAR
GROUP BY cp.Month, ip.Month
工程就像一个魅力！谢谢 – MaiOM 2012-04-04 08:10:50