在oracle的连接选择查询中将某些列名别名为一个字段
我们正在开发类似社交网站的东西。我有任务要做 “跟我来”功能。在我们的网站中,对象有用户、团队、公司、渠道和群组(请不要问为什么有群组和团队——这对我来说也很复杂,但团队与用户的才能有关)
用户、团队、渠道、公司和团体都有自己的桌子。
我有一个查询,可以获取所有追随者的领导者,如下所示,
select
--fo.leader_id,
--fo.leader_type,
us.name as user_name,
co.name as company_name,
ch.title as channel_name,
gr.name as group_name,
tt.name as team_name
from
follow_up fo
left join users us
on (fo.leader_id = us.id and fo.leader_type = 'user')
left join companies co
on (fo.leader_id = co.user_id and fo.leader_type = 'company')
left join channels ch
on (fo.leader_id = ch.id and fo.leader_type = 'channel')
left join groups gr
on (fo.leader_id = gr.id and fo.leader_type = 'group')
left join talent_teams tt
on (fo.leader_id = tt.id and fo.leader_type = 'team')
where
follower_id = 83
我需要获取所有字段,例如:
- user_name、
- company_name、
- channel_name、
- group_name、
- team_name
作为 SELECT 产品中的一个字段。 我试图给它们起一个相同的“名字”,但 Oracle 给它编号了。 请帮忙:)
We are developing something like a social networking website. I've got task to do
'follow me' functionality. In our website objects are users, teams, companies, channels and groups (please don't ask why there are groups and teams - it is complicated for me too, but teams are releated to user's talent)
Users, teams, channels, companies and groups have all their own tables.
I have a query which gets me all the follower's leaders like this
select
--fo.leader_id,
--fo.leader_type,
us.name as user_name,
co.name as company_name,
ch.title as channel_name,
gr.name as group_name,
tt.name as team_name
from
follow_up fo
left join users us
on (fo.leader_id = us.id and fo.leader_type = 'user')
left join companies co
on (fo.leader_id = co.user_id and fo.leader_type = 'company')
left join channels ch
on (fo.leader_id = ch.id and fo.leader_type = 'channel')
left join groups gr
on (fo.leader_id = gr.id and fo.leader_type = 'group')
left join talent_teams tt
on (fo.leader_id = tt.id and fo.leader_type = 'team')
where
follower_id = 83
I need to get all fields like:
- user_name,
- company_name,
- channel_name,
- group_name,
- team_name
as one field in SELECT's product.
I have tried to alias them all the same 'name' but Oracle numbered it.
Please help :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我不确定为什么您需要将它们作为一个字段,因为您不需要在客户端将信息分开吗?无论如何,您可以这样做的一种方法是这样的:
这将为您提供一个名为 all_fields 的管道分隔字段。如果您有来自不同表的多个 user_name 字段,您可以使用相同的方法:
然后您可以在客户端拆分该字段。
就我个人而言,我会这样做:
换句话说,只需为每个 user_name 使用唯一的列别名。
I'm not sure why you need to get them as one field, because aren't you going to need to split the information out on the client side? Anyway, one way you could do it would be like this:
This would give you a pipe delimited field called all_fields. If you have multiple user_name fields from different tables, you could use the same approach:
You could then split the field on the client side.
Personally, I would just do something like this:
In other words, just use a unique column alias for each user_name.
查询结果集中的列名必须是唯一的。也许您希望给定关注者的每个用户、公司、渠道、组和团队占一行?在这种情况下,我会使用这样的查询:
Column names in a query result set must be unique. Perhaps you want one row for each user, company, channel, group and team for the given follower? In which case I'd use a query like this:
我开始思考并提出了这个解决方案:
它比 Jeffrey Kemp 的解决方案慢吗?
I got thinking and I come up with this solution:
Is it slower than solution of Jeffrey Kemp?