首页 > 数据库 >sql根据团队树一级一级汇总统计

sql根据团队树一级一级汇总统计

时间:2022-12-31 12:44:50浏览次数:77  
标签:一级 Business 汇总 TeamID PeopleId .. sql 团队 TB

 1、需求描述

        最近碰到了一个需求,是要统计各个团队的员工的销售金额,然后一级一级向上汇总。

​编辑

 架构团队树是类似于这种样子的,需要先算出每个员工的销售金额,然后汇总成上一级的团队金额,然后各个团队的销售总金额再往上汇总成一个区域的销售金额,然后各个区域的金额再往上汇总成总公司的金额。当然我工作碰到的团队树要远比这个复杂许多,但反正差不多是这么个意思。

 

 

2、解决方法

2.1、方法一(不推荐)

        持久层通过一些sql把团队树结构,以及各个员工的销售金额汇总拿到,然后在业务层通过代码去一层层拼起来。这是我一开始拿到这个需求时的思路,后来发现可以但是很复杂,代码可读性及可维护性很差。

 

2.2、方法二(推荐)

        在sql里面计算汇总出来。

        我这里是在测试环境建了几张Demo表来加以说明sql的逻辑。

1、建表、

CREATE TABLE Business..TGroupV2(TreeNodeNo int,TeamId int,TeamName varchar(100),[Path] varchar(100));
CREATE TABLE Business..TPeopleSalesInfoV2(TeamId int,PeopleId varchar(100),PeopleName varchar(100));
CREATE TABLE Business..TPeopleSalesDetailInfoV2(PeopleId varchar(100),Amount Decimal(18,2),ContractID varchar(100));

2、添加一些测试数据

​编辑

 

3、SQL代码

