sql - 查询将多个相同记录分组为 1 行

Question

在查找我的表的查询时遇到问题

我有 2 个表表 A 和表 B，如下所示

table A 
---------------------
|   Name   | addrid |
---------------------
|   zlai   |   1    |
|   blai   |   2    |

table B 
---------------------
| addrid |   addr     |
---------------------
|   1    |   AMERICA  |
|   1    |   SPAIN    |
|   1    |   MEXICO   |
|   2    |   TURKEY   |

我需要的结果是

--------------------------
|   Num  | Name  | addr    |
--------------------------
|    1   | zlai  | AMERICA |               
|        |       | SPAIN   |
|        |       | MEXICO  |
|    2   | blai  | TURKEY  |

到目前为止我已经尝试过的查询

http://sqlfiddle.com/#!12/3ac39/12

Answer 1

SELECT  CASE WHEN No != 1
             THEN ''
             ELSE CAST(a.addrid AS VARCHAR(20))
        END  AS Num,
        CASE WHEN No != 1
             THEN ''
             ELSE name
        END  AS name,
        addr
FROM
        (
          SELECT  ROW_NUMBER() OVER (PARTITION BY a.addrid 
                                     ORDER BY name DESC) AS No,
                  name, 
                  addr,
                  a.addrid
          FROM    a LEFT JOIN b ON a.addrid = b.addrid
        ) a
ORDER   BY a.addrid

• SQLFiddle Demo