在相邻表中定义顺序的 SQL 查询表

Question

我需要从表 accounts 返回不同的记录，首先显示设备处于 off 状态的项目。

所以结果应该是:

我正在尝试对记录进行分组

SELECT account.id, account.name FROM account
  LEFT JOIN devices ON account.id = devices.account_id
  GROUP BY account.id, devices.status
  ORDER BY devices.status desc

但是 account #2 在列表中出现了两次，因为它有两个 on/off 设备

Answer 1

您基本上可以进行基于条件聚合的排序。以下查询将对帐户是否关闭任何设备进行一级排序。所有至少有一个设备 off 的帐户都被视为同一级别进行排序。第二级排序是在 account_id 上完成的(用于平局)。

SELECT account.id, account.name 
FROM account
LEFT JOIN devices ON account.id = devices.account_id
GROUP BY account.id, account.name
ORDER BY MAX(CASE devices.status WHEN 'off' THEN 1 ELSE 0 END) DESC, 
         account.id

SQL Fiddle Demo (感谢@Martin)

---

现在，如果您希望帐户的“关闭”设备数量最多，我们可以改用以下查询:

SELECT account.id, account.name 
FROM account
LEFT JOIN devices ON account.id = devices.account_id
GROUP BY account.id, account.name
ORDER BY SUM(CASE devices.status WHEN 'off' THEN 1 ELSE 0 END) DESC, 
         account.id