英文:
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;
答案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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论