MySQL -合并表格行(group by无效)

Question

我在完成我的MySQL查询时遇到了一些问题。假设我将表tickets、invoice line和users组合在一起。每个票证都有一个或多个发票行，每个用户可以拥有一个或多个票证。

我正在尝试创建一个查询，我可以用它来创建名字徽章。对于这个徽章，我需要知道该人注册的姓名和物品。

我创建了以下MySQL查询

SELECT u.firstName, u.lastName, il.invID, il.name, 
CASE 
  WHEN il.name = 'Conference Dinner' 
  THEN 'Diner'
END AS 'conference_dinner',

CASE 
  WHEN il.name = 'Regular Conference Fee' THEN 'Conference'
  WHEN il.name = 'Conference Fee for Phd. Students'  THEN 'Conference'
END AS 'conference',

CASE
  WHEN il.name = 'Pre-Conference Fee' THEN 'Pre-Conference'
END AS 'pre_conference',

FROM invoice_line il, events_tickets et, users u
WHERE il.invID = et.invID
AND et.userID = u.ID

查询几乎没问题，但是它为表中有三个发票行(=三个项目)的用户生成了三行，我希望这三行合并为一行。用户ID上的Group By将导致一行，但案例创建的列不会合并到一行中，只显示其中的一列。

我可能用了错误的方式来做这个。但是它应该会产生一个类似下面这样的表：

firstName | lastName | conference_diner | conference | pre_conference |
----------------------------------------------------------------------
John      | Doe      | Diner            | Conference |                |
Jane      | Norman   |                  | Conference | Pre-Conference | 
Martijn   | Thomas   |                  | Conference |                |
Pete      | Johns    |                  |            | Pre-Conference |

目前，这会导致：

firstName | lastName | conference_diner | conference | pre_conference |
----------------------------------------------------------------------
John      | Doe      | Diner            |            |                |
John      | Doe      |                  | Conference |                |
Jane      | Norman   |                  | Conference |                |
Jane      | Norman   |                  |            | Pre-Conference |  
Martijn   | Thomas   |                  | Conference |                |
Pete      | Johns    |                  |            | Pre-Conference |

提前感谢

Answer 1

您可以尝试以下方法：

SELECT u.firstName, u.lastName, il.invID, il.name,
IF(SUM(il.name = 'Conference Dinner') > 0,
    'Diner', '') AS 'conference_dinner',
IF(SUM(il.name IN ('Regular Conference Fee',
                   'Conference Fee for Phd. Students')) > 0,
    'Conference', '') AS 'conference',
IF(SUM(il.name = 'Pre-Conference Fee') > 0,
    'Pre-Conference', '') AS 'pre_conference'
FROM invoice_line il, events_tickets et, users u
WHERE il.invID = et.invID
AND et.userID = u.ID
GROUP BY u.ID

mysql中的比较结果是一个数字，0代表false，1代表true。因此，您可以简单地将这些求和，以计算与给定表达式匹配的行数。换句话说，连接中属于单个用户的所有行都被分组，那些与某个属性匹配的行被计数，这些计数将决定在结果表中打印哪些内容作为该用户的聚合值。

Answer 2

如下所示：

SELECT u.firstName, u.lastName, et.invID,
CASE 
  WHEN il_d.name IS NULL THEN '' ELSE 'Diner'
END AS 'conference_dinner',

CASE 
  WHEN il_c.name IS NULL THEN '' ELSE 'Conference'
END AS 'conference',

CASE
  WHEN il_p.name IS NULL THEN '' ELSE 'Pre-Conference'
END AS 'pre_conference'
FROM events_tickets et
INNER JOIN users u ON et.userID = u.ID
LEFT JOIN invoice_line il_d ON il_d.invID = et.invID AND il_d.name = 'Conference Dinner'
LEFT JOIN invoice_line il_c ON il_c.invID = et.invID AND il_c.name IN ('Regular Conference Fee', 'Conference Fee for Phd. Students')
LEFT JOIN invoice_line il_p ON il_p.invID = et.invID AND il_p.name = 'Pre-Conference Fee'

Answer 3

由于您没有包括源数据，因此我将假设最终结果中的每一列都与输入数据中的一行相关。

这意味着您确实希望使用GROUP BY...

SELECT
  u.firstName, u.lastName, il.invID, il.name, 
  MAX(CASE
    WHEN il.name = 'Conference Dinner'                 THEN 'Diner'
  END) AS 'conference_dinner',
  MAX(CASE
    WHEN il.name = 'Regular Conference Fee'            THEN 'Conference'
    WHEN il.name = 'Conference Fee for Phd. Students'  THEN 'Conference'
  END) AS 'conference',    
  MAX(CASE
    WHEN il.name = 'Pre-Conference Fee'                THEN 'Pre-Conference'
  END) AS 'pre_conference'

FROM
  invoice_line il, events_tickets et, users u
WHERE
  il.invID = et.invID AND et.userID = u.ID
GROUP BY
  u.firstName, u.lastName

然而，我不得不从SELECT中删除il.invID, il.name,。这是因为我推断它们可以有不同的值。在哪种情况下，您希望在聚合结果中显示哪个值？

编辑

与多LEFT JOIN版本相比，这有一个优势，因为它只扫描invoice_line talb一次，而不是多次。这应该意味着更少的读取，潜在更少的cpu，以及通常更少的执行时间。

缺点是它稍微复杂一些，如果需要的话，提取所有单独的il.invID和il.name值就不那么直接了。