postgresql 自连接

Question

假设我有一张这样的 table

  id  |     device     |  cmd  | value | 
------+----------------+-------+---------

id = unique row ID
device = device identifier (mac address)
cmd = some arbitrary command
value = value of corresponding command

我想以某种方式自行加入此表以获取特定 cmd 及其针对特定设备的相应值。

我不想只是 SELECT cmd,value FROM table WHERE device='00:11:22:33:44:55';

假设我想要的值对应于 getname 和 getlocation 命令。我想输出类似的东西

        mac         |    name   | location
--------------------+-----------+------------
 00:11:22:33:44:55  | some name | somewhere

我的 sql fu 很漂亮。我一直在尝试不同的组合，例如 SELECT a.value,b.value FROM table AS a INNER JOIN table AS b ON a.device=b.device 但我一无所获。

感谢您的帮助。

Answer 1

SELECT a.value AS thisval ,b.value AS thatval
FROM table AS a JOIN table AS b USING (device)
WHERE a.command='this' AND b.command='that';