如何在不重复且省略空条目的情况下,将两列显示在单独的行中

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

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.

huangapple
  • 本文由 发表于 2023年3月9日 22:49:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/75686213.html
匿名

发表评论

匿名网友

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

确定