[存储过程]sql server 中 统计一条记录中 非空 字段个数 ,并且计算它所占百分比

原创 2011年07月07日 13:33:43

integrity


是表中存放非空字段百分比 的字段

companyName

等字段是参与统计的成员


看代码


 
drop procedure update_company_intergrity
;
create procedure update_company_intergrity @id int
as

update manager_company   set integrity =
(
SELECT 
	(
	len(rtrim(ltrim(SubString(isnull(companyName,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(productFirstType_ID,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(linkManName,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(linkManPost,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyTel,''),1,1))))
 
	+len(rtrim(ltrim(SubString(isnull(companyAdr,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(companyMobile,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyFax,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(companyHomepage,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyDelegate,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(companyDelegateCall,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(companyMobile,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyBusinessModel,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(companyArea,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyIntroduction,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(businessRegistrationNumber,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(registeredCapital,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(enterpriseType,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(registeredDate,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(registeredAuthority,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(inspectionDate,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(companyScale,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(style_ID,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(mainProducts,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(province,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(city,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(manageRange,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(turnoverOfYear,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(postalcode,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(stapleMarket,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(openBank,''),1,1))))
  +len(rtrim(ltrim(SubString(STR(isnull(isOfferProcessAndService,'')),1,1))))
	+len(rtrim(ltrim(SubString(isnull(importOfYear,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(exitOfYear,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(researchNum,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(outputOfMonth,''),1,1))))
 
  
  +len(rtrim(ltrim(SubString(isnull(mainIndustry,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(registerAdr,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(mainCustom,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(mainManagePlace,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(mangerBrand,''),1,1))))

  +len(rtrim(ltrim(SubString(isnull(bankNum,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(qualityControl,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(areaNum,''),1,1))))
	+len(rtrim(ltrim(SubString(STR(isnull(certificateNum,'')),1,1))))
	+len(rtrim(ltrim(SubString(isnull(effectTime,''),1,1))))
	 )
	 *100/45
FROM manager_companydetails 
where  rtrim(ltrim(manager_companydetails.id)) = rtrim(ltrim(@id))
)
where rtrim(ltrim(manager_company.id)) = rtrim(ltrim(STR(@id)))
 
;


sql server 使用over 统计百分比

SELECT dsr.ChildOrderId,count(1) AS total, 100 * count(1)/sum(count(1)) OVER() AS pct, sum(count(1...
  • zghnpdswyp
  • zghnpdswyp
  • 2016年02月16日 10:32
  • 812

mysql分组然后统计某个值的百分比sql实现

问题: mysql如何遍历某个字段所有值并计算比例解决问题1.构造数据 INSERT INTO `test`.`test` (`id`, `num`, `num2`, `branch_id`, `o...
  • lcj_star
  • lcj_star
  • 2017年08月01日 13:18
  • 2010

Sql存储过程---计算出投票百分比

1。建表:Vote主表和VoteOption选项表Vote表:Vote测试数据:VoteOption表: Option测试数据:现在要计算出投票名称为“第二个投票”的各项投票数的百分比存储过程如下:S...
  • zhengmingli
  • zhengmingli
  • 2010年06月22日 10:48
  • 1813

SQL 计算比例总结

---------------------------------------------------------------------用sql语句实现计算比例-------------------...
  • javaFay
  • javaFay
  • 2012年10月10日 09:54
  • 3504

sql server 计算男女比例 百分比

--分组列出性别 计算男女各人数 和所占比例 select tsex ,count(*) from person_info where ttype='在职人员' group by tsex --总人...
  • su12345su
  • su12345su
  • 2016年01月25日 17:23
  • 8120

SQL统计一个字段里某个特定字符的个数

select sum(length(couponrecordid)-length(replace(couponrecordid,',',''))) from t_order where paytype...
  • luwei42768
  • luwei42768
  • 2016年01月06日 11:14
  • 1081

SQL Server统计数据库中表个数、视图个数、存储过程个数

数据库SQL2008 表个数 SELECT count(*) FROM sys.objects WHERE type='U'视图个数 SELECT count(*) FROM sys.obj...
  • snails_zx
  • snails_zx
  • 2015年11月09日 10:59
  • 42850

简单的SQL语句:计算一列中某个值的个数

这两天在参加
  • lavorange
  • lavorange
  • 2014年05月05日 22:39
  • 7626

sql oracal 查询某一字段的某一个值的总条数

查询某一字段的某一个值的总条数 比如:orderprogressno字段的值分别为1,2,3,4,那么就是查询值为1的总条数和值为2的总条数 select orderno,count(orde...
  • spider_max
  • spider_max
  • 2016年09月12日 17:05
  • 1405

Sql Server 给表添加合计并统计金额的存储过程!(源代码分享)

USE [life] GO /****** 对象: StoredProcedure [dbo].[类_表添加合计] 脚本日期: 06/09/2012 09:33:16 ******/ SET ...
  • dxnn520
  • dxnn520
  • 2012年06月09日 09:43
  • 2023
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:[存储过程]sql server 中 统计一条记录中 非空 字段个数 ,并且计算它所占百分比
举报原因:
原因补充:

(最多只允许输入30个字)