postgresql自我加入

发布时间：2020-12-13 18:04:57 所属栏目：百科来源：网络整理

导读：说我有一张这样的桌子 id | device | cmd | value | ------+----------------+-------+---------id = unique row IDdevice = device identifier (mac address)cmd = some arbitrary commandvalue = value of corresponding command 我想以某种方式自我加入此

说我有一张这样的桌子

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

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

我想以某种方式自我加入此表以获取特定设备的特定cmds及其对应值.

我不想只是SELECT cmd,值FROM表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 INNER JOIN表AS b ON a.device = b.device但我无处可去.

谢谢你的帮助.

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!