SET 和 Select Query 结合 Run in a Single MySql Query 以在 pentaho 中传递结果 [英] SET and Select Query combine Run in a Single MySql Query to pass result in pentaho

问题描述

我想在 mysql 中作为单个查询运行以下 3 个查询，并将此结果传递给 Pentaho 查询组件以绘制一些图形.

Below 3 queries i want to run as single query in mysql and i will pass this result to Pentaho query component to plot some graph.

SET @input = select "22:4,33:4" from dual;  
SET @count := 0;     
select SUBSTRING_INDEX(@input, ' ', (@count) * 2), ' ', -1) as xyz, som_cnt as count from abc;   

样本字符串长度未知 (22:4,33:4,96:6....)

Sample string is of unknown length (22:4,33:4,96:6....)

expected output 

xyz      count
---------------- 
22        4
33        4
96        6

reference - Mysql 查询分隔空间delimited String 转换成2列

reference - Mysql Query to Separate space delimited String Convert into 2 columns

• 设置查询计数=0，如果我可以在选择查询中合并这个 SET 查询也可以.
• 选择查询使用计数对输入字符串进行一些解析 - 仅添加了示例子字符串.(此处添加解析逻辑)

我想要下面显示的 mysql 链接中的相同功能我试过这个解决方案，但我不确定 set_config 在 mysql 中是否有效.在单个查询中设置和选择?

I want same functionality in mysql link shown below i tried this solution but i am not sure is set_config works in mysql. SET and SELECT within a single query?

或任何在 Pentaho 的 Query 组件中运行存储过程的方法.

or any method to run store procedure in Query component of Pentaho.

推荐答案

您可以将用户定义的会话变量的初始化移动到派生表，并Cross Join 与您的其他表:

You can move the initialization of user-defined session variables to a Derived table, and Cross Join with your other table(s):

SELECT SUBSTRING_INDEX(@input, ' ', (@count) * 2), ' ', -1) AS xyz, 
       som_cnt AS `count` 
FROM abc
CROSS JOIN (SELECT @count := 0, 
                   @input := '22:4,33:4'
           ) AS user_init_vars 

这篇关于SET 和 Select Query 结合 Run in a Single MySql Query 以在 pentaho 中传递结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！