javascript - 将连接的结果集拆分为行

Question

有一个存储过程来处理来自某些表的记录。为了存储连接操作生成的一些结果，有一个临时表。

表A

+----+------+--------+
| id | name | number |
+----+------+--------+
|  1 | John |    123 |
|  2 | Tim  |    567 |
|  3 | Bill |    789 |
|  4 | Jim  |    345 |
+----+------+--------+

表B

+----+------+--------+
| id | code | number |
+----+------+--------+
|  1 | LK   |    123 |
|  2 | CN   |    123 |
|  3 | BN   |    789 |
|  4 | IN   |    345 |
+----+------+--------+

表温度

+----+------+-----+------+--------+
| id | name | age | code | number |
+----+------+-----+------+--------+
|  1 | John |  54 | LK   |    123 |
|  1 | John |  54 | CK   |    123 |
|  3 | Bill |  26 | BN   |    789 |
|  4 | Jim  |  78 | IN   |    345 |
+----+------+-----+------+--------+

将表 Temp 结果集转换为 JSON。

[{"id":1,"name":"John","code":"LK","number":123}, {"id":2,"name":"John","code":"CK","number":123}, {"id":3,"name":"Bill","code":"BN","number":789}, {"id":4,"name":"Jim","code":"IN","number":345}]

我需要拆分记录以显示在 View 中，如下所示。

+------------+-----+------+--------+
|    name    | age | code | number |
+------------+-----+------+--------+
| John       | 54  |      |        |
|            |     | LK   |    123 |
|            |     | CK   |    123 |
| Bill       |  26 |      |        |
|            |     | BN   |    789 |
| Jim        |  78 |      |        |
|            |     | IN   |    345 |
+------------+-----+------+--------+


[{"name":"John","age":54}, {"code":"LK","number":123}, {"code":"CK","number":123}, {"name":"Bill","age":26}, {"code":"BN","number":789}, {"name":"Jim","age":78}, {"code":"IN","number":345}]

有什么有效的方法，拆分JSON还是可以通过查询MySQL中的表Temp来生成这样的结果集？

Answer 1

-- query wanted
select 
    if(name = @last_name, '', @last_name := name) as name,
    if(age = @last_age, '', @last_age := age) as age,
    code,
    number
from
(   
    (select name, age, code, number from t)
    union 
    (select distinct name, age, '', '' from t)
    order by 1,2,3,4
) as t2 cross join (select @last_name := null, @last_age := null ) param; 

演示:

SQL:

-- data
create table t(id int, name char(20), age int, code char(20), number int);
insert into t values
(  1 , 'John' ,  54 , 'LK'   ,    123 ),
(  1 , 'John' ,  54 , 'CK'   ,    123 ),
(  3 , 'Bill' ,  26 , 'BN'   ,    789 ),
(  4 , 'Jim'  ,  78 , 'IN'   ,    345 );
select * from t;

-- query wanted
select 
    if(name = @last_name, '', @last_name := name) as name,
    if(age = @last_age, '', @last_age := age) as age,
    code,
    number
from
(   
    (select name, age, code, number from t)
    union 
    (select distinct name, age, '', '' from t)
    order by 1,2,3,4
) as t2 cross join (select @last_name := null, @last_age := null ) param
; 

输出:

mysql> select * from t;
+------+------+------+------+--------+
| id   | name | age  | code | number |
+------+------+------+------+--------+
|    1 | John |   54 | LK   |    123 |
|    1 | John |   54 | CK   |    123 |
|    3 | Bill |   26 | BN   |    789 |
|    4 | Jim  |   78 | IN   |    345 |
+------+------+------+------+--------+
4 rows in set (0.00 sec)

mysql> -- query wanted
mysql> select
    -> if(name = @last_name, '', @last_name := name) as name,
    -> if(age = @last_age, '', @last_age := age) as age,
    -> code,
    -> number
    -> from
    -> (
    -> (select name, age, code, number from t)
    -> union
    -> (select distinct name, age, '', '' from t)
    -> order by 1,2,3,4
    -> ) as t2 cross join (select @last_name := null, @last_age := null ) param
    -> ;
+------+------+------+--------+
| name | age  | code | number |
+------+------+------+--------+
| Bill | 26   |      |        |
|      |      | BN   | 789    |
| Jim  | 78   |      |        |
|      |      | IN   | 345    |
| John | 54   |      |        |
|      |      | CK   | 123    |
|      |      | LK   | 123    |
+------+------+------+--------+
7 rows in set (0.00 sec)