SQL:利用mysql.help_topic将一行转换为多行

该文章展示了如何使用SQL的substring_index函数结合表连接,从a.sourceId字段中提取特定分隔符后的子串。它通过与mysql库中的help_topic表联接,利用help_topic_id作为分割依据,为名为yeshen的用户提取源ID。虽然help_topic表在此处主要作为数字序列使用,但提到了其常规用途是存储MySQL的帮助信息。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

数据准备

创建表

准备数据

结果

SQL:

SELECT
    a.username,
    substring_index( substring_index( a.sourceId, ',', b.help_topic_id + 1 ), ',',- 1 ) AS sourceId 
FROM
    test a
    JOIN mysql.help_topic b 
    ON b.help_topic_id < ( length( a.sourceId ) - length( REPLACE ( a.sourceId, ',', '' ))+ 1 ) 
WHERE
    a.username = 'yeshen' 
ORDER BY
    a.username

解析

1.Substring_index

取得目标字符串左侧第n个分割符左侧的部分,n为负时返回右侧第n个的右部分

2.help_topic

help_topic是数据库mysql的一个表,该表提供查询帮助主题给定关键字的详细内容(详细帮助信息)

表字段含义:

  • help_topic_id:帮助主题详细信息在表记录中对应的ID

  • name:帮助主题给定的关键字名称。

  • help_category_id:帮助主题类别ID,与help_category表中的help_category_id字段值相等。

  • description:帮助主题的详细信息(这里就是我们通常查询帮助信息真正想看的内容,例如:告诉我们某某语句如何使用的语法与注意事项等)。

  • example:帮助主题的示例信息(告诉我们语句如何使用的示例)。

  • url:该帮助主题对应在MySQL官方在线手册中的URL链接地址。

ps:本次使用help_topic表实际上没有什么用,仅仅是借用了help_topic表的help_topic_id的值为(0,1,2,...),因此在本数据库中建立一个仅含ID字段的表进行关联,同样可以实现转多行效果

select AUDITED_UNIT_IDS,AUDITED_UNIT_NAMES as AUDITED_UNIT from ( SELECT substring_index(substring_index(f.involved_department_accounts,',',b.help_topic_id + 1),',' ,- 1) AS AUDITED_UNIT_IDS,substring_index(substring_index(f.involved_department_names,',',b.help_topic_id + 1),',' ,- 1) AS AUDITED_UNIT_NAMES, f.id,f.PLAN_YEAR FROM (SELECT f.involved_department_accounts,f.involved_department_names, f.id,p.PLAN_YEAR FROM iam_fact_confirmation_problem f,iam_audit_project p where f.PROJECT_ID = p.ID and p.ITEM_STATUS<>'06' AND p.PLAN_YEAR >= :startYear AND p.PLAN_YEAR <= :endYear AND p.UNIT_ID IN ( :unitIds ) ) f JOIN iam_help_topic b ON b.help_topic_id < (length(f.involved_department_names) - length(REPLACE (f.involved_department_names, ',', '')) + 1) ) t group by AUDITED_UNIT_IDS,AUDITED_UNIT_NAMES 我要在这个基础上 加上select AUDITED_UNIT_IDS,AUDITED_UNIT_NAMES as AUDITED_UNIT from ( SELECT substring_index(substring_index(f.involved_department_accounts,',',b.help_topic_id + 1),',' ,- 1) AS AUDITED_UNIT_IDS,substring_index(substring_index(f.involved_department_names,',',b.help_topic_id + 1),',' ,- 1) AS AUDITED_UNIT_NAMES, f.id,f.PLAN_YEAR FROM (SELECT f.involved_department_accounts,f.involved_department_names, f.id,p.PLAN_YEAR FROM iam_external_problem f,iam_external_project p where f.PROJECT_ID = p.ID and p.ITEM_STATUS<>'06' AND p.PLAN_YEAR >= :startYear AND p.PLAN_YEAR <= :endYear AND p.UNIT_ID IN ( :unitIds ) ) f JOIN iam_help_topic b ON b.help_topic_id < (length(f.involved_department_names) - length(REPLACE (f.involved_department_names, ',', '')) + 1) ) t group by AUDITED_UNIT_IDS,AUDITED_UNIT_NAMES 这个
最新发布
06-28
select count(AUDITED_UNIT) from( SELECT AUDITED_UNIT_IDS, AUDITED_UNIT_NAMES AS AUDITED_UNIT FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(f.involved_department_accounts, ',', b.help_topic_id + 1), ',', -1) AS AUDITED_UNIT_IDS, SUBSTRING_INDEX(SUBSTRING_INDEX(f.involved_department_names, ',', b.help_topic_id + 1), ',', -1) AS AUDITED_UNIT_NAMES, f.PROBLEM_ID, f.PLAN_YEAR, f.UNIT_ID, f.PROJECT_SOURCE FROM ( SELECT '1' AS PROJECT_SOURCE, f.involved_department_accounts, f.involved_department_names, f.ID AS PROBLEM_ID, P.PLAN_YEAR :: INTEGER AS PLAN_YEAR, P.unit_id AS UNIT_ID FROM iam_fact_confirmation_problem f JOIN iam_audit_project P ON f.PROJECT_ID = P.ID JOIN iam_help_topic b ON b.help_topic_id < (LENGTH(f.involved_department_names) - LENGTH(REPLACE(f.involved_department_names, ',', '')) + 1) UNION ALL SELECT '2' AS PROJECT_SOURCE, f.involved_department_accounts, f.involved_department_names, f.ID AS PROBLEM_ID, EXTRACT(YEAR FROM to_timestamp(P.creation_time / 1000)) AS PLAN_YEAR, P.unit_id AS UNIT_ID FROM iam_external_problem f JOIN iam_external_project P ON f.PROJECT_ID = P.ID JOIN iam_help_topic b ON b.help_topic_id < (LENGTH(f.involved_department_names) - LENGTH(REPLACE(f.involved_department_names, ',', '')) + 1) ) f WHERE f.PLAN_YEAR >= :startYear AND f.PLAN_YEAR <= :endYear AND f.UNIT_ID IN (:unitIds) ) t GROUP BY AUDITED_UNIT_IDS, AUDITED_UNIT_NAMES ) t999 这个在查询中 报错 ... 174 common frames omitted Caused by: org.postgresql.util.PSQLException: [10.0.116.108:64607/b.hd.com/172.20.97.226:5432] ERROR: syntax error at or near ":" 位置:749 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2933) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2655) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:376) at org.postgresql.jdbc.PgStatement.runQueryExecutor(PgStatement.java:561) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:538) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:396) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:171) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:127) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ... 188 common frames omitted
06-28
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值