使用SUBSTRING、CHARINDEX、LEN等函数,写一个sql server查询,不能使用string_split函数。在MeetItem表中进行查询,表中JoinName列,MsterName列MeetSummaryName列,其中JoinName列是text类型,MsterName列MeetSummaryName列是varchar类型。这三列中有若干人员姓名且多个姓名之间用逗号隔开,统计每个姓名的在这三列总共出现次数以及分别在每一列的出现次数并展示出来。
时间: 2024-03-29 17:35:28 浏览: 22
以下是查询语句:
```
SELECT name,
SUM(cnt) AS total_cnt,
SUM(CASE WHEN col = 'JoinName' THEN cnt ELSE 0 END) AS join_cnt,
SUM(CASE WHEN col = 'MsterName' THEN cnt ELSE 0 END) AS mster_cnt,
SUM(CASE WHEN col = 'MeetSummaryName' THEN cnt ELSE 0 END) AS meet_cnt
FROM (
SELECT SUBSTRING(',' + JoinName + ',' + MsterName + ',' + MeetSummaryName + ',', number + 1, CHARINDEX(',', ',' + JoinName + ',' + MsterName + ',' + MeetSummaryName + ',', number + 1) - number - 1) AS name,
COUNT(*) AS cnt,
col
FROM MeetItem
CROSS APPLY (
SELECT number
FROM master..spt_values
WHERE type = 'P' AND number <= LEN(',' + JoinName + ',' + MsterName + ',' + MeetSummaryName + ',') - 1
AND SUBSTRING(',' + JoinName + ',' + MsterName + ',' + MeetSummaryName + ',', number, 1) = ','
) nums
CROSS APPLY (
VALUES ('JoinName'), ('MsterName'), ('MeetSummaryName')
) cols (col)
GROUP BY SUBSTRING(',' + JoinName + ',' + MsterName + ',' + MeetSummaryName + ',', number + 1, CHARINDEX(',', ',' + JoinName + ',' + MsterName + ',' + MeetSummaryName + ',', number + 1) - number - 1),
col
) t
GROUP BY name
ORDER BY total_cnt DESC
```
该查询使用了两个 CROSS APPLY 子查询,第一个子查询生成了一个名为 nums 的结果集,其中包含了字符串中所有逗号的位置,第二个子查询生成了一个名为 cols 的结果集,其中包含了 JoinName、MsterName、MeetSummaryName 三列的列名。然后将 MeetItem 表和 nums、cols 两个结果集进行了 CROSS JOIN 操作,得到了所有可能的姓名及其出现的列,然后使用 SUBSTRING 和 CHARINDEX 函数将每个姓名分离出来,并统计了每个姓名在每个列中出现的次数。最后在外层使用 GROUP BY 进行了汇总,并按照总出现次数倒序排序。