BREAK ON MySQL查询中的指令? [英] BREAK ON instruction in MySQL queries?

问题描述

我想使用与Oracle配合良好的MySQL(v.5.5.41)执行该查询:

I would like to execute that query with MySQL (v. 5.5.41) that works fine with Oracle:

CLEAR BREAKS;
BREAK ON mq;

SELECT mq, im, pf, kmct 
FROM vehicules 
WHERE mq='Renault' 
ORDER BY mq;

CLEAR BREAKS;

这是Oracle构建的输出，以及我正在使用MySQL寻找的内容:

This is the output Oracle builds, and what I'm looking for with MySQL :

mq       | im          | pf   | kmct
---------+-------------+------+----------
Renault  | AA-888-AA   | 4    | 3424
---------+-------------+------+----------
         | AA-999-AA   | 2    | 2042
---------+-------------+------+----------
         | BB-888-BB   | 3    | 1580

我得到的错误是:

#1064-您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的'CLEAR BREAKS'附近使用

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLEAR BREAKS' at line 1

在MySQL中似乎没有实现"BREAK"命令.真的吗?在那种情况下，有什么方法可以模拟它吗?

It seems like there is no implementation of the command "BREAK" in MySQL. Is that true? In that case, is there any way to simulate it?

推荐答案

MySQL不支持BREAK ON. AFAIK这是ORACLE中的<报告>报告样式命令.

MySQL does not support BREAK ON. AFAIK it is a reporting style command in ORACLE.

要获得所需的输出，您必须记住先前读取的行的列值，并与在下一行的列中读取的值进行比较.当它们匹配时，可以将一个空字符串设置为输出，否则保留新值.

To achieve your required output, you have to remember the previously read row's column value and compare with value read in next row's column. When they match, you can set an empty string as output, else retain the new value.

示例:

SELECT 
       ( CASE WHEN @prev_mq != mq THEN @prev_mq := mq ELSE '' END ) AS mq
     , im, pf, kmct 
  FROM vehicules, ( SELECT @prev_mq := '' ) AS initializer
 WHERE mq = 'Renault' 
-- ORDER BY mq;

使用ORDER BY mq在这里没有意义.因为，当您用空字符串替换重复出现的内容时，ORDER BY会导致意外的结果.

Use of ORDER BY mq has no meaning here. Because, as you are replacing repeating occurrences with an empty string, ORDER BY would cause unexpected results.

这篇关于BREAK ON MySQL查询中的指令?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！