PostgreSQL的自我加入

本文介绍了PostgreSQL的自我加入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

说我有一个像这样的桌子

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 amand='this' AND bmand='that';