PostgreSQL的自我加入 [英] postgresql self join

问题描述

说我有一个像这样的桌子

Say I have a table like so

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

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

我想以某种方式自我联接到该表上，以获取特定设备的特定cmd及其对应的值.

I would like to somehow self join on this table to grab specific cmds and their corresponding values for a particular device.

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

说我想要的值对应于getname和getlocation命令.我想输出类似

Say the values I want correspond to the getname and getlocation commands. I would like to have output something like

        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这样的不同组合，但是却无济于事.

My sql fu is pretty pants. I've been trying different combinations like SELECT a.value,b.value FROM table AS a INNER JOIN table AS b ON a.device=b.device but I am getting nowhere.

感谢您的帮助.

推荐答案

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';

这篇关于PostgreSQL的自我加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！