日期:2014-05-18  浏览次数:20611 次

如何求和???


如何根据 “YearMonth”是“2009”开头 和 “RegionName”不同的记录,求“SumQty”的和?

------解决方案--------------------
SQL code
select RegionName,sum(SumQty)as SumQty from table_name where left(yearMonth,4)=2009
group by RegionName

------解决方案--------------------
select RegionName,sum(SumQty) as SumQty,YearMonth from tbl
where left(YearMonth ,4)='2009'
group by RegionName and left(YearMonth ,4)

------解决方案--------------------
SQL code


GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
RName varchar(20),
SumQty decimal(18,2),
YearMonth varchar(6)
)
GO
INSERT TBL
SELECT '沪北小区',25452.00,'200901' UNION ALL
SELECT '沪北小区',35452.00,'200901' UNION ALL
SELECT '嘉定校区',26452.00,'200901' UNION ALL
SELECT '南校区',  25452.00,'200901' UNION ALL
SELECT '四平校区',25422.00,'200901' UNION ALL
SELECT '彭五小区',52452.00,'200901' UNION ALL
SELECT '沪北小区',21252.00,'200902' union all
SELECT '沪西小区',20452.00,'200902'

select RName,SUM(SumQty) as SumQty,left(YearMonth,4) as [Year]
from tbl where left(YearMonth,4)='2009'
group by RName,left(YearMonth,4)

/*
RName    SumQty    Year
沪北小区    82156.00    2009
沪西小区    20452.00    2009
嘉定校区    26452.00    2009
南校区    25452.00    2009
彭五小区    52452.00    2009
四平校区    25422.00    2009
*/

------解决方案--------------------
探讨

left(YearMonth,4)

------解决方案--------------------
select left(yearmonth,4) yearmonth , RegionName , sum(SumQty) SumQty from tb group by left(yearmonth,4) , RegionName

select substring(yearmonth,1,4) yearmonth , RegionName , sum(SumQty) SumQty from tb group by substring(yearmonth,1,4) , RegionName

------解决方案--------------------
探讨

引用:

SQL code


GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
RName varchar(20),
SumQty decimal(18,2),
YearMonth varchar(6)
)
GO
INSERT TBL
SE……

------解决方案--------------------
select sum(sumqty),regionname from a where left(yearMonth,4) = '2009' group by regionName;
------解决方案--------------------
3楼正解