最近一些SQL开发中碰到的问题

bisal的个人杂货铺 2024-08-01

点击标题下「蓝色微信名」可快速关注

最近做了一些数据库应用的开发工作，其实就是写SQL进行数据的整合，碰到一些小问题，有些问题还是比较典型的，或者说日常都可能碰到的，记录一下，如有需要，可以参考。

问题1 数据整合的需求

MySQL数据库，test表有如下记录，需要根据a列相同值将b列字段用","进行整合，例如此处应该得到的是('a', 'a,b,c')和('b', 'a,b')，

可以用group_concat()函数，实现此需求，

select a, group_concat(b separator ',') as data from test group by a;

返回如下，

group_concat()的使用可以参考官方文档，

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

问题2 MySQL的group_concat报错"was cut by GROUP_CONCAT()"

如上个问题，用group_concat()函数时，可能会抛出异常，"SQL 错误 [1260] [HY000]: Row XXXX was cut by GROUP_CONCAT()"。

它的原因是group_concat()有个最大长度的限制，超过最大程度，就会提示这个，受参数group_concat_max_len的控制，可以查看global和session级别该参数当前的值，默认是1024，

select @@global.group_concat_max_len;
select @@session.group_concat_max_len;

因此，需要适当调整这个参数，才可以正常执行group_concat()，

SET session group_concat_max_len=102400;

问题3 MySQL的case when

很多数据库都提供了case when这种条件判断的语法，MySQL中可以用case when对单个字段或多个字段进行条件判断并返回相应的值，如下是单个字段进行判断的例子，如果name有值，则用then后的替换，如果name不是A、B、C，但是不为空，则用'it is others'替换，如果name是空，则显示空，

select
  case when name = 'A' then 'it is A'
       when name = 'B' then 'it is B'
       when name = 'C' then 'it is C'
       when name is not null then 'it is others'
       else '' end as name
from test;

如下是多个字段进行判断的例子，会根据subject和score联合进行判断，

SELECT 
  name, 
  subject, 
  score,
  CASE 
    WHEN subject = 'Math' THEN
      CASE 
        WHEN score >= 90 THEN 'Excellent'
        WHEN score >= 80 THEN 'Very Good'
        WHEN score >= 70 THEN 'Good'
        ELSE 'Fair'
      END
    WHEN subject = 'English' THEN
      CASE 
        WHEN score >= 90 THEN 'A*'
        WHEN score >= 80 THEN 'A'
        WHEN score >= 70 THEN 'B'
        ELSE 'C'
      END
    ELSE 'N/A'
  END AS grade_level
FROM 
  students;

问题4 MySQL数据库中进行字符串截取

像Java中可能经常用到substring这种函数进行字符串截取的操作，数据库中同样有这种函数，MySQL中对应的就是substring_index()。

如下表中数据需要根据两个"/"之间的内容进行判断返回不同的值，

substring_index()函数就派上用场，如下例子中substring_index()表示截取字符串a中第2个"/"之前的内容，

select
  case when substring_index(a, '/', 2) = '/Portugal' then 'POR'
       when substring_index(a, '/', 2) = '/England' then 'GBR'
       when substring_index(a, '/', 2) = '/France' then 'FRA'
       else 'others' end as name
from test;

得到如下值，

substring_index()函数可参考官方文档，

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index

再多往前一步，上述例子中substring_index(a, '/', 2)返回的是带"/"的字符串，例如"/Portugal"，如果想得到"Portugal"，可以用replace函数进行替换，如下所示，

select replace(substring_index('/Portugal/Cristiano Ronaldo', '/', 2), '/', '') as name

就会返回这个，

其实很好理解，第二个参数是"/"，第三个参数是''空，表示将字符串中所有的"/"用''空替代，具体还可参考官方文档，

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_replace

问题5 SQL Server中截取URL

SQL Server中表数据如下，

现需要将"协议+域名或IP地址+端口"和其它内容拆开，例如上述表中的第一条记录拆成"http://localhost:13000"和"/test/a"两个字段。

这个链接中有且仅有1个"//"，可能存在多个"/"，从逻辑上来说，需要以第一个"/"为标记，截取之前和之后的字符串。

此时可以用charindex进行"/"的定位，

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。
expressionToSearch ：用于被查找的字符串。
start_location：开始查找的位置，为空时默认从第一位开始查找。

"charindex('//', a)"可以找到"//"的位置。

"charindex('//', a) + 2"表示跨过"//"两个字符的起始。

"charindex('/', a, charindex('//', a) + 2)"表示从"//"后开始找到第一个"/"的位置，即"http://localost:13000/"。

可以用left函数，得到某个位置左侧的字符串，例如"left(a, charindex('/', a, charindex('//', a) + 2) - 1)"，得到"http://localost:13000"。

可以用right函数，得到某个位置右侧的字符串，但需要计算截取的字符串长度，用a字段总长度和需要截取位置进行相减操作，例如"right(a, len(a) - charindex('/', a, charindex('//', a) + 2) + 1)"，得到"/test/a"。

完整SQL，如下所示，

select 
  a,
  left(a, charindex('/', a, charindex('//', a) + 2) - 1) as address,
  right(a, len(a) - charindex('/', a, charindex('//', a) + 2) + 1) as url
from test;