将一个表中的两行与第二个表中的一行连接起来
我有两个表 - report
和 bouydata
report
------
id sun
1 overcast
bouydata
----------
reportid bouy winddir
1 46026 340
1 46027 345
我想将所有 3 行选择为一行。这是我的查询:
SELECT report.id, sun, bouy1.bouy as bouy1, bouy1.winddir as winddir1, bouy2.bouy as bouy2, bouy2.winddir as winddir2
FROM report
LEFT JOIN bouydata bouy1
ON report.id = bouy1.reportid
LEFT JOIN bouydata bouy2
ON report.id = bouy2.reportid
由于某种原因,我返回了四行:
1 Slightly Overcast 46026 340 46026 340
1 Slightly Overcast 46026 340 46027 345
1 Slightly Overcast 46027 345 46026 340
1 Slightly Overcast 46027 345 46027 345
它不是只选择唯一的行并将它们组合成一行。任何人都可以帮助我得到我想要的结果:
1 Slightly Overcast 46026 340 46027 345
I have two table - report
and bouydata
report
------
id sun
1 overcast
bouydata
----------
reportid bouy winddir
1 46026 340
1 46027 345
I want to select all 3 rows into one row. Here's my query:
SELECT report.id, sun, bouy1.bouy as bouy1, bouy1.winddir as winddir1, bouy2.bouy as bouy2, bouy2.winddir as winddir2
FROM report
LEFT JOIN bouydata bouy1
ON report.id = bouy1.reportid
LEFT JOIN bouydata bouy2
ON report.id = bouy2.reportid
For some reason I'm getting four rows back:
1 Slightly Overcast 46026 340 46026 340
1 Slightly Overcast 46026 340 46027 345
1 Slightly Overcast 46027 345 46026 340
1 Slightly Overcast 46027 345 46027 345
It's not selecting only the unique rows and combining them into one row. Can anyone help me get my desired result of:
1 Slightly Overcast 46026 340 46027 345
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我不知道你为什么要使用相同的表和相同的条件进行双连接。这只是重复信息。
会给你:
I don't know why you're doing a double-join, with the same tables and same conditions. That just duplicates information.
would give you: