我想知道是否有人可以帮助我弄清楚如何从 SQL 表的特定列中计算特定字符串的所有唯一出现次数？
使用这个：
index gender
1 0::Male
2 0::Male
3 0::Male||1::Male
4 0::Male||1::Female
5 0::Male||1::Male||2::Female
要得到这个：
gender count
Males 7
Females 2
这是我尝试过的，但它无法解释同一单元格中的多次出现：
SELECT
SUM (CASE WHEN genders LIKE '%Male%' THEN '1' END) AS Males,
SUM (CASE WHEN genders LIKE '%Female%' THEN '1' END) AS Females
FROM genders
请尝试以下解决方案。
它将从 SQL Server 2016 开始工作。
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY Key, gender VARCHAR(MAX));
INSERT INTO @tbl (gender) VALUES
('0::Male'),
('0::Male'),
('0::Male||1::Male'),
('0::Male||1::Female'),
('0::Male||1::Male||2::Female');
-- DDL and sample data population, end
-- Method #1
;WITH rs AS
(
SELECT STUFF(value, 1, pos + 1, '') AS gender
FROM @tbl
CROSS APPLY STRING_SPLIT(REPLACE(gender,'||','|'), '|')
CROSS APPLY (SELECT CHARINDEX('::', value)) AS t(pos)
)
SELECT gender, COUNT(*) AS [Counter]
FROM rs
GROUP BY gender;
-- Method #2
SELECT 'Male' AS Gender
, SUM(IIF(value LIKE '%:Male%', 1, 0)) AS [Counter]
FROM @tbl
CROSS APPLY STRING_SPLIT(gender,'|')
UNION ALL
SELECT 'Female' AS Gender
, SUM(IIF(value LIKE '%:Female%', 1, 0)) AS [Counter]
FROM @tbl
CROSS APPLY STRING_SPLIT(gender,'|')
ORDER BY Gender;
输出
+--------+---------+
| gender | Counter |
+--------+---------+
| Female | 2 |
| Male | 7 |
+--------+---------+
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句