SQL:保留CASE + GROUP BY(+ VIEW)中的原始列名 – 歧义的列名

huangapple go评论67阅读模式
英文:

SQL: preserve original column name from CASE + GROUP BY (+ VIEW) - ambiguous column name

问题

当您在使用CASE语句根据条件从多个表中获取列,并且必须在GROUP BY中使用该列名称时,可以通过以下方式保留原始列名,以便创建具有正确列名的VIEW。在这里,您使用的是PostgreSQL。

首先,您可以在CASE语句中给列起一个别名,然后在GROUP BY中使用该别名,以避免列名歧义。下面是示例SQL:

create or replace view my_view as
select sum(v.value) as sum_value,
       (case
           when true then
               f.field
           else
               s.field
           end) as field  -- 别名在此处定义
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by field;  -- 在GROUP BY中使用别名

这将确保您的VIEW中使用的列名是field而不是field1

英文:

How to preserve original column name when I use CASE to get column from many tables based on condition and I have to use this name in GROUP BY because used aggregate function? I need it for create VIEW with proper column names. I use PostgreSQL.

Example

I have three tables:

create table first
(
    id    bigserial primary key,
    field varchar(255) not null
);

create table second
(
    id       bigserial primary key,
    field    varchar(255) not null,
    first_id bigint constraint second_first_id references first
);

create table values
(
    id       bigserial primary key,
    value    numeric,
    first_id bigint constraint value_first_id references first
);

Some input data:

insert into first (field) values ('aaa');
insert into first (field) values ('bbb');
insert into second (field, first_id) values ('ggg', 1);
insert into second (field, first_id) values ('hhh', 2);
insert into values (value, first_id) values (2, 1);
insert into values (value, first_id) values (4, 1);
insert into values (value, first_id) values (20, 2);
insert into values (value, first_id) values (40, 2);

And SQL that join those tables and group by:

select sum(v.value) as sum_value,
       f.field,
       s.field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by f.field, s.field;

Now I add CASE statement with some condition (true for simplicity) to get field field from first or second table:

select sum(v.value) as sum_value,
       case
           when true then
               f.field
           else
               s.field
           end      as field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by field;

There is error:

ERROR: column reference "field" is ambiguous

I could fix the error by change as field and group by field with new name form example field1.

But I want to preserve original name field. Its important when I create view:

create or replace view my_view as
select sum(v.value) as sum_value,
       case
           when true then
               f.field
           else
               s.field
           end      as field1
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by field1;

select * from my_view;

The view returns column with name field1 instead of field.

I try to qualify name as field in view by group by my_view.field but it also doesn't works:

ERROR: missing FROM-clause entry for table "my_view"

I can copy whole CASE statement and paste it in GROUP BY but I think it's not a good solution (especially when CASE is very long and many columns):

create or replace view my_view as
select sum(v.value) as sum_value,
       case
           when false then
               f.field
           else
               s.field
           end      as field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by case
             when false then
                 f.field
             else
                 s.field
             end;

I saw those questions: 1, 2, it is not a duplicate.

答案1

得分: 5

你可以在GROUP BY子句中使用列的序数。例如:

select sum(v.value) as sum_value,
       case
           when true then
               f.field
           else
               s.field
           end      as field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by 2;
英文:

You could use the ordinal of the column in the group by. ie:

select sum(v.value) as sum_value,
       case
           when true then
               f.field
           else
               s.field
           end      as field
from first f
         left join second s on f.id = s.first_id
         left join values v on f.id = v.first_id
group by 2;

huangapple
  • 本文由 发表于 2023年5月30日 00:49:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/76359061.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定