sql - 在相邻表中定义顺序的 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设备

标签： sqlpostgresql

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 演示（感谢@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