(SELECT     SUM([金额(美金)]) AS [月金额]
FROM         Source
WHERE     (月份 = 1) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 2) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 3) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 4) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 5) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 6) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 7) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 8) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 9) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 10) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 11) AND (当前状态 = N'已发报告')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 12) AND (当前状态 = N'已发报告')
)
union all
(SELECT     SUM([金额(美金)]) AS [常规检验金额]
FROM         Source
WHERE     (月份 = 1 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 2 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 3 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 4 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 5 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 6 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 7 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 8 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 9 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 10 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 11 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
union all
SELECT     SUM([金额(美金)])
FROM         Source
WHERE     (月份 = 12 AND 当前状态 = N'已发报告') and (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI')
)

获取结果如下：

我想要的结果如下：

应该如何写？

select 月份, sum([金额(美金)]) as [月金额],
       sum(case when (检验类型 = N'PSI' or 检验类型 = N'DPI' or 检验类型 = N'PPI') then [金额(美金)] else 0 end) as [常规检验金额]
from source where 当前状态 = N'已发报告' group by 月份

以上语句未验证，请调试下！

[ 本帖最后由 volte 于 2014-7-30 16:46 编辑 ]

楼主写那么长 看着不眼花？