在 oracle 的连接选择查询中将某些列名称别名为一个字段

alias some columns names as one field in oracle's join select query

提问by Marecky

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 :)

作为 SELECT 产品中的一个字段。我试图将它们全部命名为相同的“名称”，但 Oracle 对其进行了编号。请帮忙 ：）

采纳答案by Jeffrey Kemp

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:

查询结果集中的列名必须是唯一的。也许您希望给定关注者的每个用户、公司、频道、群组和团队都占一行？在这种情况下，我会使用这样的查询：

select fo.leader_type, us.name
from follow_up fo
join users us
  on (fo.leader_id = us.id and fo.leader_type = 'user')
where follower_id = 83
UNION ALL
select fo.leader_type, co.name
from follow_up fo
join companies co
  on (fo.leader_id = co.user_id and fo.leader_type = 'company')
where follower_id = 83
UNION ALL
select fo.leader_type, ch.title as name
from follow_up fo
join channels ch
  on (fo.leader_id = ch.id and fo.leader_type = 'channel')
where follower_id = 83
UNION ALL
select fo.leader_type, gr.name
from follow_up fo
join groups gr
  on (fo.leader_id = gr.id and fo.leader_type = 'group')
where follower_id = 83
UNION ALL
select fo.leader_type, tt.name
from follow_up fo
join talent_teams tt
  on (fo.leader_id = tt.id and fo.leader_type = 'team')
where follower_id = 83

回答by dcp

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:

我不确定为什么需要将它们作为一个字段获取，因为您不需要在客户端拆分信息吗？无论如何，你可以这样做的一种方法是这样的：

user_name || '|' || company_name || '|' || channel_name || '|' || group_name || '|' || team_name all_fields

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:

这将为您提供一个名为 all_fields 的管道分隔字段。如果您有来自不同表的多个 user_name 字段，您可以使用相同的方法：

   table1.user_name || '|' || table2.user_name ... all_user_names

You could then split the field on the client side.

然后，您可以在客户端拆分该字段。

Personally, I would just do something like this:

就个人而言，我只会做这样的事情：

    table1.user_name table1_user_name
  , table2.user_name table2_user_name
    ...

In other words, just use a unique column alias for each user_name.

换句话说，只需为每个 user_name 使用唯一的列别名。

回答by Marecky

I got thinking and I come up with this solution:

我开始思考，我想出了这个解决方案：

Is it slower than solution of Jeffrey Kemp?

它比 Jeffrey Kemp 的解决方案慢吗？

select
  fo.leader_id,
  fo.leader_type,

  case
    when us.subdomain is not null then us.subdomain
    when us2.subdomain is not null then us2.subdomain
    --when co.name is not null then co.name
    when ch.service_url is not null then ch.service_url
    when gr.id is not null then to_char(gr.id)
    when tt.subdomain is not null then tt.subdomain
    else 'nothing!'
    end
    as leader_url,

  case
    when us.name is not null then us.name
    when co.name is not null then co.name
    when ch.title is not null then ch.title
    when gr.name is not null then gr.name
    when tt.name is not null then tt.name
    else 'nothing!'
    end
    as leader_names,

    case
    when us.img_avatar_path is not null then us.img_avatar_path
    when us2.img_avatar_path is not null then us2.img_avatar_path
    --when us.img_avatar_path is not null and fo.leader_id = co.user_id and fo.leader_type = 'company' then us.img_avatar_path
    when ch.default_img is not null then ch.default_img
    when gr.img_avatar_path is not null then gr.img_avatar_path
    when tt.img_avatar_path is not null then tt.img_avatar_path
    else 'nothing!'
    end
    as img_avatar_path,

    case
    when us.img_avatar_x is not null then us.img_avatar_x
    when us2.img_avatar_x is not null then us2.img_avatar_x
    when  ch.default_img_x is not null then ch.default_img_x
    when gr.img_avatar_x is not null then gr.img_avatar_x
    when tt.img_avatar_x is not null then tt.img_avatar_x
    else 0
    end
    as img_avatar_x,

    case
    when us.img_avatar_y is not null then us.img_avatar_y
    when us2.img_avatar_y is not null then us2.img_avatar_y
    when  ch.default_img_y is not null then ch.default_img_y
    when gr.img_avatar_y is not null then gr.img_avatar_y
    when tt.img_avatar_y is not null then tt.img_avatar_y
    else 0
    end
    as img_avatar_y

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 users us2
    on (co.user_id = us2.id)
  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 = :follower_id