英文:
DB2 Group by specific columns
问题
是否可能构建一个查询，仅返回以下两行数据。
它应该按照前三列进行分组，并且只选择具有列LANG值的行，最好不为空；如果没有LANG的值，它应该选择具有语言为空的行。
| ID_1 | ID_2 | ID_3 | LANG |
|---|---|---|---|
| 1 | 11 | 111 | F_lang |
| 2 | 22 | 222 | null |
英文:
| ID_1 | ID_2 | ID_3 | LANG |
|---|---|---|---|
| 1 | 11 | 111 | F_lang |
| 1 | 11 | 111 | null |
| 2 | 22 | 222 | null |
Is it possible to build a query which returns only these two lines below.
It should be grouped by the first three columns and should take only that line with value of column LANG which is preferably not null, if no value for LANG exists, it should take the line with language null.
| ID_1 | ID_2 | ID_3 | LANG |
|---|---|---|---|
| 1 | 11 | 111 | F_lang |
| 2 | 22 | 222 | null |
答案1
得分: 0
这回答了问题吗？
with t1(id_1, id_2, id_3, lang) as (
VALUES
('1', '11', '111', 'F_lang'),
('1', '11', '111', NULL),
('2', '22', '222', NULL),
('3', '33', '333', 'F_lang_3_1'),
('3', '33', '333', 'F_lang_3_2'),
('3', '33', '333', NULL)
)
select id_1, id_2, id_3, lang from t1 where lang is not null
union all
select id_1, id_2, id_3, null as lang from t1 group by id_1, id_2, id_3 having max(lang) is null
order by id_1, id_2, id_3
| ID_1 | ID_2 | ID_3 | LANG |
|---|---|---|---|
| 1 | 11 | 111 | F_lang |
| 2 | 22 | 222 | null |
| 3 | 33 | 333 | F_lang_3_1 |
| 3 | 33 | 333 | F_lang_3_2 |
英文:
Does this anwser the question ?
with t1(id_1, id_2, id_3, lang) as (
VALUES
('1', '11', '111', 'F_lang'),
('1', '11', '111', NULL),
('2', '22', '222', NULL),
('3', '33', '333', 'F_lang_3_1'),
('3', '33', '333', 'F_lang_3_2'),
('3', '33', '333', NULL)
)
select id_1, id_2, id_3, lang from t1 where lang is not null
union all
select id_1, id_2, id_3, null as lang from t1 group by id_1, id_2, id_3 having max(lang) is null
order by id_1, id_2, id_3
| ID_1 | ID_2 | ID_3 | LANG |
|---|---|---|---|
| 1 | 11 | 111 | F_lang |
| 2 | 22 | 222 | null |
| 3 | 33 | 333 | F_lang_3_1 |
| 3 | 33 | 333 | F_lang_3_2 |
答案2
得分: 0
使用OLAP计数函数。
cnt = 0 表示：组中没有非空的langs。
with t1(id_1, id_2, id_3, lang) as (
VALUES
('1', '11', '111', 'F_lang'),
('1', '11', '111', NULL),
('2', '22', '222', NULL),
('3', '33', '333', 'F_lang_3_1'),
('3', '33', '333', 'F_lang_3_2'),
('3', '33', '333', NULL)
)
select id_1, id_2, id_3, lang
from
(
select
id_1, id_2, id_3, lang
, count (lang) over (partition by id_1, id_2, id_3) cnt
from t1
)
where lang is not null or cnt = 0
order by id_1, id_2, id_3
| ID_1 | ID_2 | ID_3 | LANG |
|---|---|---|---|
| 1 | 11 | 111 | F_lang |
| 2 | 22 | 222 | |
| 3 | 33 | 333 | F_lang_3_1 |
| 3 | 33 | 333 | F_lang_3_2 |
英文:
Using OLAP count function.
cnt = 0 means: there are no non-null langs in the group.
with t1(id_1, id_2, id_3, lang) as (
VALUES
('1', '11', '111', 'F_lang'),
('1', '11', '111', NULL),
('2', '22', '222', NULL),
('3', '33', '333', 'F_lang_3_1'),
('3', '33', '333', 'F_lang_3_2'),
('3', '33', '333', NULL)
)
select id_1, id_2, id_3, lang
from
(
select
id_1, id_2, id_3, lang
, count (lang) over (partition by id_1, id_2, id_3) cnt
from t1
)
where lang is not null or cnt = 0
order by id_1, id_2, id_3
| ID_1 | ID_2 | ID_3 | LANG |
|---|---|---|---|
| 1 | 11 | 111 | F_lang |
| 2 | 22 | 222 | |
| 3 | 33 | 333 | F_lang_3_1 |
| 3 | 33 | 333 | F_lang_3_2 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论