MS SQL Server STUFF 函数实战 统计记录行转为列显示

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

范例运行环境

操作系统: Windows Server 2019 DataCenter


数据库:Microsoft SQL Server 2016


视图样本设计

假设某一视图 [v_pj_rep1_lname_score] 可查询对某一被评价人的绩效指标的打分情况,并按评价人的职务进行分类, 设计如下:

序号 字段名 类型 说明 备注

1

projectcid

uniqueidentifier

项目ID


2

wxmpcid uniqueidentifier 被评价人ID


3

count_sortid tinyint

评价人职级排序号

数值越小职务越高

4

lname

nvarchar 评价人职务


5

rs

int 评价人总数


6

score decimal

评价人总分

所有评价人给被评价人打分的总和

7

score2 decimal 评价人平均得分 所有评价人给被评价人打分的总和除以总人数的平均分

查询分析器结果数据显示如下图

/****** SSMS 的 SelectTopNRows 命令的脚本  ******/
SELECT  [projectcid]
      ,[wxmpcid]
      ,[count_sortid]
      ,[lname]
      ,[rs]
      ,[score]
      ,[score2]
  FROM [v_pj_rep1_lname_score] order by projectcid,wxmpcid,count_sortid

如图我们对项目ID、被评价人ID、评价人职务排序号进行排序,可以看到职务越高排位越靠前。


数据统计要求

假设统计视图名 [v_pj_rep1_lname_score_count] 可查询对某一被评价人的所有被评价人统计描述(如人员人数情况、每类人打分情况等),即将视图设计样本的行数据变为列进行显示, 统计表设计如下:

序号 字段名

类型

说明

备注

1

projectcid uniqueidentifier

项目ID


2

wxmpcid uniqueidentifier 被评价人ID


3

score

decimal 被评价人权重分1

总人数的平均分*20%

4

score2 decimal 被评价人权重分2 总人数的平均分*20%*30%

5

dname

nvarchar

统计显示

将行数据变为列数据,显示统计详情信息

查询分析器结果数据显示如下图:

如图第一行数据 dname 列返回 “董事长88.21分,总经理100.00分,分协管领导92.23分,其他领导91.79分,部门职工(2人,总分187.65分)93.83分” ,该列会显示各职务打分的人数,总分及平均分情况,从统计结果来看,更加直观。

STUFF函数实现

示例代码如下:

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE view [dbo].[v_pj_rep1_lname_score_count] as 
 
select projectcid,wxmpcid,round(sum(score2)*0.2,2) score,round(sum(score2)*0.2*0.3,2) score2,
STUFF(( select ','+b.lname+case when rs>1 then '('+cast(rs as varchar(20))+'人,总分'++REPLACE(cast(round(score,2) as nvarchar(20)),'0000','')+'分'+')' else '' end+REPLACE(cast(round(score2,2) as nvarchar(20)),'0000','')+'分'   
from v_pj_rep1_lname_score b 
where b.projectCid=a.projectcid and b.wxmpCid=a.wxmpCid order by count_sortid for xml path('')
),1,1,'') as dname from 
 
  v_pj_rep1_lname_score a group by projectcid,wxmpcid
GO

关键说明见下表:

序号 关键语句 说明

1

round(sum(score2)*0.2,2) score, round(sum(score2)*0.2*0.3,2) score2, 取权重值,并使用ROUND函数取后两位小数

2

STUFF((

select ','+b.lname+case when rs>1 then '('+cast(rs as varchar(20))+'人,总分'++REPLACE(cast(round(score,2) as nvarchar(20)),'0000','')+'分'+')' else '' end+REPLACE(cast(round(score2,2) as nvarchar(20)),'0000','')+'分'  

from v_pj_rep1_lname_score b

where b.projectCid=a.projectcid and b.wxmpCid=a.wxmpCid order by count_sortid for xml path('')

),1,1,'') as dname

             

使用 STUFF 函数配合 SQL 语句 FOR XML PATH 来实现行转列。

SQL语句中通过 CASE 来判断人数,大于1则显示人数和总分,否则直接显示分值,并在前面加上职务 lname 字段,并以 count_sortid 进行排序,数值越小的职务越往前排

3


v_pj_rep1_lname_score a group by projectcid,wxmpcid

对视图样本进行项目ID和被评价人ID进行分组统计

小结

SQL Server 中的 STUFF 函数是将字符串插入到另一个字符串中。 它从第一个字符串的开始位置删除指定长度的字符;然后将第二个字符串插入到第一个字符串的开始位置。




相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
4天前
|
SQL XML 关系型数据库
【less-5】基于SQLI的SQL盲注常用函数
【less-5】基于SQLI的SQL盲注常用函数
19 2
|
4天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1天前
|
SQL Oracle 关系型数据库
SQL FIRST() 函数
SQL FIRST() 函数
8 3
|
1天前
|
SQL Oracle 关系型数据库
SQL COUNT() 函数
SQL COUNT() 函数
8 3
|
1天前
|
SQL 数据库
SQL AVG() 函数
SQL AVG() 函数
10 2
|
2天前
|
SQL 关系型数据库 MySQL
SQL 函数
SQL 函数
23 3
|
3天前
|
SQL 存储 关系型数据库
SQL Date 函数
SQL Date 函数
25 3
|
3天前
|
SQL Oracle 关系型数据库
SQL NULL 函数
SQL NULL 函数
15 1
|
1天前
|
SQL 存储 关系型数据库
ArcGIS Engine连接ArcSDE SQL Server(获得所有SDE图层)
ArcGIS Engine连接ArcSDE SQL Server(获得所有SDE图层)
|
SQL
sql 增加列
alter table student add nickname char(20)
580 0