postgresql 自连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5648210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql self join
提问by tbh1
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
I would like to somehow self join on this table to grab specific cmds and their corresponding values for a particular device.
我想以某种方式在这个表上自我加入以获取特定设备的特定 cmds 及其相应的值。
I do not want just SELECT cmd,value FROM table WHERE device='00:11:22:33:44:55';
我不想要只是 SELECT cmd,value FROM table WHERE device='00:11:22:33:44:55';
Say the values I want correspond to the getnameand getlocationcommands. I would like to have output something like
说我想要的值对应于getname和getlocation命令。我想输出类似的东西
mac | name | location
--------------------+-----------+------------
00:11:22:33:44:55 | some name | somewhere
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.devicebut I am getting nowhere.
我的 sql fu 很漂亮。我一直在尝试不同的组合，SELECT a.value,b.value FROM table AS a INNER JOIN table AS b ON a.device=b.device但我一无所获。
Thanks for any help.
谢谢你的帮助。
回答by Michael Krelin - hacker
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';
