英文:
How to show two columns in separate rows, avoid duplicates and omit empty entries
问题
我有两个表,正在连接查询。连接后的表有两列,我需要它们作为查询输出中的单独行;必须省略重复项。
到目前为止我明白了。
如果连接后的表的第二列中有一个为空,这一行也必须被省略。
在这个要求上我失败了。
英文:
I got two tables which I joining the query.
The joined table has two columns which I need as separate rows in the query-output; duplicates must be omitted.
So far I got it.
If one of the second column of the joined table is empty, this row must also be omitted.
I am failing with this requirement.
create table article (id integer, artnum varchar(100), artname varchar(100), PRIMARY KEY (artnum));
insert into article (id, artnum, artname) values (1, '100', 'apple');
insert into article (id, artnum, artname) values (2, '101', 'banana');
insert into article (id, artnum, artname) values (3, '102', 'clementine');
insert into article (id, artnum, artname) values (4, '103', 'dragon fruit');
--
create table supplier (id integer, supartnum varchar(100), supplier1 varchar(100), supplier1name varchar(100), supplier2 varchar(100), supplier2name varchar(100), PRIMARY KEY (supartnum));
insert into supplier (id, supartnum, supplier1, supplier1name, supplier2, supplier2name) values (1, '100', '23', 'DZ', '42', 'VZ');
insert into supplier (id, supartnum, supplier1, supplier1name, supplier2, supplier2name) values (2, '101', '42', 'ZV', '23', 'DZ');
insert into supplier (id, supartnum, supplier1, supplier1name, supplier2, supplier2name) values (3, '102', '23', 'DZ', '', '');
insert into supplier (id, supartnum, supplier1, supplier1name, supplier2, supplier2name) values (4, '103', '23', 'DZ', '23', 'DZ');
select artnum, artname, --supplier1name, supplier2name,
(unnest(ARRAY(SELECT DISTINCT e FROM unnest(ARRAY[supplier1name,supplier2name]) AS a(e)))) as "suppliercombo"
from article
join supplier on artnum = supartnum
;
Result:
| artnum | artname | suppliercombo |
| ------ | ------------ | ------------- |
| 100 | apple | VZ |
| 100 | apple | DZ |
| 101 | banana | ZV |
| 101 | banana | DZ |
| 102 | clementine | |
| 102 | clementine | DZ |
| 103 | dragon fruit | DZ |
The first appearance of atrium 102 (clementine) with an empty suppliercombo hat to be omitted.
I tried using Union All but failed, because its already a 300line-sql which would be duplicated..
Example is also on DB Fiddle
答案1
得分: 0
如果连接表的第二列中有一列是空的,则必须省略此行。我在满足此要求时遇到问题。
查询可按照DB-FIDDLE-1中的方式编写:
select artnum, artname, suppliercombo
from (
select artnum, artname,
unnest(array_remove(array[supplier1name, supplier2name], '')) as suppliercombo
from article
JOIN supplier ON artnum = supartnum
) t1
group by artnum, artname, suppliercombo
order by artnum;
这为我提供了正确的输出:
artnum | artname | suppliercombo |
---|---|---|
100 | apple | VZ |
100 | apple | DZ |
101 | banana | ZV |
101 | banana | DZ |
102 | clementine | DZ |
103 | dragon fruit | DZ |
也可以使用联合来简化上述查询,如DB-FIDDLE-2中所示:
select artnum, artname, suppliercombo
from (
select artnum, artname, supplier1name as suppliercombo
from article
join supplier on artnum = supartnum
where supplier1name != ''
union
select artnum, artname, supplier2name as suppliercombo
from article
join supplier on artnum = supartnum
where supplier2name != ''
) t
group by artnum, artname, suppliercombo
order by artnum;
这也产生了与上述相同的预期输出。
英文:
> If one of the second column of the joined table is empty, this row
> must also be omitted. I am failing with this requirement.
The query can be written as in DB-FIDDLE-1
select artnum, artname, suppliercombo
from (
select artnum, artname,
unnest(array_remove(array[supplier1name, supplier2name], '')) as suppliercombo
from article
JOIN supplier ON artnum = supartnum
) t1
group by artnum, artname, suppliercombo
order by artnum;
This gives me the correct output as :
artnum | artname | suppliercombo |
---|---|---|
100 | apple | VZ |
100 | apple | DZ |
101 | banana | ZV |
101 | banana | DZ |
102 | clementine | DZ |
103 | dragon fruit | DZ |
The above query can be also simplified using union as in DB-FIDDLE-2
select artnum, artname, suppliercombo
from (
select artnum, artname, supplier1name as suppliercombo
from article
join supplier on artnum = supartnum
where supplier1name != ''
union
select artnum, artname, supplier2name as suppliercombo
from article
join supplier on artnum = supartnum
where supplier2name != ''
) t
group by artnum, artname, suppliercombo
order by artnum;
This also gives the expected output as above.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论