--以团队为单位,汇总各个团队,子团队,父团队的销售金额
SELECT TB.TreeNodeNo,TB.TeamID,TB.TeamName,AA.Amount,'' as PeopleId ,'' as PeopleName FROM 
(
	SELECT A.ParentTeamID,SUM(A.Amount) as Amount FROM
		(
		SELECT  
			TT.*,TG2.TeamID as ParentTeamID,BB.Amount from
				(
					select T1.*,TG.[Path]
					from Business..TPeopleSalesInfoV2 T1 
					left join Business..TGroupV2 TG on T1.TeamId=TG.TeamId
				) AS TT
				left join Business..TGroupV2 TG1 on TT.TeamId=TG1.TeamId
				left join Business..TGroupV2 TG2 on 
				TG1.[Path] LIKE ('%\' + convert(varchar(50),TG2.TeamID)) 
				 	or TG1.[Path] like ('%\' + convert(varchar(100),TG2.TeamID) + '\%') 
				    or TG1.[Path] like (convert(varchar(50),TG2.TeamID) + '\%') 
				    or TG1.[Path] = convert(varchar(50),TG2.TeamID) 
				LEFT JOIN 
					(select PeopleId,SUM(Amount) as Amount from Business..TPeopleSalesDetailInfoV2 group by PeopleId)
				as BB on TT.PeopleId=BB.PeopleId
		) A	 GROUP by  ParentTeamID
) as AA LEFT JOIN Business..TGroupV2 TB on TB.TeamID=AA.ParentTeamID
UNION 
--以员工为单位获取各个销售人员的销售金额
select TB.TreeNodeNo,TB.TeamID,TB.TeamName,SUM(TP.Amount) as Amount,TP.PeopleId,TPS.PeopleName from Business..TPeopleSalesDetailInfoV2 TP
LEFT JOIN Business..TPeopleSalesInfoV2 TPS on  TPS.PeopleId=TP.PeopleId
LEFT JOIN Business..TGroupV2 TB on TB.TeamID=TPS.TeamID
group by TB.TreeNodeNo,TB.TeamID,TB.TeamName,TP.PeopleId,TPS.PeopleName
ORDER BY TreeNodeNo,PeopleId ASC 

​编辑

 

2.3、思路说明

​编辑

 

 

3、总结

        随着数据量增加一些老的sql查询性能太慢了,经常出现这种查询超时问题。

​编辑

 造成这种问题的原因有很多,一种是sql写的太烂了,业务层有循环查询。就像我方法一中的那种思想,不可避免你要循环查询出每个团队的金额再一级一级向上汇总。还有就是不合理的权限控制。比如你要查询团队的销售金额。因为团队的关系是一个树状结构嘛。假如你是东区的领导,你只能查询东区及其下所有子团队的数据,但在权限判断这块,其实是会东区下每个子团队,以及子团队的子团队.....都要判断一遍你有没有查询的权限。这样就增加了不必要的负担。不过这个是历史遗留问题,是因为之前的权限结构设计就不完善,也不太好改。

解决方法嘛,目前我就是通过存储过程取代select查询,因为存储过程是预编译的,所以执行起来开销比较小所以速度比较快。可以看下这篇详细了解下:为什么存储过程比sql语句效率高? - herizai - 博客园 (cnblogs.com)

因为原先的select查询关联了好多表以及视图,各种join的,可读性很差。我所要做的就是理清这些join之间的关系, 存储过程中用几个临时表把大的join拆成合并成小的join。再加一些注释什么的,虽然业务没有变,只是代码更容易理解了。速度确实快了一些,不在出现查询的超时的问题了。

 

 

4、参考资料

为什么要用存储过程,存储过程的优缺点。。_jokeylin的博客-CSDN博客

为什么存储过程比sql语句效率高? - herizai - 博客园 (cnblogs.com)

标签:一级,Business,汇总,TeamID,PeopleId,..,sql,团队,TB
From: https://www.cnblogs.com/wlc-atlantide/p/17016458.html

相关文章

  • Centos安装Mysql
    Centos安装MysqlCentos通过下载安装包方式进行安装Mysql,Centos版本7.9,Mysql版本8.0.31#查看Centos版本cat/etc/redhat-release#查看mysql版本mysql--versionmysq......
  • MySQL密码策略审计,提升安全
    背景:审核密码是否符合规范,如大小写、数字、特殊字符等安装插件:1、在线启动INSTALLPLUGINvalidate_passwordSONAME'validate_password.so';2、写入配置文件plugi......
  • 认真学习MySQL中锁机制(二)
    接上文​​认真学习MySQL中锁机制(一)​​我们继续学习MySQL中的锁机制。【5】按加锁的方式划分:显示锁、隐式锁①隐式锁一个事务在执行insert操作时,如果即将插入的间隙已经被......
  • SQLAlchemy连接MySQL及记录的查询、更新、删除、多表关联查询
    SQLAlchemy是Python的ORM库,支持多种数据库。建立连接连接MySQL要用到​​Engine​​,Engine集成了连接池pool和方言Dialect(支持不通数据库的SQL语法),最后都统一成标准DBAPI。f......
  • Docker运行mysql
    1、拉取mysql5.7镜像dockerpullmysql:5.72、创建用于挂载的目录(日志目录、数据目录、配置目录) 3、在conf目录下创建my.cnf配置文件my.cnf内容如下:[mysqld]......
  • lightdb/postgresql中的表空间及相关注意点
    一般来说,现在为了各司其职,都把硬盘做成了san,raid,至少lvm。但是仍然有一些用户希望可以指定表空间和索引,当然也包括冷热数据。在lightdb里面也支持该特性。如下:lightdb@p......
  • MySQL报错解决
    1mysql报错解决mysql>grantallon*.*to"dba"@"%"identifiedby"mysql123";ERROR1819(HY000):Yourpassworddoesnotsatisfythecurrentpolicyrequirement......
  • HiveSQL——打折日期交叉问题
    需求描述如下为平台商品促销数据:字段为品牌,打折开始日期,打折结束日期createtabletemp_date_link(brand_idvarchar(10),start_datestring,end_dat......
  • MySQL slave upgrade: Slave failed to initialize relay log info structure from th
    MySQL slaveafterupgradefrom 5.6.x to 5.7.x maythrowthefollowingerror:12mysql>STARTSLAVE;ERROR1872(HY000):Slavefailedtoinitiali......
  • mysql调优
    一、insert调优1、插入多条数据时最好批量插入(但一般不超过一千条)2、手动提交事务,多条语句一起提交starttransaction;insertintotablevalues(),(),();......