表Aa b c d e
1 2 3 4 5
2 4 5 4 9
3 5 3 8 9现在要根据c列和d列作为两个条件进行获取sum(b)的值,如何只查询获取一次A的数据,得出我需要的值。
即:select sum(b) from A where c>3
select sum(b) from A where d>4
我实际的数据肯定不是一个表的,大约有6个表,要从6个表中取不同条件的数据。
怎么写最好呢?
1 2 3 4 5
2 4 5 4 9
3 5 3 8 9现在要根据c列和d列作为两个条件进行获取sum(b)的值,如何只查询获取一次A的数据,得出我需要的值。
即:select sum(b) from A where c>3
select sum(b) from A where d>4
我实际的数据肯定不是一个表的,大约有6个表,要从6个表中取不同条件的数据。
怎么写最好呢?
union all
select sum(b) from A where d>4
...
union all
select sum(b) from A where d>4
...
如果这样,我就必须:select sum(b),0 from A,B,C,D,E...... where ...
union all
select 0,sum(b) from A,B,C,D,E ..... where ...
.......那岂不是效率很低?
inner join wcChargeRecord as Record on Detail.ChargeRecordID =Record.ID
inner join wcUserBaseInfo as Info on Record.UserID=Info.ID
inner join wcChargeProceed as Proceed on Record.ChargeProceedID=Proceed.ID
inner join wcListUDP a on Detail.WaterKindID = a.ID
inner join wcListUDP b on a.ParentID = b.ID
left join (select ID,ParentID,UDPName from wcListUDP ) dd on dd.ParentID=a.ID and Detail.WaterKindID=dd.ID
left join
(select d.ID,WaterKind= bb.UDPName,
WaterMuch =sum(isnull(d.WaterMuch,0)),
TotalMoney=sum(isnull(d.ChargeMoney,0))
from wcCopyNoteData n inner join wcComputeChargeRecord c
on n.ID = c.CopyNoteDataID
inner join wcChargeRecord r on c.ID = r.ComputeChargeRecordID
inner join wcChargeDetail d on r.ID = d.ChargeRecordID
inner join wcChargeProceed p on r.ChargeProceedID = p.ID
inner join wcListUDP aa on WaterKindID = aa.ID inner join wcListUDP bb on aa.ParentID = bb.ID
where CopyYear=2009 and CopyMonth=2 and year(ChargeDate) = 2009 and day(ChargeDate)=10
and month(ChargeDate) = 2
group by WaterKind, bb.UDPName,d.ID,P.ChargeDate
) as MonthPay on MonthPay.ID=Detail.ID
left join
(select d.ID,WaterKind= bb.UDPName,
WaterMuch2 = case when Convert(datetime,convert(varchar(10),P.ChargeDate))
between dateadd(year,-1,Convert(datetime,cast(2009 as varchar(4)) + '-' + '12' + '-' + '26',120)) and
Convert(datetime,cast(2009 as varchar(4)) + '-' + '12' + '-' + '25',120)
then sum(isnull(d.WaterMuch,0)) else 0 end,
TotalMoney2 =case when Convert(datetime,convert(varchar(10),P.ChargeDate))
between dateadd(year,-1,Convert(datetime,cast(2009 as varchar(4)) + '-' + '12' + '-' + '26',120)) and
Convert(datetime,cast(2009 as varchar(4)) + '-' + '12' + '-' + '25',120)
then sum(isnull(d.ChargeMoney,0)) else 0 end
from wcCopyNoteData n inner join wcComputeChargeRecord c
on CopyYear>2009-1 and n.ID = c.CopyNoteDataID
inner join wcChargeRecord r on c.ID = r.ComputeChargeRecordID
inner join wcChargeDetail d on r.ID = d.ChargeRecordID
inner join wcChargeProceed p on year(ChargeDate) = 2009 and day(ChargeDate)=10
and month(ChargeDate) = 2 and r.ChargeProceedID = p.ID
inner join wcListUDP aa on WaterKindID = aa.ID inner join wcListUDP bb on aa.ParentID = bb.ID
group by WaterKind, bb.UDPName,d.ID,P.ChargeDate
) as MonthPay2 on MonthPay2.ID=Detail.ID
where Detail.ChargeFeeID=1 and year(Proceed.ChargeDate)=2009 and month(Proceed.ChargeDate)=2
and day(Proceed.ChargeDate)=10 and Info.Company='绵阳水务集团'
这里明显对一个表去取了很次数据,而且这些表都是百万级的,如果这样,查询的效率会很低。
小弟现在就不晓得怎么改进了。