mysql - 如何在mysql中使用2列进行分组

Question

我在 MySQL 数据库中有一个查询

SELECT tbl_lab_reservations.full_desc, serial_number, rsvn_owner, reservation_id, 
SUM((SELECT DATEDIFF(reservation_date_end, reservation_date_start)+1)) AS totalNumberOfDaysReserve FROM tbl_lab_reservations 
JOIN tbl_lab_assets ON tbl_lab_assets.id = tbl_lab_reservations.lab_id 
WHERE tbl_lab_reservations.full_desc = 'WMHD (Masthead Amplifier)' 
AND tbl_lab_reservations.asset_status = 'Idle' 
GROUP BY rsvn_owner 
ORDER BY `tbl_lab_reservations`.`rsvn_owner` ASC

这就是结果

根据查询结果是正确的，但这不是我想要的。根据我在数据库中保存的数据，应该有另一个名为 PAYAK 的 rsvn_owner，但问题是 PAYAK 使用 serial_numbers，IT 也使用它，因为它是按序列分组的PAYAK 和 IT 的 totalNumberOfDaysReserve 被添加，导致错误。我想要的是查询按 serial_number 分组，但如果它们属于不同的 rsvn_owner 他们应该创建另一行。

希望有人理解并能帮助我解决这个问题。提前致谢

Answer 1

所以尝试按序列号分组，然后按所有者分组？ :)

GROUP BY serial_number, rsvn_owner

像这样

SELECT
  tbl_lab_reservations.full_desc, serial_number, rsvn_owner, reservation_id,
  SUM((SELECT DATEDIFF(reservation_date_end, reservation_date_start)+1)) AS totalNumberOfDaysReserve
FROM tbl_lab_reservations
JOIN tbl_lab_assets ON tbl_lab_assets.id = tbl_lab_reservations.lab_id
WHERE tbl_lab_reservations.full_desc = 'WMHD (Masthead Amplifier)'
  AND tbl_lab_reservations.asset_status = 'Idle'
GROUP BY serial_number, rsvn_owner
ORDER BY `tbl_lab_reservations`.`rsvn_owner`

AS 语法告诉我们(参见逗号附近)

[GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]

我们能做到

https://dev.mysql.com/doc/refman/5.0/en/select.html