sql - 在一列中返回多个值

Question

我有两张 table

员工:

Empid Ename Eage Eadd Ephone
1      x     23   b    677
2      y     24   h    809 
3      z     34   u    799

部门:

Did  fkEmpid dname ddescription
123    1     test   test
234    1     test1  test1
667    2     hello  hello

最后我想要这样的东西

Ename Eage  Eadd Ephone  dname
x      23     b   677     test,test1
y      24     h   809     hello
z      34     u   799     null

请帮我写SQL

Answer 1

如果知道目标 RDBMS 肯定会很好。但是这个问题经常被问到，所以让我们尝试并排列出所有(至少是流行的)。

对于 SQL Server:

SELECT e.Ename, e.Eage, e.Eadd, e.Ephone, d.dname
  FROM Employee e LEFT JOIN 
(
  SELECT fkEmpid,
         STUFF((SELECT ',' + dname 
                    FROM Department 
                   WHERE fkEmpid = t.fkEmpid 
                     FOR XML PATH('')) , 1 , 1 , '' ) dname
    FROM Department t
   GROUP BY fkEmpid
) d
    ON e.Empid = d.fkEmpid

这是 SQLFiddle 演示

对于 Mysql、SQLite、HSQLDB 2.X:

SELECT e.Ename, e.Eage, e.Eadd, e.Ephone, d.dname
  FROM Employee e LEFT JOIN 
(
  SELECT fkEmpid,
         GROUP_CONCAT(dname) dname
    FROM Department t
   GROUP BY fkEmpid
) d
    ON e.Empid = d.fkEmpid

这是 SQLFiddle 演示(MySql)
这是 SQLFiddle 演示(SQLite)

对于 Oracle 11g:

SELECT e.Ename, e.Eage, e.Eadd, e.Ephone, d.dname
  FROM Employee e LEFT JOIN 
(
  SELECT fkEmpid,
         LISTAGG (dname, ',') WITHIN GROUP (ORDER BY dname) dname
    FROM Department t
   GROUP BY fkEmpid
) d
    ON e.Empid = d.fkEmpid

这是 SQLFiddle 演示

对于 PostgreSQL 9.X:

SELECT e.Ename, e.Eage, e.Eadd, e.Ephone, d.dname
  FROM Employee e LEFT JOIN 
(
  SELECT fkEmpid,
         string_agg(dname, ',') dname
    FROM Department t
   GROUP BY fkEmpid
) d
    ON e.Empid = d.fkEmpid

这是 SQLFiddle 演示

所有情况下的输出:

| ENAME | EAGE | EADD | EPHONE |      DNAME |
---------------------------------------------
|     x |   23 |    b |    677 | test,test1 |
|     y |   24 |    h |    809 |      hello |
|     z |   34 |    u |    799 |     (null) |