mysql - 使用sql语句合并两行

Question

我有一个关于使用 sql 语句将表中的两行合并的 MySQL 语句的查询。最初，我已经实现了在数据 GridView 中显示员工轮类的代码。

LocationName | StationName | 12:00 - 13:00 | 13:00 - 14:00 | 14:00 - 15:00
T2           | Counter     | Michael Joyce | Michael Joyce | Michael Joyce

如您所见，同一地点和车站下有两名员工。下一步，我将使用SQL语句来打出格式，最终将SQL语句用于reportviewer。

这是我尝试以与上图相同的格式显示的 SQL 语句。

select
  z.LocationName,
  z.StationName,
  a.12001300,
  b.13001400,
  c.14001500
from (SELECT DISTINCT
        LocationName,
        StationName
      FROM satsschema.employeeslot
      where AllocationDate = '10-Aug'
          and LocationName = 'T2 PML'
          and StationName is not null) z
  left outer join (SELECT
                     LocationName,
                     StationName,
                     EmpName         AS '12001300'
                   FROM satsschema.employeeslot
                   WHERE Assigned = true
                       and AllocationDate = '10-Aug'
                       and (EmpTime = '12:00:00' && EmpTime < '13:00:00')) a
    on z.LocationName = a.LocationName
      and z.StationName = a.StationName
  left outer join (SELECT
                     LocationName,
                     StationName,
                     EmpName         AS '13001400'
                   FROM satsschema.employeeslot
                   WHERE Assigned = true
                       and AllocationDate = '10-Aug'
                       and (EmpTime = '13:00:00' && EmpTime < '14:00:00')) b
    on a.LocationName = b.LocationName
      and a.StationName = b.StationName
  left outer join (SELECT
                     LocationName,
                     StationName,
                     EmpName         AS '14001500'
                   FROM satsschema.employeeslot
                   WHERE Assigned = true
                       and AllocationDate = '10-Aug'
                       and (EmpTime = '14:00:00' && EmpTime < '15:00:00')) c
    on b.LocationName = c.LocationName
      and b.StationName = c.StationName

上面显示的这条 SQL 语句显示的结果与我想要的不符。显示如下:

LocationName | StationName | 12:00 - 13:00 | 13:00 - 14:00 | 14:00 - 15:00
T2           | Counter     | Michael       | Michael       | Michael

如您所见，它仅在 Location 和 Station 中显示一名员工。如果 Location 和 Station 中有多个员工，我还可以看到其他员工的 SQL 语句怎么办？有什么意见吗？

Answer 1

使用 GROUP_CONCAT。参见 Can I concatenate multiple MySQL rows into one field?

类似的东西:

SELECT z.LocationName, z.StationName, Block12.EmpList, Block13.EmpList, Block14.EmpList
FROM satsschema.employeeslot z
LEFT OUTER JOIN
(
    SELECT LocationName, StationName, GROUP_CONCAT(EmpName) AS EmpList
    FROM satsschema.employeeslot
    WHERE Assigned = true
        AND AllocationDate = '10-Aug'
        AND (EmpTime = '12:00:00' && EmpTime < '13:00:00')
    GROUP BY LocationName, StationName
) Block12
ON z.LocationName = Block12.LocationName
    AND z.StationName = Block12.StationName
LEFT OUTER JOIN
(
    SELECT LocationName, StationName, GROUP_CONCAT(EmpName) AS EmpList
    FROM satsschema.employeeslot
    WHERE Assigned = true
        AND AllocationDate = '10-Aug'
        AND (EmpTime = '13:00:00' && EmpTime < '14:00:00')
    GROUP BY LocationName, StationName
) Block13
ON z.LocationName = Block13.LocationName
    AND z.StationName = Block13.StationName
LEFT OUTER JOIN
(
    SELECT LocationName, StationName, GROUP_CONCAT(EmpName) AS EmpList
    FROM satsschema.employeeslot
    WHERE Assigned = true
        AND AllocationDate = '10-Aug'
        AND (EmpTime = '14:00:00' && EmpTime < '15:00:00')
    GROUP BY LocationName, StationName
) Block14
ON z.LocationName = Block14.LocationName
    AND z.StationName = Block14.StationName
WHERE AllocationDate = '10-Aug'
GROUP BY z.LocationName, z.StationName