写一个sql server查询,不能使用string_split函数。在MeetItem20200119表中,JoinName列,MsterName列和MeetSummaryName列中有若干人员姓名且多个姓名之间用逗号隔开,统计这三列中每个名字的出现次数并把其在这三列的出现次数展示出来。
时间: 2024-03-28 18:39:38 浏览: 15
如果不能使用 `string_split` 函数,你可以使用以下查询来实现相同的功能:
```
SELECT name, COUNT(*) AS occurrences
FROM (
SELECT TRIM(value) AS name
FROM (
SELECT JoinName AS names
FROM MeetItem20200119
UNION ALL
SELECT MasterName AS names
FROM MeetItem20200119
UNION ALL
SELECT MeetSummaryName AS names
FROM MeetItem20200119
) AS allnames
CROSS APPLY (
SELECT CHARINDEX(',', ','+names+',', pos)+1 AS pos
FROM (
SELECT SUBSTRING(','+names+',', 1, LEN(','+names+',')) AS names,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
FROM (
SELECT names
FROM (
SELECT names,
CAST('<x>'+REPLACE(names,',','</x><x>')+'</x>' AS XML) AS namesxml
FROM (
SELECT JoinName AS names
FROM MeetItem20200119
UNION ALL
SELECT MasterName AS names
FROM MeetItem20200119
UNION ALL
SELECT MeetSummaryName AS names
FROM MeetItem20200119
) AS allnames
) AS x
CROSS APPLY (
SELECT n.v.value('.','nvarchar(max)') AS value
FROM x.namesxml.nodes('x') AS n(v)
) AS namesplit
) AS names
) AS t1
CROSS APPLY (
SELECT SUBSTRING(names, pos, CHARINDEX(',', names+',', pos)-pos) AS value
FROM (
SELECT ','+names+',',
CAST(LEN(','+names+',')-LEN(REPLACE(','+names+',', ',', '')) AS INT) AS cnt
FROM t1
) AS t2
WHERE CHARINDEX(',', names+',', pos) > pos
AND cnt >= rn
UNION ALL
SELECT SUBSTRING(names, pos, LEN(names)-pos+1) AS value
FROM t1
WHERE cnt < rn
) AS t3
) AS namesplit
) AS names
GROUP BY name
ORDER BY occurrences DESC;
```
这个查询使用了一些较为复杂的方法来将三列中的多个姓名分割成单个姓名。它首先将三列中的所有姓名合并成一个结果集,然后将每个多个姓名之间用逗号分隔的字符串转换为 XML,并将每个姓名分割成单个姓名。它使用了多个 `CROSS APPLY` 和子查询来实现这个过程,因为不能使用 `string_split` 函数。
最后,使用 `GROUP BY` 和 `COUNT` 函数来统计每个姓名出现的次数,并按照出现次数从高到低进行排序。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)