注册 登录
编程论坛 → SQL Server论坛

这个一条查询语句该怎么写？

等待冰柠檬 发布于 2010-08-05 15:05， 838 次点击
Select VehicleBrands,sum(Amount)
          from CostReports
          where CostReports.CostType = 1 and CreateTime between StartDate and EndDate
          group by VehicleBrands

      Select VehicleBrands,sum(Amount/ApportionMonth)
          from CostReports
          where CostReports.CostType = 2  and ThisMonth < StartMonth + ApportionMonth
      and ApportionMonth > 0
          group by VehicleBrands
select VehicleBrands,sum(Amount)
          from CostReports
          where CostReports.CostType = 3
          group by VehicleBrands
如何将这三条语句查询的的结果进行累加
Select VehicleBrands,sum(第1种查询的结果+第2种查询的结果+第3种查询的结果) as ReportCost
from CostReports
group by VehicleBrands
注意：第X种查询的结果用查询语句表示

[ 本帖最后由 等待冰柠檬 于 2010-8-5 15:19 编辑 ]
6 回复
#2
happynight2010-08-05 16:51
SELECT a.VehicleBrands,suma+sumb+sumc
FROM (
Select VehicleBrands,sum(Amount) suma
          from CostReports
          where CostReports.CostType = 1 and CreateTime between StartDate and EndDate
          group by VehicleBrands) a
INNER JOIN
(      Select VehicleBrands,sum(Amount/ApportionMonth) sumb
          from CostReports
          where CostReports.CostType = 2  and ThisMonth < StartMonth + ApportionMonth
      and ApportionMonth > 0
          group by VehicleBrands)b
ON a.VehicleBrands=b.VehicleBrands
INNER JOIN (
select VehicleBrands,sum(Amount) sumc
          from CostReports
          where CostReports.CostType = 3
          group by VehicleBrands
) c
ON a.VehicleBrands=c.VehicleBrands
注意:假定 三个查询返回的VehicleBrands都能一一对应到
#3
等待冰柠檬2010-08-06 10:11
好像有问题吧！版主！
#4
happynight2010-08-06 10:58
以下是引用等待冰柠檬在2010-8-6 10:11:40的发言：

好像有问题吧！版主！
什么问题?请看我后面附加的条件说明,如果正是在我的说明造成的或者是另外的原因 详细说明下
#5
dearwolf41282010-08-06 12:10
select distinct VehicleBrands,((Select VehicleBrands,sum(Amount) suma from CostReports where CostReports.CostType = 1 and CreateTime between StartDate and EndDate group by VehicleBrands)+( Select VehicleBrands,sum(Amount/ApportionMonth) from CostReports where CostReports.CostType = 2  and ThisMonth < StartMonth + ApportionMonth
  and ApportionMonth > 0 group by VehicleBrands) + (select VehicleBrands,sum(Amount)  from CostReports  where CostReports.CostType = 3
          group by VehicleBrands)) as ReportCost from CostReports  where  VehicleBrands in (select VehicleBrands from CostReports)

我测过了，可以实现
#6
pumbaax102010-08-06 13:52
其实如果在VehicleBrands不能一一对应的情况下，你可以先把三个表的VehicleBrands查找出来distinct的，然后在left outer join那三个sum的表，然后就可以加总了。
#7
aei1352010-08-06 14:43
楼上说得没错,就这样
SELECT distinct CostReports.VehicleBrands,isnull(suma,0)+isnull(sumb,0)+isnull(sumc,0) amt
FROM CostReports  
left join (
Select VehicleBrands,sum(Amount) suma
          from CostReports
          where CostReports.CostType = 1 and CreateTime between StartDate and EndDate
          group by VehicleBrands) a
on CostReports.VehicleBrands=a.VehicleBrands
left JOIN
(      Select VehicleBrands,sum(Amount/ApportionMonth) sumb
          from CostReports
          where CostReports.CostType = 2  and ThisMonth < StartMonth + ApportionMonth
      and ApportionMonth > 0
          group by VehicleBrands)b
ON CostReports.VehicleBrands=b.VehicleBrands
left JOIN (
select VehicleBrands,sum(Amount) sumc
          from CostReports
          where CostReports.CostType = 3
          group by VehicleBrands
) c
ON CostReports.VehicleBrands=c.VehicleBrands
1
©2025, BC-CN.NET
电脑版