如何获得类似于Oracle滞后函数的结果集 [英] How to get result set like Oracle lag function

问题描述

我有一个mysql表，该表具有以下结构

I have a mysql table which has below structure

| ColA    | ColB    | ColC |
+------------------ +------+
| A       | xxx     |    40 |
| A       | YYY     |    40 |
| A       | zzz     |    40 |
| B       | ABC     |    50 |
| B       | BCA     |    50 |
| C       | TTT     |    60 |
| C       | FFF     |    60 |

我需要如下输出

| ColA    | ColB    | ColC |
+------------------ +------+
| A       | xxx     |    40 |
|         | YYY     |       |
|         | zzz     |       |
| B       | ABC     |    50 |
|         | BCA     |       |
| C       | TTT     |    60 |
|         | FFF     |       |

通过谷歌搜索，我得到了Group_Concat函数.

From googling I get Group_Concat function.

但是它将在同一列中显示xxx，yyy，zzz.

But It will give like xxx,yyy,zzz in same column.

我如何实现以上格式?

请帮帮我.

推荐答案

好吧，请考虑以下...

Well, consider the following...

DROP TABLE IF EXISTS abc;

CREATE TABLE abc
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,ColA CHAR(1) NOT NULL
,ColB CHAR(3) NOT NULL   
,ColC INT NOT NULL
);

INSERT INTO abc (ColA,ColB,ColC) VALUES
('A','xxx',40),
('A','YYY',40),
('A','zzz',40),
('B','ABC',50),
('B','BCA',50),
('C','TTT',60),
('C','FFF',60);

SELECT * FROM abc;
+----+------+------+------+
| id | ColA | ColB | ColC |
+----+------+------+------+
|  1 | A    | xxx  |   40 |
|  2 | A    | YYY  |   40 |
|  3 | A    | zzz  |   40 |
|  4 | B    | ABC  |   50 |
|  5 | B    | BCA  |   50 |
|  6 | C    | TTT  |   60 |
|  7 | C    | FFF  |   60 |
+----+------+------+------+

SELECT id,a,b,c
  FROM (
  SELECT id
     , CASE WHEN ColA = @prev_a 
            THEN @curr_a:= '' 
            ELSE @curr_a := ColA END a
     , @prev_a := ColA

     , CASE WHEN ColB = @prev_b 
        THEN @curr_b:= '' 
            ELSE @curr_b := ColB END b
     , @prev_b := ColB

     , CASE WHEN ColC = @prev_c 
            THEN @curr_c:= '' 
            ELSE @curr_c := ColC END c
     , @prev_c := ColC
  FROM abc
     , (SELECT @curr_a = ''
             , @prev_a = ''
             , @curr_b = ''
     , @prev_b = ''
             , @curr_c = ''
     , @prev_c = ''
             ) vars 
 ORDER 
    BY id
    ) x ORDER BY id;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | A    | xxx  | 40   |
|  2 |      | YYY  |      |
|  3 |      | zzz  |      |
|  4 | B    | ABC  | 50   |
|  5 |      | BCA  |      |
|  6 | C    | TTT  | 60   |
|  7 |      | FFF  |      |
+----+------+------+------+

我应该提到，这是假定排序规则匹配的(例如SET NAMES utf8;)

I should mention that this assumes collations match (e.g. SET NAMES utf8;)

这篇关于如何获得类似于Oracle滞后函数的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！