使用SUBSTRING、CHARINDEX、LEN等函数,写一个sql server查询,不能使用string_split函数。在MeetItem表中进行查询,表中有text类型的JoinName列,varchar类型的MsterName列和varchar类型的MeetSummaryName列,这三列中有若干人员姓名且多个姓名之间用逗号隔开,统计每个姓名的在这三列总共出现次数以及分别在每一列的出现次数并展示出来。
时间: 2024-03-28 19:41:18 浏览: 48
可以使用以下SQL查询语句:
```
SELECT
name,
SUM(total_count) AS total_count,
SUM(join_name_count) AS join_name_count,
SUM(mster_name_count) AS mster_name_count,
SUM(meet_summary_name_count) AS meet_summary_name_count
FROM (
SELECT
SUBSTRING(',' + JoinName + ',', Number + 1, CHARINDEX(',', ',' + JoinName + ',', Number + 1) - Number - 1) AS name,
COUNT(*) AS total_count,
COUNT(CASE WHEN Number = 0 THEN 1 END) AS join_name_count,
COUNT(CASE WHEN Number > 0 AND CHARINDEX(',' + JoinName + ',', Number) = Number THEN 1 END) AS mster_name_count,
COUNT(CASE WHEN Number > 0 AND CHARINDEX(',' + JoinName + ',', Number) <> Number THEN 1 END) AS meet_summary_name_count
FROM MeetItem
CROSS JOIN (
SELECT ones.number + tens.number * 10 + hundreds.number * 100 AS Number
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(number),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(number),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(number)
) AS Numbers
WHERE Numbers.Number <= LEN(JoinName) AND SUBSTRING(',' + JoinName + ',', Numbers.Number, 1) = ','
GROUP BY SUBSTRING(',' + JoinName + ',', Number + 1, CHARINDEX(',', ',' + JoinName + ',', Number + 1) - Number - 1)
UNION ALL
SELECT
SUBSTRING(',' + MsterName + ',', Number + 1, CHARINDEX(',', ',' + MsterName + ',', Number + 1) - Number - 1) AS name,
COUNT(*) AS total_count,
0 AS join_name_count,
COUNT(CASE WHEN Number = 0 THEN 1 END) AS mster_name_count,
COUNT(CASE WHEN Number > 0 AND CHARINDEX(',' + MsterName + ',', Number) <> Number THEN 1 END) AS meet_summary_name_count
FROM MeetItem
CROSS JOIN (
SELECT ones.number + tens.number * 10 + hundreds.number * 100 AS Number
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(number),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(number),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(number)
) AS Numbers
WHERE Numbers.Number <= LEN(MsterName) AND SUBSTRING(',' + MsterName + ',', Numbers.Number, 1) = ','
GROUP BY SUBSTRING(',' + MsterName + ',', Number + 1, CHARINDEX(',', ',' + MsterName + ',', Number + 1) - Number - 1)
UNION ALL
SELECT
SUBSTRING(',' + MeetSummaryName + ',', Number + 1, CHARINDEX(',', ',' + MeetSummaryName + ',', Number + 1) - Number - 1) AS name,
COUNT(*) AS total_count,
0 AS join_name_count,
COUNT(CASE WHEN Number = 0 THEN 1 END) AS mster_name_count,
COUNT(CASE WHEN Number > 0 AND CHARINDEX(',' + MeetSummaryName + ',', Number) <> Number THEN 1 END) AS meet_summary_name_count
FROM MeetItem
CROSS JOIN (
SELECT ones.number + tens.number * 10 + hundreds.number * 100 AS Number
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(number),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(number),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(number)
) AS Numbers
WHERE Numbers.Number <= LEN(MeetSummaryName) AND SUBSTRING(',' + MeetSummaryName + ',', Numbers.Number, 1) = ','
GROUP BY SUBSTRING(',' + MeetSummaryName + ',', Number + 1, CHARINDEX(',', ',' + MeetSummaryName + ',', Number + 1) - Number - 1)
) AS name_counts
GROUP BY name
```
这个查询语句使用了三个UNION ALL操作,分别统计了JoinName、MsterName和MeetSummaryName列中的姓名出现次数。在每个子查询中,我们使用了SUBSTRING和CHARINDEX函数来提取出每个姓名,使用了COUNT函数来计数。在UNION ALL操作后,我们再次使用SUM函数来计算每个姓名在三列中出现的总次数以及分别在每一列的出现次数。最后,我们使用GROUP BY语句按姓名进行聚合。