数据库|mysql教程
统计
数据库-mysql教程
客户提出需求，针对某一列分组加上小计，合计汇总。网上找了一些有关SQL加合计的语句。都不是很理想。决定自己动手写。
思路有三个：1.很多用GROUPPING和ROLLUP来实现。
优点：实现代码简洁，要求对GROUPPING和ROLLUP很深的理解。
缺点：低版本的Sql Server不支持。
什么值得买 网站源码,vscode结束符号,ubuntu 双击删除,tomcat start不,sqlite数据库适用,爬虫的技术水平是什么,php 5.5.9,梅州seo站内优化,织梦通用企业网站模板,网页全屏广告代码可关闭重播支持倒计时效果,css折叠窗体模板lzw
2.游标实现。
优点：思路逻辑简洁。
缺点：复杂和低效。
asp自动发卡源码,ubuntu挂载虚拟光盘,tomcat的监听器,xpath爬虫乱码,php建站公司,滨海seo优化一站式服务lzw
3.利用临时表。
优点：思路逻辑简洁，执行效率高。SQL实现简单。
缺点：数据量大时耗用内存.
综合三种情况，决定“利用临时表”实现。 实现效果
加上小计，合计后效果
SQL语句
select * into #TB from TB
select * into #TB1 from #TB where 11
select distinct zcxt into #TBype from #TB order by zcxt
select identity(int,1,1) fid,zcxt into #TBype1 from #TBype
DECLARE @i int
DECLARE @k int
多重筛选表格数据源码,vscode进入java方法,ubuntu装putty,tomcat页面看日志,在sqlite3中插入字段,爬虫爸爸和爬虫妈妈故事,php 面向对象教程,利于seo的模板类型,网站如何自适应手机版,dz 模板小偷lzw
select @i=COUNT(*) from #TBype
set @k=0
DECLARE @strfname varchar(50)
WHILE @k < @i
BEGIN
Set @k =@k +1
select @strfname=zcxt from #TBype1 where fid =@k
set IDENTITY_INSERT #TB1 ON
insert into #TB1(fid,qldid,fa_cardid,ztbz,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,zcxt,fa_ljjzzb)
select fid,qldid,fa_cardid,ztbz,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,zcxt,fa_ljjzzb from
(
select * from #TB where zcxt=@strfname
union all
select 0 fid,” qldid,” fa_cardid,” ztbz,’小计’ fa_name,” model,sum(i_number) as i_number,” gzrq,sum(CAST(zcyz as money)) as zcyz,sum(CAST(ljzj as money)) as ljzj,sum(CAST(jz as money)) as jz,” sybm,” zcxt,Sum(fa_ljjzzb) as fa_ljjzzb
from #TB where zcxt=@strfname
group by ztbz
) as B
set IDENTITY_INSERT #TB1 off
END
select qldid,fa_cardid,zcxt,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,ztbz,fa_ljjzzb from #TB1
union all
select ” qldid,” fa_cardid,” ztbz,’合计’ fa_name,” model,sum(i_number) as i_number,” gzrq,sum(CAST(zcyz as money)) as zcyz,sum(CAST(ljzj as money)) as ljzj,sum(CAST(jz as money)) as jz,” sybm,” zcxt,Sum(fa_ljjzzb) as fa_ljjzzb
from #TB
drop table #TB1
drop table #TBype1
drop table #TBype
drop table #TB
扩展改进可以改写成一个通用的添加合计小计的存储过程。
