如何用SQL统计Garden表Fruit列每行的水果数量？

嘿，这个需求其实挺常见的，不用正则也能轻松搞定～不同数据库的实现方式略有差异，我给你分主流情况列出来：

核心思路其实很简单：计算字符串里逗号的个数，再加1就是水果的数量（因为n个分隔符对应n+1个元素）。接下来针对不同数据库给出具体实现：

MySQL/MariaDB #

用LENGTH()和REPLACE()组合就能搞定：

SELECT 
  Fruit,
  LENGTH(Fruit) - LENGTH(REPLACE(Fruit, ',', '')) + 1 AS FruitCount
FROM Garden;

• 原理：REPLACE(Fruit, ',', '')会把所有逗号移除，用原字符串长度减去移除逗号后的长度，得到逗号的总数，加1就是水果个数。
• 小补充：如果某行Fruit是空值或空字符串，上面的公式会返回1，如果你需要这种情况返回0，可以加个条件判断：

SELECT 
  Fruit,
  CASE 
    WHEN Fruit IS NULL OR Fruit = '' THEN 0
    ELSE LENGTH(Fruit) - LENGTH(REPLACE(Fruit, ',', '')) + 1 
  END AS FruitCount
FROM Garden;

PostgreSQL #

PostgreSQL有更直观的方法，用string_to_array()把字符串转成数组，再取数组长度：

SELECT 
  Fruit,
  array_length(string_to_array(Fruit, ','), 1) AS FruitCount
FROM Garden;

• 空值处理：如果Fruit为空，上面的语句会返回NULL，可以用COALESCE()把它转成0：

SELECT 
  Fruit,
  COALESCE(array_length(string_to_array(Fruit, ','), 1), 0) AS FruitCount
FROM Garden;

SQL Server #

和MySQL逻辑类似，用LEN()和REPLACE()组合：

SELECT 
  Fruit,
  LEN(Fruit) - LEN(REPLACE(Fruit, ',', '')) + 1 AS FruitCount
FROM Garden;

• 同样处理空值的版本：

SELECT 
  Fruit,
  CASE 
    WHEN Fruit IS NULL OR Fruit = '' THEN 0
    ELSE LEN(Fruit) - LEN(REPLACE(Fruit, ',', '')) + 1 
  END AS FruitCount
FROM Garden;

额外注意事项 #

如果你的数据存在这些不规范的情况，需要额外处理：

• 连续逗号（比如Apple,,Banana）：上面的方法会把空值也算成一个水果，你可以先替换掉连续逗号再计算（以MySQL为例）：

  SELECT 
    Fruit,
    LENGTH(REPLACE(Fruit, ',,', ',')) - LENGTH(REPLACE(REPLACE(Fruit, ',,', ','), ',', '')) + 1 AS FruitCount
  FROM Garden;
  

• 首尾逗号（比如,Apple,Banana或Apple,Banana,）：这种情况会多算一个，先去掉首尾的逗号再计算（以MySQL为例）：

  SELECT 
    Fruit,
    LENGTH(TRIM(BOTH ',' FROM Fruit)) - LENGTH(REPLACE(TRIM(BOTH ',' FROM Fruit), ',', '')) + 1 AS FruitCount
  FROM Garden;
  

当然，最好还是从数据源层面保证格式规范，避免这些额外的处理～

内容的提问来源于stack exchange，提问作者Abhinay Kumar