Sql分组合计小计查询

页面导航:首页 > 数据库 > MsSql > Sql分组合计小计查询

Sql分组合计小计查询

来源: 作者: 时间:2016-01-27 10:35 【

效果如下:用的表和字段:-table 实收水费:hx_t_received --字段收费部门id:hx_fdepartmentid 1、收费部门名称:hx_fdepartmentname 应收水费信息ID:hx_freceivableid--收费类别:hx_ftype (水费(1)

效果如下:

\

用的表和字段:

-table 实收水费:hx_t_received

--字段收费部门id:hx_fdepartmentid 1、收费部门名称:hx_fdepartmentname 应收水费信息ID:hx_freceivableid

--收费类别:hx_ftype (水费(1):再分[开户(table) 供水类别:自来水(100000001)==水费,中水(100000002)==中水费],代理费(2)==污水处理费)

--实收金额:hx_freceivedamount 创建时间:createdon 支付方式:hx_fpayway(现金:100,000,000与支票:100,000,001,充值账户:,000,002)

--交易状态:hx_fstate(除:【作废:,000,002、银行付款:,000,003】,入账:,000,000)

 

--table 应收水费:hx_t_receivable

--字段 水量信息ID:hx_fusedamountid 主键:hx_t_ReceivableId

--:hx_fcollchargesreceivable4(污水处理费),hx_freceivablefee(应收水费),hx_fusedamountid(水量信息)

--hx_fstate(水量状态计划:100000000),

--hx_frecordtype(抄表类型):正常,000,000 估水,000,001 未抄见,000,002

--hx_festimateamountreason(估水原因):不用水,000,000 无表,000,001 防冻,000,002 表停,000,003

--hx_fnotrecordreason(未抄见原因):未见表,000,000 有水,000,001 表埋,000,002 锁门,000,003 表不清,000,004

--hx_freading(本次抄表止度)

 

--table 用水水量:hx_t_waterusedamount

--字段 开户信息:hx_fcustomerandmeterrelaid 主键:hx_t_WaterUsedAmountId 负责团队:owningteam

hx_frecorder(抄表员),hx_fzone(区段),hx_fmeterid(水表),hx_fpayamount(结算水量),hx_fdepartmentid(收费部门)

 

--table 开户信息:hx_t_customerandmeterrela

--字段供水类别:hx_fwatertype 负责人:ownerid 主键:hx_t_CustomerAndMeterRelaId

 

--table 团队:team

--字段 主键:teamid 业务部门:businessunitid

 

--table 部门:businessunit

--字段 主键:businessunitid 上级部门:parentbusinessunitid 名称:Name

SQL代码:

 

declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()

declare @hx_ClosingAccountInfoId uniqueidentifier
set @hx_ClosingAccountInfoId='08AFEF9F-E174-46F2-855A-32F26BE38F3B'

-----------------SQL正文----------------------------------------------------------执行时间:
declare @FMonth int
declare @FYear int
select @FYear=t.hx_FYear,@FMonth=t.hx_FMonth from hx_ClosingAccountInfo t where 
[email protected]_ClosingAccountInfoId

 SELECT
	CASE
		WHEN (GROUPING(u.ParentBusinessUnitIdName) = 1) THEN '总计'
		ELSE ISNULL(u.ParentBusinessUnitIdName, '未知')
	END AS zfsName,--征收所名称
	CASE
		WHEN (GROUPING(t.Name) = 1 AND
			GROUPING(u.ParentBusinessUnitIdName) = 1) THEN ' '
		WHEN GROUPING(t.Name) = 1 THEN '小计'
		ELSE ISNULL(t.Name, '未知')
	END AS ItemName ,--抄表班组名称
	
sum(1) as AllCounts,
sum(case when w.hx_frecordtype='100000000' then 1 else 0 end) Normal,
sum(case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000000' then 1 else 0 end) NotUseWater,
sum(case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000001' then 1 else 0 end) NoMeterWater,
sum(case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000002' then 1 else 0 end) antifreeze,
sum(case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000003' then 1 else 0 end) MeterStop,
sum(case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason not in('100000000','100000001','100000002','100000003') 
then 1 else 0 end) OtherWater,

sum(case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000000' then 1 else 0 end) NotSeeMeterWater,
sum(case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000001' then 1 else 0 end) HaveWater,
sum(case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000002' then 1 else 0 end) MeterWaterBuried,
sum(case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000003' then 1 else 0 end) LockDoor,
sum(case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000004' then 1 else 0 end) MeterWaterFuzzy,

sum(case when w.hx_freading is null then 1 else 0 end) hx_freading
	
from hx_t_waterusedamount w--水量
INNER JOIN hx_t_receivable r --应收
	ON w.hx_t_waterusedamountid=r.hx_fusedamountid and [email protected] and [email protected]
--团队
INNER JOIN Team t
	ON w.OwningTeam = t.TeamId
--部门
INNER JOIN BusinessUnit u
	ON t.BusinessUnitId = u.BusinessUnitId
	
GROUP BY	u.ParentBusinessUnitIdName,
			t.Name WITH ROLLUP
---------------------------------------------------------------------------

select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '用时/毫秒'
SQL执行效果如下:

 

\

Tags:

相关文章

    文章评论

    最 近 更 新
    热 点 排 行
    Js与CSS工具
    代码转换工具
    
    <